Home » Infrastructure » Unix » Accepting out parameters from a pl/sql procedure
Accepting out parameters from a pl/sql procedure [message #303274] Thu, 28 February 2008 10:57 Go to next message
shree_z
Messages: 75
Registered: February 2008
Member
Hi all,

I want to accept 3 out parameters from the shell script.
Also I want to move a file from one directory to other depending on the returned values from the procedure.

I have a part of the shell script here.

[QUOTE]#!/bin/ksh

echo "=================================================="
echo "Beginning program " `date "+%m/%d/%y %H:%M:%S"` "\n"
echo "================================================="

datadir="/path/data";

archdir="/path/archive";

cd $datadir

ls -lrt A*.txt | awk '{print $9}' | while read fname
do

echo The file to be passed to importing program is $datadir/$fname
sqlplus -s apps/apps4you@GAMDEV << EOF
echo Connected
var request_id number(30);
var status varchar2(30);
var phase varchar2(30);
execute XX_Call_Import_Prgm('$fname','$datadir',:request_id,:status,:phase);
print request_id;
print phase;
print status;

exit

EOF

if [[$phase = "Completed"]] then
echo Import Successful
mv $fname $archdir
else
echo Import Not Successful
fi

done

echo "=================================================="
echo "Ending program " `date "+%m/%d/%y %H:%M:%S"` "\n"
echo "=================================================="[/QUOTE
]

Well here I want to put the condition

Quote:
if $phase ='Completed' and $status='Normal',
then
mv $fname $datadir


How do I put this condition and how can I use the variables declared in the sql section of this program in the script part?

Thanks in advance
Re: Accepting out parameters from a pl/sql procedure [message #303289 is a reply to message #303274] Thu, 28 February 2008 12:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How do I put this condition

Using -a option of test: "if [ $phase = 'Completed' -a $status = 'Normal' ]; then".

Quote:
how can I use the variables declared in the sql section of this program in the script part?

Redirect sqlplus output to awk and use dbms_output on these variables with tags you can extract.

Regards
Michel
Re: Accepting out parameters from a pl/sql procedure [message #303296 is a reply to message #303289] Thu, 28 February 2008 13:37 Go to previous messageGo to next message
shree_z
Messages: 75
Registered: February 2008
Member
Hi Michel,

Thanks for your reply.I understood the -a option.

Quote:
Redirect sqlplus output to awk and use dbms_output on these variables with tags you can extract.


Could you please give me an example for this?
Re: Accepting out parameters from a pl/sql procedure [message #303297 is a reply to message #303296] Thu, 28 February 2008 13:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I have neither a database nor Unix by hand but try:
sqlplus <<EOF
connect ...
declare
  var integer := 1;
begin
  dbms_output.put_line('var='||var);
end;
/
exit;
EOF

You see the format of sqlplus output and how you can awk it to get what you want in a similar way you did with:
ls -lrt A*.txt | awk '{print $9}' | while read fname

Regards
Michel

[Updated on: Thu, 28 February 2008 13:50]

Report message to a moderator

Re: Accepting out parameters from a pl/sql procedure [message #303298 is a reply to message #303297] Thu, 28 February 2008 14:52 Go to previous message
shree_z
Messages: 75
Registered: February 2008
Member
thanks a lot Michel
Previous Topic: Connecting to oracle database
Next Topic: shell script error - passing parameters to PL/SQL (merged)
Goto Forum:
  


Current Time: Sat Dec 03 00:57:41 CST 2016

Total time taken to generate the page: 0.23772 seconds