i am trying this but it is not working..it is only printing first sql session output.. please advise..
variable1=$ echo "set feed off
set pages 0
select status from t1 where id = 10;
exit" | sqlplus -s scott/tiger@orcl > v_tmp.txt
sqlplus -s /nolog << EOF
CONNECT scott/tiger@orcl;
whenever sqlerror exit sql.sqlcode;
set newpage 0;
SET PAGESIZE 0;
SET ECHO OFF;
SET FEEDBACK OFF;
SET HEADING OFF;
SET VERIFY OFF;
SET LINESIZE 100;
set tab off;
set trimspool off;
spool v_out.txt
SELECT '/****************************************************'
||CHR(10)
|| '************* Creating Table **************'
||CHR(10)
|| '*****************************************************/'
FROM dual;
spool off
exit;
if [[ $(cat v_tmp.txt) == 'COMPLETE' ]]; then
echo "#_______________________________________________________#"
echo "STATUS IS COMPLETE"
echo "#_______________________________________________________#"
else
echo "#_______________________________________________________#"
echo "STATUS IS PENDING"
echo "#_______________________________________________________#"
sqlplus -s /nolog << EOF
CONNECT scott/tiger@orcl;
whenever sqlerror exit sql.sqlcode;
set newpage 0;
SET PAGESIZE 0;
SET ECHO OFF;
SET FEEDBACK OFF;
SET HEADING OFF;
SET VERIFY OFF;
SET LINESIZE 100;
set tab off;
set trimspool off;
spool V_OUT.txt append
SELECT COL1||' '||COL2 FROM T2;
spool off
exit
fi;
You should ensure the here document is closed for both your SQL blocks:-
sqlplus -s /nolog << EOF
connect $conn_str;
whenever sqlerror exit sql.sqlcode;
set newpage 0;
SET PAGESIZE 0;
SET ECHO OFF;
SET FEEDBACK OFF;
SET HEADING OFF;
SET VERIFY OFF;
SET LINESIZE 100;
set tab off;
set trimspool off;
spool v_out.txt
select 'First SQL' from dual;
spool off;
exit;
EOF
sqlplus -s /nolog << EOF
connect $conn_str;
whenever sqlerror exit sql.sqlcode;
set newpage 0;
SET PAGESIZE 0;
SET ECHO OFF;
SET FEEDBACK OFF;
SET HEADING OFF;
SET VERIFY OFF;
SET LINESIZE 100;
set tab off;
set trimspool off;
spool v_out.txt append
select 'Second SQL' from dual;
spool off;
exit;
EOF
thanks Yoda but the second sql should execute based on the condition when satisfied else not..something like this
variable1=$ echo "set feed off
set pages 0
select status from t1 where id = 10;
exit" | sqlplus -s scott/tiger@orcl > v_tmp.txt
if [[ $(cat v_tmp.txt) == 'COMPLETE' ]]; then
echo "second sql"
sqlplus -s /nolog << EOF
connect $conn_str;
whenever sqlerror exit sql.sqlcode;
set newpage 0;
SET PAGESIZE 0;
SET ECHO OFF;
SET FEEDBACK OFF;
SET HEADING OFF;
SET VERIFY OFF;
SET LINESIZE 100;
set tab off;
set trimspool off;
spool v_out.txt append
select 'Second SQL' from dual;
spool off;
exit;
EOF
fi