?
Solved

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

Posted on 2005-04-18
7
Medium Priority
?
1,503 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:abrusko
  • 4
  • 3
7 Comments
 
LVL 25

Expert Comment

by:jrb1
ID: 13808906
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  
0
 
LVL 2

Author Comment

by:abrusko
ID: 13809020
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
0
 
LVL 25

Expert Comment

by:jrb1
ID: 13809133
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.
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 2

Author Comment

by:abrusko
ID: 13825441
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
0
 
LVL 25

Accepted Solution

by:
jrb1 earned 1500 total points
ID: 13826961
I'm wondering if it's an issue with KSH.  Here's another way to try:

I created a file test.sql:
-----
whenever sqlerror exit sql.sqlcode
begin
   raise_application_error ( -20000, 'Error in program')
end;
/
exit
-----

Here's a second test.sql file I created (this caused a unique constaint violation error)
-----
whenever sqlerror exit sql.sqlcode
begin
  insert into mytable select * from mytable;
end;
/
exit
-----

Then I created a batch file:
-----
sqlplus -s user/password@dbenv @test
echo 'Status = ' $?
-----

When I run this with the first file, I get Status = 32.  When I run the second file, I get Status = 1.
0
 
LVL 2

Author Comment

by:abrusko
ID: 13827132
Thanks...I'll mess around with these, but I need a few days...working on another issue...I'll be in touch...thanks again.
0
 
LVL 2

Author Comment

by:abrusko
ID: 13844755
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
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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.  …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses
Course of the Month15 days, 6 hours left to enroll

839 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