Re: Output param not retaining value

jackal_on_work_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
> Jackal
>

Maybe you'll be interested in reading something like http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14261/errors.htm#sthref1956 <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 Received on Wed Jan 24 2007 - 04:54:46 CST