Hi All,
I need your help again , could you please have a look at the below script and share your thoughts? Thank you.
-- The below code works as expected.
create or replace package pkg_exceptions
as
excp_20001 exception;
end pkg_exceptions;
create or replace procedure prc_excp1
as
begin
raise pkg_exceptions.excp_20001;
exception
when pkg_exceptions.excp_20001 then
dbms_output.put_line('excp 20001 occurred');
end;
set serveroutput on
begin
prc_excp1;
end;
-- The below code works well
create or replace procedure prc_excp3
as
excp_20001 exception;
pragma exception_init(excp_20001, -20001);
begin
raise_application_error(-20001, 'my exception');
exception
when excp_20001 then
dbms_output.put_line('excp 20001 occurred');
dbms_output.put_line(sqlerrm);
when others then
dbms_output.put_line('in others exception');
end prc_excp3;
-- The below gives compilation error, I couldn't refer the exception defined in a package
create or replace procedure prc_excp2
as
pragma exception_init(pkg_exceptions.excp_20001, -20001);
begin
raise_application_error(-20001, 'my exception');
exception
when pkg_exceptions.excp_20001 then
dbms_output.put_line('excp 20001 occurred');
dbms_output.put_line(sqlerrm);
when others then
dbms_output.put_line('in others exception');
end prc_excp2;
Is this the expected behaviour or I'm making any mistakes here? Please advise, thank you.