can nested SQl be run in Unix Script?
I tried some and found only simply sql(one select) can work well in unix script.
If you mean:
a) can this nested SQL be run in a shell script?
{
sqlplus un/pw <<EOF
select col_a
, col_b
from table_a a
where exists (
select 1
from table_b b
where a.col_a = b.col_b
);
EOF
} | while read COL_A COL_B
do
echo $COL_A $COL_B
done
Answer: Yes
or
b) can this nested SQL be run in a shell script?
{
sqlplus un/pw <<EOF
select col_a
from table_a a;
EOF | while read COL_A
do
sqlplus un/pw <<EOF
select col_c
from table_b
where col_a = ${COL_A};
EOF
} | while read COL_B
do
echo ${COL_B}
done
done
Answer: Yes
how about:
select x,y
from (select blah1,blah2) a ,(select blah1,blah2 ) b
where a.blah1=b.blah1;
mine one doesnt work at all.
Yes, you can. I'll post proof momentarily.
what is problem with mine one. the output file was always empty, how can I test where is the problem
{
typeset IFS='
'
sqlplus -s un/pw <<EOF
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'
;
EOF
} | while read line
do
echo "$line"
done
TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
PLAN_TABLE STATEMENT_ID
PLAN_TABLE TIMESTAMP
PLAN_TABLE REMARKS
PLAN_TABLE OPERATION
PLAN_TABLE OPTIONS
PLAN_TABLE OBJECT_NODE
PLAN_TABLE OBJECT_OWNER
...
27 rows selected.
I need a real example. "x" and "y" are not in your sub selects so this example is problematic. Also "(select blah1, blah2)" is incomplete.
can I email you my unix script, quite long though
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.
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
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.
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.
gonna go home now and wil be back online in a hour, maybe half.
Thanks for your help.
cheers
The line is absolutly required in sqlplus!!!
Here is my version of your script with "SET SERVEROUTPUT ON" commented out:
#! /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:
$ rm output.csv
$ ksh test.sh
$ cat output.csv
$
Now add the SET SERVEROUTPUT ON line:
$ 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.
but why I added that line and it still doesnt work.
That is a good question but, as you can see, I added my query to your PL/SQL block and the results changed from no output to output.
I can think of two possibilities:
-
You mistyped it. The text must be as follows: SET SERVEROUTPUT ON. Note that there are no spaces between SERVER and OUTPUT.
-
Your query results in no rows. Try my code block as a test to prove it to yourself that it works. The only requirement is that you have an EXPLAIN PLAN table defined. If you do not, change PLAN_TABLE to something you do have in your schema.
I had thought probably I mistyped something,however, I checked it again and again, the answer is no.
is it possible to cause this problem because of the system?
one more difference is i got count(*) in the query and u havent.
No, it isn't a system problem. I would guess that your query returns no rows at this point.
Try my example above and remember to change "REM #### LINE COMMENTED OUT #### set serveroutput on" to "set serveroutput on" and change "un/pw" to something meaningful on to your database.
You must have edited your message after I read it. The COUNT(*) isn't going to change anything either but now you have given me a thought of how we can test your query.
Place "SELECT COUNT(), COUNT(), repeat for total number of columns you are fetching FROM (" before your your first SELECT keyword and place ")" at your SQL's semi-colon ;.
This will prove how many rows your query is return.
Example with my query:
cursor cs is
select count(*), count(*) from (
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'
);
This will always return one row; mine is "112,112". If your query returns no rows, you'll get "0,0" always.
many thanks for your help. i have sorted out. silly mistake.
cheers.