sqlplus script out param

UNIX for Beginners Q & A UNIX for Dummies Questions & Answers
1

For starters, I have read the forums from top to bottom and cannot find a solution to this particular scenario. Just finished up two hours or scouring the archives. Here is my question which has been asked numerous times before but never fully explained for this particular solution: How do I pass a sqlplus "script" out parameter back to my calling ksh shell script to make use of? (ie. not a return code or error code - just a selected column etc.). I need to use a sqlplus script - not just a block of sql in my shell script. I have tried both the (< @sample.sql) syntax as well as the (exit p_oid) syntax and neither seem to work in ksh. In my sql script, (p_oid) is my out param/bind variable. I need to return it back to my calling ksh script. Thanks in advance for your help!

ksh script example
example.sh

VALUE=`sqlplus -s $USER/$PSWD@$INSTANCE @sample.sql $INPUT_PARAM`
echo $VALUE >> /dir/example.log

sql script example:
sample.sql

WHENEVER SQLERROR EXIT 1 ROLLBACK
WHENEVER OSERROR EXIT 1 ROLLBACK
set echo off
set serveroutput on size 100000
set pause off
set feedback off
set verify off
set term off
set lines 115
var p_oid number
spool /dir/samplesql.log
DECLARE
BEGIN 
DBMS_OUTPUT.PUT_LINE('Beginning select');
SELECT oid
INTO :p_oid
FROM table
WHERE column = '&1';
END;
/
spool off
exit
2 
WHENEVER SQLERROR EXIT 1 ROLLBACK
WHENEVER OSERROR EXIT 1 ROLLBACK
set echo off
set serveroutput on size 100000
set pause off
set feedback off
set verify off
set term off
set lines 115
var p_oid number
spool /dir/samplesql.log
DECLARE
BEGIN 
-- DBMS_OUTPUT.PUT_LINE('Beginning select');
SELECT oid
INTO p_oid
FROM table
WHERE column = '&1';
DBMS_OUTPUT.PUT_LINE(TO_CHAR(p_oid, '999999.99') );

END;
/
spool off
exit

try that

3

Jim - thanks for the suggestion. As long as I use the (colon)p_oid (bind variable syntax), adding the dbms_output does spool the select stmt results to the sqlplus script spooled log file, however, I need to get the out param fed back to the calling shell script to make use of it without having to go read it from the spooled log file (this is a threaded app. so the less moving parts the better). Let me know if you have any other thoughts about how to return the sqlplus script out param back to the calling shell script without the use of a dbms_output spooled log file. Thanks again!

---------- Post updated at 01:22 PM ---------- Previous update was at 12:56 PM ----------

Figured it out. Here is the method to return/pipe a sqlplus script OUT parameter (bind variable) back to a calling shell script without the use of a dbms_output spooled log file.

shell script example
example.sh

VALUE=`sqlplus -s $USER/$PSWD@$INSTANCE <<EOF
WHENEVER SQLERROR EXIT 1 ROLLBACK
WHENEVER OSERROR EXIT 1 ROLLBACK
set echo off
set serveroutput on size 100000
set pause off
set feedback off
set verify off
set term off
set lines 115
var p_oid number
@ $SCRIPT_DIR/sample.sql $INPUT_PARAM :p_oid
print p_oid
exit
EOF`
echo $VALUE >> /dir/example.log

sql script example
sample.sql

DECLARE
BEGIN
  SELECT oid
    INTO :p_oid
    FROM table
   WHERE column = '&1';
END;
/