1

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 .

user3152869
  • 33
  • 5

1 Answers1

0

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.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • This is the same way i am doing , But the problem is my procedure will return hell lot of DBMS output statements after the `echo $out_variable` And finally returns the return code in my log , can you please help me with the syntax how to grep the o_val from $out_variable . – user3152869 Apr 05 '15 at 15:14