can nested SQl be run in Unix Script? - Page 2 - Shell Programming and Scripting

YoYo · #8 09-20-2005

can I email you my unix script, quite long though

tmarikle · #9 09-20-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.

YoYo · #10 09-20-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

tmarikle · #11 09-20-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.

YoYo · #12 09-20-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.

YoYo · #13 09-20-2005

gonna go home now and wil be back online in a hour, maybe half.
Thanks for your help.
cheers

tmarikle · #14 09-20-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.

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
script for nested rlogin and telnet	mcburke38	Shell Programming and Scripting	5	03-06-2008 03:05 PM
sed in awk ? or nested awk ?	varungupta	UNIX for Advanced & Expert Users	11	02-08-2008 07:34 AM
Nested Arrays	guysporty	Shell Programming and Scripting	5	04-13-2005 04:44 AM
nested read	TioTony	Shell Programming and Scripting	2	03-05-2004 12:11 PM
Nested calls	omran	Shell Programming and Scripting	6	08-16-2002 01:18 AM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode