sqlplus script out param


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers sqlplus script out param
# 1  
sqlplus script out param

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
Code:
VALUE=`sqlplus -s $USER/$PSWD@$INSTANCE @sample.sql $INPUT_PARAM`
echo $VALUE >> /dir/example.log

sql script example:
sample.sql
Code:
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  
Code:
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
Code:
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
Code:
DECLARE
BEGIN
  SELECT oid
    INTO :p_oid
    FROM table
   WHERE column = '&1';
END;
/

 

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #14
Difficulty: Easy
The F9 function key needs to be pressed during a Windows reboot to enter into Safe Mode.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Sqlplus error - sqlplus -s <login/password@dbname> : No such file or directory

i am using bash shell Whenever i declare an array, and then using sqlplus, i am getting sqlplus error and return code 127. IFS="," declare -a Arr=($Variable1); SQLPLUS=sqlplus -s "${DBUSER}"/"${DBPASS}"@"${DBASE} echo "set head off ; " > ${SQLCMD} echo "set PAGESIZE 0 ;" >> ${SQLCMD}... (6 Replies)
Discussion started by: arghadeep adity
6 Replies

2. Shell Programming and Scripting

Dynamics param for script

Hi everyone, I need a way to take the value of a parameter with a for lood. For example i execute the script with this parameter ./Script PARAM1 PARAM2 PARAM3 PRAM4 for i in <LIST OF PARAMETERS> do PARAMETERS=$<NUMBER OF PARAMETER> done How can i express <LIST OF... (1 Reply)
Discussion started by: Xedrox
1 Replies

3. Linux

Shell Script to generate Dynamic Param file Using SQL Plus Quey

Hi All, Can anyone give me Shell script sample script to generate Param file by Reading Values from SQL Plus query and it should assign those values to variables like.. $$SChema_Name='ORCL' Thanks in Advance... Srav... (4 Replies)
Discussion started by: Sravana Kumar
4 Replies

4. Shell Programming and Scripting

sqlplus in shell script

Hi When I use sqlplus in shell script, I get sqlplus: command not found. ORACLE_HOME is not set. How to set ORACLE_HOME in unix? Thanks (3 Replies)
Discussion started by: vinoth_kumar
3 Replies

5. Shell Programming and Scripting

SQLPLUS within shell script

Hi I want to connect to the Oracle database using a username/password and get back the query result(a numeric value) in a variable, which I can then compare using a conditional. Can anybody help me with this. Thanks Gaurav (4 Replies)
Discussion started by: gaurav_1711
4 Replies

6. Shell Programming and Scripting

how can i get sqlplus answer into my script

hi, i have an sql query, and i want to get the answer returned and read it into a variable in my shell script thanks (2 Replies)
Discussion started by: JamesByars
2 Replies

7. Shell Programming and Scripting

(Urgent):Creating flat file using sql script and sqlplus from UNIX Shell Script

Hi, I need help urgently for following issue. Pls help me to resolve this issue. I am calling sql script file(file1.sql) from UNIX Shell Script(script1.ksh) using sql plus and trying to create flat file that contains all records returned from SQL query in SQL script(file1.sql) I given... (6 Replies)
Discussion started by: praka
6 Replies

8. Shell Programming and Scripting

help me in sending parameters from sqlplus script to unix shell script

Can anybody help me out in sending parameters from sql*plus script to unix shell script without using flat files.. Initially in a shell script i will call sql*plus and after getting some value from some tables, i want that variable value in unix shell script. How can i do this? Please tell me... (2 Replies)
Discussion started by: Hara
2 Replies

9. UNIX for Dummies Questions & Answers

Script for reading filelist and preparing param file.

Hi All, Not sure if this would be in a dummies sectiin or advanced. I'm looking for a script if someone has doen something like this. I have list of files - adc_earnedpoints.20070630.txt adc_earnedpoints.20070707.txt adc_earnedpoints.20070714.txt adc_earnedpoints.20070721.txt... (2 Replies)
Discussion started by: thebeginer
2 Replies

10. Shell Programming and Scripting

SQLPLUS in script

I have a script that passes a parameter to the sqlplus command that looks like the following: #!/bin/sh TARGET_SID=$1 AUXILIARY_SID=$2 sqlplus 'sys@$AUXILIARY_SID as sysdba' @shutdown.sql I keep getting the following error: ERROR: ORA-12154: TNS:could not resolve service name... (4 Replies)
Discussion started by: renichols
4 Replies

Featured Tech Videos