Re: Output param not retaining value

On Jan 24, 5:54 am, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> jackal_on_w..._at_yahoo.com schrieb:
>
>
>
>
>
> > Hi group,
>
> > I have created an proc which is as follows:
>
> > create or replace procedure test1(p OUT number) as
> > err exception;
> > begin
> > p := 2;
> > raise err;
> > exception
> > when err then
> > p := 2;
> > raise_application_error('-20000','Errrrrrrrrrrrrrrrrrr....');
> > end;
>
> > I have a PL/SQL block that calls this proc:
>
> > declare
> > a number(2);
> > begin
>
> > a := 1;
>
> > test1(a);
>
> > dbms_output.put_line(a);
> > exception
> > when others then
> > dbms_output.put_line(sqlerrm);
> > dbms_output.put_line(a);
> > null;
> > end;
> > /
>
> > But whenever i call this proc and print the value for variable a, it
> > always shows 1 instead of 2 which i had set in the proc. Is it possible
> > to for the pass output parameters even in case of an exception?
>
> > Thanks in advance
> > JackalMaybe you'll be interested in reading something likehttp://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14261/err...
> <quote>
> Unhandled exceptions can also affect subprograms. If you exit a
> subprogram successfully, PL/SQL assigns values to OUT parameters.
> However, if you exit with an unhandled exception, PL/SQL does not assign
> values to OUT parameters (unless they are NOCOPY parameters). Also, if a
> stored subprogram fails with an unhandled exception, PL/SQL does not
> roll back database work done by the subprogram.
>
> You can avoid unhandled exceptions by coding an OTHERS handler at the
> topmost level of every PL/SQL program.
> </quote>
>
> and then rethink your questions?
>
> Best regards
>
> Maxim- Hide quoted text -- Show quoted text -

Jackal, it is possible to have a failed procedure pass back a value. An exception handler, handles the exception so if you do not raise an error the procedure can just set default values and exit back to the caller. However, you should write the error handler to capture specific expected conditions.

Example you perform a select into, which by rule must return one and only one row. The possibility exits and is acceptable that the row may not exit so you write the exception handler to handle no_rows and return default values (or null) while for any other error you signal a problem that needs to stop processing.

By default unhandled exceptions bubble up to the highest caller in a chain of called procedures. Normally you need to capture where an error occurred in your code identifying the SQL being ran and the key values involved. Since all expected errors should be explicitly handled this means that for any unexpected error you probably need to stop processing. One way to pass the error all the way back to the first caller in the chain of programs is to use the raise statement to re-raise the existing error within an exception handler so that it is passed by to the caller. If you are going to use a when others exception handler I suggest you strongly consider using raise so that the calling programs sees the true error.

Just some thoughts.
-- Mark D Powell -- Received on Wed Jan 24 2007 - 08:26:57 CST