Oracle 9i - Return Code from Procedure to UNIX
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Oracle 9i - Return Code from Procedure to UNIX

  1. #1
    Join Date
    Aug 2006
    Posts
    5

    Oracle 9i - Return Code from Procedure to UNIX

    A bit of background first before I get to the main question. I have managed to execute a simple stored procedure from UNIX via a command file and UNIX script.

    UNIX Script:
    sqlplus uname/psswrd@sname @update_aap_delme.sql

    SQL File - update_aap_delme.sql:
    execute update_aap_delme(:finals);
    exit;


    Stored Procedure
    create or replace procedure update_aap_delme
    as
    begin
    update aap_delme set one_column = one_column + 1;
    end update_aap_delme;
    /


    This works without any problems. The question I have is how can I return, say, a 0 for success and a 1 for failure to the calling UNIX Script ?

    Adding RETURN_CODE=`echo $?` after the sqlplus line in the UNIX script will take the output value from the update_aap_delme.sql file but I'm not sure how to return a value from:
    (1) the .sql file
    (2) the stored procedure.

    I have tried playing with exceptions and OUTPUT variables but haven't hit the correct combination so far. I am a relative newbie to Oracle (10+ years Sybase/SQL Server) so hopefully I am missing something simple.

    I am not allowed to use dbms_job as this seems to be a corporate standard.

    Any help at all would be appreciated.

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,478

    Cool


    Try this in .sql file:
    PHP Code:
    WHENEVER SQLERROR EXIT 1;
    execute update_aap_delme(:finals);
    exit 
    0

    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Aug 2006
    Posts
    5
    Thanks for the reply - I have this working now.

  4. #4
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    RETURN_CODE=`echo $?`

    is the same thing as

    RETURN_CODE=$?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width