Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2006-06-26
3
Medium Priority
?
390 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
1 Comment
 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup
Suggested Courses

572 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