Shell script appending output of sql query

I am writing the following script to create the file v_out.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;

I want to append this file v_out.txt with the output of another query based on the condition provided below.

select status from t1 where id = 10;
 if  status = 'COMPLETE' THEN
   SELECT COL1||' '||COL2 FROM T2;
 else 
   'status not complete'
  end if;

Any suggestions on how to do this will be appreciated.

Is this within the same sqlplus session, or have you issues spool off perhaps?

I would think that:-

spool /tmp/a.txt ;
select Hello   from dual;
select Goodbye from dual;

.... type structure in your SQL would write a single output.

Can you show us what you have that is not working?

Robin

Note that SPOOL has an APPEND option. So all you have to do is use it when you run your 2nd SQL:-

SPOOL v_out.txt APPEND

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;

File name is case sensitive:-

spool v_out.txt
spool V_OUT.txt append

will create two different files.

tried that ..still not working..

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

Produces output:-

$ cat v_out.txt
First SQL
Second SQL

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