Send result of SQL procedure in a file with a script sh

Question

I have a SQL procedure in a file and I would like to call this procedure in a sh script then send the result in a file (no matter where is the file).

I do not know if I am doing well or not but here what I wrote :

#!/bin/ksh
#exit 0

VAR=$(sqlplus /NOLOG <<!
connect E760/E760@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST=p595n11)(PORT=1521))(CONNECT_DATA = (SID = DBUVNSD5)))@PA_IND_DELAYS_BODY.sql;
!)

result=`sqlplus / <<SCRIPT| grep ^result | sed "s/^result: //"
set serveroutput on
declare
    var_truc_result VARCHAR2(255);
begin
   export_csv('$1','$2');
   dbms_output.put_line( 'result: ' || var_truc_result);
end;
/
exit
SCRIPT`

sqlplus "system/ADMIN" <<!
exec export_csv($0,$1);
!

echo var_truc_result is: "$result"

Answer 1

After some search, I created and tested a small code snippet to get the printed text from dbms_output.

#!/bin/bash

result=$(sqlplus64 -S <username>/<password>@<IPADDRESS>/<SERVICENAME> << EOF
set serveroutput on;
begin
   dbms_output.put_line('hello '||'$0');
end;
/
exit;
EOF
)
echo "result=$result"

• You just need to process $result variable to grep your output. I ran it under debian 8, I do not have ksh but used bash and my sqlplus bin name sqlplus64.

• sqlplus -S argument explanation

  -S Sets silent mode which suppresses the display of the SQL*Plus banner, prompts, and echoing of commands.

• Moreover, here is another stackoverflow question and good answers to get select query results from sqlplus.

I hope this helps you to solve your problem.