Return code from PL/SQL Code

UNIX for Advanced & Expert Users
1

Hi Guys,
I was just wondering if anybody can help me with this problem.

OK, how we can get a value back from PL/SQL Script (not stored procedure/function)

See the below example: (for example aaa.sh)

#!/bin/ksh

VALUE=`sqlplus -s user/password@test_id <<EOF
@xxx.sq
EOF`
echo $VALUE

And xxx.sql is like below:

DECLARE
x number = 10;
y number = 0;
BEGIN
y = x /2;
END;
/
exit

OK, I'd like to get the value of "y" variable from xxx.sql back into the calling aaa.sh VALUE variable...

Mind you "exit y;" doesn't help...

Thanks in advance.

l

2

try something like this...have to be honest I dont do much PL/SQL programming, rather I maintain some of what we have that is in our ksh scripts. here is a sample.

sqlplus -s $ORA_UID/$ORA_PWD << EOF
whenever sqlerror exit 9; --Arbitrary Return Code
variable error_cd number

BEGIN
:error_cd = 1;
pkg_do_some_stuff.call_this_function ( :error_cd ) ;

END;
/

exit :error_cd
EOF

MY_NEW_RETURN_CD=$?

if [ $MY_NEW_RETURN_CD -ne 0 ]
then
do some really cool stuff
fi

3

Google,
Thanks for the prompt reply.
As I mentione before I'd like to get a value (like a value of a variable) from PL/SQL "SCRIPT" (not a function or procedure) back to the calling Unix script... I know how to do it when I call a stored function simply by return a value (return somevarable;)
Mind you I can not pass a value to a PL/SQL script (or I don't know how)

Thanks again...

4

Not sure if this is really going to help you, but in Informix I would do something like this:

dbaccess $DBNAME - << ! 2>/dev/null | grep -v "^$" | sed -e 's/value//g' | read SOD_FILE

set isolation to dirty read;
select value from $PARAMTABLE
where parameter_key = 'SOD_FILE' and parameter_grp = 'extract_dates'
and gems_site = '$SITE'
!

dbaccess returns the result of the sql to stdout which is manipulated by sed and grep for the desired result and read into SOD_FILE.

(The dbaccess command could be back quoted and SOD_FILE set by assignment as you have done in your example).

Note here the use of environment variables (PARAMTABLE and SITE) embedded in the SQL. KSH will expand these variables and substitute their values into the sql before the dbaccess command is run.

Alternatively you could generate an sql file on the fly and clear it down afterwards. If you use the echo command, again the shell will expand environment variables.

MBB.

5

Put set serverouput on
and add dbms_output.put_line(to_char(y)); in @xxx.sql.

Also why r u using EOF? will
VALUE=`sqlplus -s user/password@test_id < @xxx.sq`
this doesn't work?

Regards,
Yeheya

6

Thanks Yeheya... It works....
Thanks to mbb and google for their contribution...

7

I subscribe to a UNIX tip of the day from UGU and it just so happens that I received a tip on accessing our parameters from Oracle. Thought I would post it since it was differant from the solution provided...

OUT PARAMETER FROM ORACLE

Ever wanted to execute an
Oracle Procedure which contains
an out parameter, and use the
value returned from the out
parameter in the shell variable.

Try out this. The procedure
test1 adds two nos. and returns
output into the out parameter.

export ORACLE_HOME=< type in your Oracle Home Path >
export ORACLE_SID=< Mention the Oracle SID >
export PATH=$ORACLE_HOME:$ORACLE_HOME/bin:$PATH # This Path
is set to access the sqlplus executable
dummyvar=`sqlplus -s tcon4iqalib/tcon4iqalib <<end
set pagesize 0 feedback off ver off heading off echo off
serverout on
variable verr_mesg number
exec test1(4,5,:VERR_MESG)
print verr_mesg
exit;
end`
echo " Error is " $dummyvar
echo " Result is " $dummyvar
echo $dummyvar
#end of shell script

This tip generously supported by: (edited)

FORUM RULE: NO EMAIL ADDRESSES (ANTISPAM RULE) - Neo

8

google YOU'RE MY MAN!