Re: Exiting PL/SQL

"Gordon" <gordon_t_wu_at_hotmail.com> wrote in message news:9nFxc.368$Ny6.1046_at_mencken.net.nih.gov...
>
> "srivenu" <srivenu_at_hotmail.com> wrote in message
> news:1a68177.0406082158.3c7e097d_at_posting.google.com...
> > You can exit SQL*PLUS with "exit [value]" command and the exit command
> > will return the [value] to the OS.
> > $ sqlplus "/ as sysdba"
> > SQL> exit 10
> > $ echo $?
> > 10
> >
> > You can also use this for exiting from PL/SQL
> > Put this in a file x.sql
> >
> > whenever sqlerror exit 100
> > exec dbms_lock.sleep(30);
> >
> > $ sqlplus "/ as sysdba"
> > SQL> @x.sql
> > ^CBEGIN dbms_lock.sleep(30); END;
> >
> > *
> > ERROR at line 1:
> > ORA-01013: user requested cancel of current operation
> >
> > $ echo $?
> > 100
> >
> > regards
> > Srivenu
>
> Thank you Srivenu,
>
> Now my code works, but I have noticed something rather interesting.
> I have wrote my code to return 0 upon sucessful execution and -100 upon
> exception
>
> WHENEVER SQLERROR EXIT -100 ROLLBACK;
> BEGIN
> .....
> END;
> /
>
> EXIT 0; -- sucessful execution.
>
> Now: when the code executes without errors, 0 is always returned. When an
> error has occured, instead of -100, a random positive number is returned.
> Strange..., I wonder why.
>
>

Unix return codes are between 0 to 255. So return code is truncated to just the low order 8 bits

-1 will return 255 .. and -100 will return 156

Anurag Received on Wed Jun 09 2004 - 19:07:17 CDT