Output param not retaining value

From: <jackal_on_work_at_yahoo.com>
Date: 24 Jan 2007 02:34:49 -0800
Message-ID: <1169634889.476403.31820@m58g2000cwm.googlegroups.com>

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 Received on Wed Jan 24 2007 - 04:34:49 CST