How to capture the last returned value from sql session in the unix script?

Question

I am calling a procedure from shell script , my procedure has an out parameter which will return '2' at last in case of any error . My question is how do i capture this last returned value/row in the unix environment ? . i need to terminate my host program with exit 1 based on the return value "2". Any suggestions will helpful .

Answer 1

You could directly assign the output value to a shell variable.

Or, alternarively, you can call the PL/SQL procedure in SQL*Plus and store the OUTPUT parameter's value in a local file. And then in your shell script, grep the required value from the file.

For example,

out_variable=`sqlplus -s $conn_string <<EOF
SET SERVEROUTPUT ON SIZE 1000000
SET FEEDBACK OFF
whenever sqlerror exit 1;
variable o_val number;

EXEC myproc(:o_val);
EOF`

So, the out_variable will have the output value of the procedure. Using backticks, will execute and assign the output from sqlplus to the shell variable.