Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

can nested SQl be run in Unix Script?

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting can nested SQl be run in Unix Script?
# 8  
Old 09-21-2005
can I email you my unix script, quite long though
# 9  
Old 09-21-2005
Yes, send via the forum's private email. I'll post a subset of the code to describe the problem for anyone else who can benefit.
# 10  
Old 09-21-2005
I couldnt send email to you. so i just put the script here

timeout(){
`$ORACLE_BIN/sqlplus -s $user/$passwd << EOM >output.csv
set verify off
set heading off
set feedback off
declare
cursor cs is
select a.tran_date,a.event_code,a.event_desc,a.timeoutCount,b.eventCount
from (select /*+ ordered index (a fk_event_hist_1) */
trunc(start_dtg) as tran_date
, c.event_code
, d.event_desc
, count(*) as timeoutCount
from trn_event_history c
, trn_event d
where trunc(start_dtg)>=trunc(sysdate)-2
and c.event_code = d.event_code
and error_text like 'SystemError:Operation Time Out (Server Side)%'
group by trunc(start_dtg), c.event_code, d.event_desc) a
,(select /*+ ordered index (a fk_event_hist_1) */
trunc(start_dtg) as tran_date
, c.event_code
, d.event_desc
, count(*) as eventCount
from trn_event_history c
, trn_event d
where trunc(start_dtg)=trunc(sysdate)-2
and c.event_code = d.event_code
group by trunc(start_dtg), c.event_code, d.event_desc) b
where a.tran_date=b.tran_date and a.event_code=b.event_code
order by 1,3;
dt date;
--and other variables
begin
open cs;
loop
fetch cs into all the variables;
exit when cs%NOTFOUND;
dbms_output.put_line(var1||','||var2||','||...);
end loop;
close cs;
end;
.
/
EOM`
}
timeout
# 11  
Old 09-21-2005
Code:
timeout(){
`$ORACLE_BIN/sqlplus -s $user/$passwd << EOM >output.csv
set verify off
set heading off
set feedback off
SET SERVEROUTPUT ON
...

"SET SERVEROUTPUT ON" is not included in your script. DBMS_OUTPUT won't print anything without this line.
# 12  
Old 09-21-2005
i tried,no change. however I think as I save the output in a file so it doesnt matter whether I set serveroutput on or not. not sure right.
# 13  
Old 09-21-2005
gonna go home now and wil be back online in a hour, maybe half.
Thanks for your help.
cheers
# 14  
Old 09-21-2005
The line is absolutly required in sqlplus!!!

Here is my version of your script with "SET SERVEROUTPUT ON" commented out:

Code:
#! /usr/bin/ksh
# test.sh
timeout(){
`sqlplus -s un/pw << EOM >output.csv
set verify off
set heading off
set feedback off
REM #### LINE COMMENTED OUT #### set serveroutput on
declare
    cursor cs is
        select t.table_name, c.column_name
         from
           (select table_name
                  ,column_name
            from   all_tab_columns) c
          ,(select table_name
            from   all_tables) t
        where t.table_name = c.table_name
        and   t.table_name = 'PLAN_TABLE'
        ;

    dt date;
    var1 varchar2(30);
    var2 varchar2(20);
begin
    open cs;
    loop
        fetch cs into var1, var2;
    exit when cs%NOTFOUND;
        dbms_output.put_line(var1||','||var2);
    end loop;
    close cs;
end;
.
/
EOM`
}
timeout

The test:
Code:
$ rm output.csv
$ ksh test.sh
$ cat output.csv
$

Now add the SET SERVEROUTPUT ON line:

Code:
$ rm output.csv
$ ksh test.sh
$ cat output.csv
PLAN_TABLE,STATEMENT_ID
PLAN_TABLE,TIMESTAMP
PLAN_TABLE,REMARKS
PLAN_TABLE,OPERATION
PLAN_TABLE,OPTIONS
PLAN_TABLE,OBJECT_NODE
PLAN_TABLE,OBJECT_OWNER
PLAN_TABLE,OBJECT_NAME
PLAN_TABLE,OBJECT_INSTANCE
PLAN_TABLE,OBJECT_TYPE
PLAN_TABLE,OPTIMIZER
PLAN_TABLE,SEARCH_COLUMNS
PLAN_TABLE,ID
PLAN_TABLE,PARENT_ID
PLAN_TABLE,POSITION
PLAN_TABLE,COST
PLAN_TABLE,CARDINALITY
PLAN_TABLE,BYTES
PLAN_TABLE,OTHER_TAG
PLAN_TABLE,PARTITION_START
PLAN_TABLE,PARTITION_STOP
PLAN_TABLE,PARTITION_ID
PLAN_TABLE,OTHER
PLAN_TABLE,DISTRIBUTION
PLAN_TABLE,CPU_COST
PLAN_TABLE,IO_COST
PLAN_TABLE,TEMP_SPACE
$

The only thing that changed was enabling server output. sqlplus won't print dbms_output messages if this is not included.
Last edited by tmarikle; 09-21-2005 at 11:53 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

UNIX Sqlplus - Capture the sql statement about to run and execution status

Greetings Experts, I am on AIX using ksh. Created a unix script which generates the CREATE OR REPLACE VIEW ... and GRANT .. statements, which are placed in a single .txt file. Now I need to execute the contents in the file (there are around 300 view creation and grant statements) in Oracle and... (4 Replies)
Discussion started by: chill3chee
4 Replies

2. Shell Programming and Scripting

Run sql query after ssh in UNIX

I am running this test.ksh on server1. It successfully logins to server2 but runs the queries of query.sql on server1. query.sql is present in both server1 and server2 Can anybody please help. I need to run queries on server2 itself.:confused: Below is the test script... (10 Replies)
Discussion started by: shruthimithra
10 Replies

3. Shell Programming and Scripting

run sql queries from UNIX shell script.

How can i run sql queries from UNIX shell script and retrieve data into text docs of UNIX? :confused: (1 Reply)
Discussion started by: 24ajay
1 Replies

4. Shell Programming and Scripting

How can i run sql queries from UNIX shell script and retrieve data into text docs of UNIX?

Please share the doc asap as very urgently required. (1 Reply)
Discussion started by: 24ajay
1 Replies

5. Shell Programming and Scripting

Time taken to run a SQL script

Hello I am asked to run around 5-6 SQL queries in a shell script and take a note of the time taken to execute each query to a file. How do I get the time taken to run the individual SQL queries (2 Replies)
Discussion started by: vat1kor
2 Replies

6. Shell Programming and Scripting

Run SQL thru shell script: how to get a new line when run sql query?

Hi, this's Pom. I'm quite a new one for shell script but I have to do sql on shell script to query some information from database. I found a concern to get a new line...When I run my script, it retrieves all data as wondering but it's shown in one line :( What should I do? I'm not sure that... (2 Replies)
Discussion started by: Kapom
2 Replies

7. Shell Programming and Scripting

Run Sql plus in shell script

Hello, I want to connect to ssh, run a query, and store that into a variable in the shell script. Also I need to pass the variable back to php to display the query results. I have created a public/private key pair for ssh connection and that is working fine. Also I am able to run query in the... (8 Replies)
Discussion started by: shekhar2010us
8 Replies

8. Shell Programming and Scripting

Nested SQL queries within Shell script

Hi, Would someone know if I can fire nested sql queries in a shell script? Basically what I am trying to do is as follows: my_sql=$(sqlplus -s /nolog<<EOF|sed -e "s/Connected. *//g" connect... (2 Replies)
Discussion started by: shrutihardas
2 Replies

9. UNIX for Dummies Questions & Answers

Execute PL/SQL function from Unix script (.sql file)

Hi guys, I am new on here, I have a function in oracle that returns a specific value: create or replace PACKAGE BODY "CTC_ASDGET_SCHED" AS FUNCTION FN_ASDSCHEDULE_GET RETURN VARCHAR2 AS BEGIN DECLARE ASDSchedule varchar2(6); ASDComplete... (1 Reply)
Discussion started by: reptile
1 Replies

10. Shell Programming and Scripting

Nested Case in UNIX script

Hi I wanted to know if we can write a nested case in UNIX script. Something like following - Case ${sDB} in Srvr1) case ${sSchema} Sch1) DBusr=Username1 DBPwd=Pwd1 ;; Sch2) DBusr=Username2 ... (1 Reply)
Discussion started by: sumeet
1 Replies
Login or Register to Ask a Question