asked on

Still trying to pass a return code from PL/SQL to K Shell.

I posted this a few years ago and got a reply that worked for that situaion, but I have a slightly different sitaution now and am struggling mightily.

I have the following logic in my pl/sql code:

DECLARE
A EXCEPTION;
RC number;
...
...
...
IF precount_revenue = midcount_revenue AND
precount_revenue = endcount_revenue then
RAISE A;
ELSE
RAISE B;
END IF;

EXCEPTION
WHEN A then
:RC :=1;
EXIT :RC;
WHEN B then
:RC :=2;
EXIT :RC;

But when I execute the following in K shell:

RC=$?

echo $RC

a "0" is always echo'd.

How to I get RC (back in the K Shell script) to "see" the value placed in RC in the PL/SQL script?

Thanks very much for looking into this...I appreciate it.

Andy

jrb1

Are you running SQLPLUS through a script, and calling your procedure? If so, I'm sure your procedure is getting the correct value as a return code, but sql plus is still returning with a 0.

Post your call, but as part of running SQLPLUS, you should be able to pass the return code from your procedure in a bind variable, and then return that from SQL PLUS:

Here's an example:

RCODE=`sqlplus -s / << EOF

SET SHOWMODE off
SET ECHO off
SET FEEDBACK off
SET VERIFY off
SET RECSEP off
SET PAGES 0
SET TRIMSPOOL on
SET LINESIZE 2000
VARIABLE RCODE NUMBER ;

WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK;
DECLARE
BEGIN
:RCODE := rso_gen_email_info('${EMAIL_FILE}');
END;
/

print :RCODE

exit :RCODE

EOF`

echo $RCODE

abrusko

ASKER

JRB1....thank you for the reply!

Yes...I am running SQL*PLUS thru a K SHELL script and calling my pl-sql as such:

sqlplus $ORALOGON @$basedir/bid_shipment_recycle_chkcounts.sql >> $JOBLOG2

I'm not sure where the RCODE=`sqlplus -s / << EOF from you example goes...in K shell I assume?

Also not sure how it would incorporate into my existing SQLPLUS call.

Thanks again,

Andy

jrb1

What my example did was, return a value from SQLPLUS to the shell. I'm not sure that it would be available directly in the standard return code variable, but I think is available as such.

So, you can add the RCODE=` to your call. Also have to finish with a `

My example had all of the calls instream, but you're instructions are in a stored sql statement. To implement the same type of thing, add this to the top of the file:

VARIABLE RC NUMBER ;

You should be able to remove the declaration of RC from your procedure. Then after the procedure call, add

exit :RCODE

I don't think you'll need anything else.

abrusko

ASKER

jrb1,

As you might have guessed by now, I am far from an expert at this. I am having trouble incorporating your suggestions into my code structure...please bear with me a while longer as I keep trying.

Thanks,
Andy

ASKER CERTIFIED SOLUTION

jrb1

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

abrusko

ASKER

Thanks...I'll mess around with these, but I need a few days...working on another issue...I'll be in touch...thanks again.

abrusko

ASKER

jrb1,

I never did get this working...I resolved it with different logic. Mostly my lack of understanding made it difficult to conform your suggestion to my code.

But thanks alot for the help!!!

Andy