?
Solved

how to execute a stored procedure with an out value in a shell prog

Posted on 2006-06-26
3
Medium Priority
?
385 Views
Last Modified: 2013-12-12
I am trying to execute an oracle stored procedure in a shell script and then using the exit value from the procedure in a IF condition in the shell script. The out parameter is a number, its a s follows, the procedure is called within check_4_data.sql  but is giving me the following error "SP2-0734: unknown command beginning "PDL.FIND_D..." - rest of line ignored. SP2-0103: Nothing in SQL buffer to run."

CODE:

checkdatapdl=`sqlplus -s $conpdl @$DIR/check_4_data.sql &`
wait
echo $checkdatapdl >> $LOG

if [$checkdatapdl = 1] ; then
  echo "EXISTEN DATOS" >> $LOG
else
  echo "No EXISTEN DATOS" >> $LOG
fi
0
Comment
Question by:opedrega
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 16

Accepted Solution

by:
MohanKNair earned 2000 total points
ID: 16986508
Print the output from PL/SQL using DBMS_OUTPUT.PUT_LINE and redirect the output to a file.
From the shell script read the file.

sqlplus -s sqlplus -s $conpdl << EOF
set serveroutput on
var x number;

spool $DIR\outfile.dat
@$DIR/check_4_data.sql
spool off
exit;
EOF

cat $DIR\outfile.dat | while read line
do
checkdatapdl = $line
done;

if [$checkdatapdl = 1] ; then
  echo "EXISTEN DATOS" >> $LOG
else
  echo "No EXISTEN DATOS" >> $LOG
fi
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question