Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

sqlplus script out param

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers sqlplus script out param
# 1  
Old 02-01-2010
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  
Old 02-01-2010
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  
Old 02-01-2010
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;
/

 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

SQLPLUS calling Via Script

Hello All, Could you please help me if i am doing anything wrong in below script, especially the sqlplus part performance wise or anything else i could improvise in the script. Thank you. #!/bin/ksh ## Batch Obj Id MP_BCH_OBJ_ID=$1 PASS=$2 partition=$3 ## script dir... (6 Replies)
Discussion started by: Ariean
6 Replies

2. 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

3. Shell Programming and Scripting

Sqlplus in shell script

Hi All, Please let me know what i am missing in the following code (part of my script) Schemas=(AWQM WFCONTROLLER PROVCO PRISM) for s in "${Schemas}" do sch="${s}_$tol" if || ;then echo "This is AD or TD region" sqlplus -s $sch/$tpwd@$ttns <<EOF... (7 Replies)
Discussion started by: pvmanikandan
7 Replies

4. 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

5. 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

6. 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

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
Login or Register to Ask a Question