Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Posts
    14

    Unanswered: exit unix script after sqlerror in procedure

    Hi!

    Can anybody tell me, how it works to exit the UNIX Script after ANY SQLERROR has occured in my procedure??

    my unix script executes a few procedures,
    but how can i pass the error code to an unix variable and exit the unix script, if any oracle error occured?



    PHP Code:
    --------------------------------------------------------------------------------
     
    sqlplus -<<EOF1 
    $usr
    /$pwd 
    set serveroutput on feedback off verify off heading off 
    echo off 
    whenever sqlerror 
    exit sql.sqlcode
    exec cqt_proc_cdr_insert
    exit; 
    EOF1` 
    --------------------------------------------------------------------------------



    now - just for testing - i got the error (when i run the script with the -x command - ORA-01536 space quota exceeded -

    i got the errorcode on unix like 127,


    PHP Code:
     
    status
    =$? 

    I check the status, if there has any error occured within my procedure, it doesn't work.

    PHP Code:
    if [ $status -eq 0 ]; then 
    exit 
    echo 
    "error code" 
    else 
    #go on within the unix script 
    .. 
    .. 
    fi 



    Because if there is any "ORACLE error", the script must exit and display an error message.

    THANKS.

  2. #2
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    I don't see why what you have wouldn't work, other than the difficulty of capturing the exact Oracle error code. In my experience, the exact error code is none of the shellscript's business - all it should care about is whether the SQL*Plus session completed successfully or not. If it completed successfully the return code will be 0; otherwise it must have failed. Therefore I would usually not bother with sql.sqlcode, and instead just use

    WHENEVER SQLERROR EXIT 5
    WHENEVER OSERROR EXIT 10

    The error code limitation is that the return code of a Unix process is a 2 byte number, which translates into decimal as 0-255. Therefore any return codes up to 255 willl be fine, but after that it will overflow, i.e. 256 becomes 0, 257 becomes 1 etc.
    Code:
    $ sqlplus william/xx@yy
    
    SQL*Plus: Release 9.2.0.5.0 - Production on Wed Jan 26 18:43:47 2005
    
    Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
    
    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.5.0 - Production
    
    SQL> whenever sqlerror exit sql.sqlcode
    SQL> DECLARE
      2     n NUMBER;
      3  BEGIN
      4     SELECT 1 INTO n FROM dual WHERE 2 = 3;
      5  END;
      6  /
    DECLARE
    *
    ERROR at line 1:
    ORA-01403: no data found
    ORA-06512: at line 4
    
    Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.5.0 - Production
    $ print $?
    123
    123 is MOD(1403,256).

    Note that you don't even have to capture the return code.
    Code:
    $ sqlplus william/xx@yy <<-the_end || echo I seem to  have failed
    > exit 999
    > exit
    > the_end
    
    SQL*Plus: Release 9.2.0.5.0 - Production on Wed Jan 26 18:56:14 2005
    
    Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
    
    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.5.0 - Production
    
    SQL> Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.5.0 - Production
    I seem to have failed
    A handy thing in shellscripts (assuming you are using a proper shell such as ksh, bash or zsh) is an error handler:
    Code:
    #!/bin/ksh
    
    function die
    {
        print -u2 $*
        exit 1
    }
    
    # Now we can "do or die", similar to Perl:
    
    cd nosuchdir || die "Failed to navigate to the desired directory. I quit."
    
    echo More commands execute here if the above is successful...
    If I save that as "xx", make it executable and run it, I get:
    Code:
    $ xx
    ./xx[11]: nosuchdir:  not found
    Failed to navigate to the desired directory. I quit.
    More sophisticated versions of the "die" function can capture the actual return code, but I'm not sure it's worth the effort.

Posting Permissions

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