I'm writing my pl sql package. I have a function like
FUNCTION my_func(argument IN NUMBER)
RETURN NUMBER
BEGIN
return some_package.somefunction(argument);
END;
I want to handle exceptions, that are thrown if
What are those exceptions? Are they predefined or should I define them to handle? I'm not sure they are in list here in predefined ones.
And how can I handle every exception?
To answer your general question, most Oracle errors don't have pre-defined named exceptions associated with them, just the couple of dozen you found.
However, you can create your own named exceptions and associate them like this:
declare
table_or_view_does_not_exist exception;
pragma exception_init (table_or_view_does_not_exist, -942);
l_table_name varchar2(30) := 'XXX';
l_count integer;
begin
execute immediate 'select count(*) from ' || l_table_name into l_count;
exception
when table_or_view_does_not_exist then
raise_application_error (-20001, 'No such table exists');
end;
/
declare
*
ERROR at line 1:
ORA-20001: No such table exists
ORA-06512: at line 10
How you actually handle the exception is up to you.
You can declare such exceptions in a package specification, and then use them in other code:
when my_package.my_exception then...
Alternatively, you can catch them all in WHEN OTHERS and then use sqlcode to identify them:
declare
l_table_name varchar2(30) := 'XXX';
l_count integer;
begin
execute immediate 'select count(*) from ' || l_table_name into l_count;
exception
when others then
case sqlcode
when -942 then
raise_application_error (-20001, 'No such table exists');
else
raise;
end case;
end;
when others then of code handle every possible exception?One thing is sure, "some_package" AND "some_package.somefunction" existed when you created the function "my_func" otherwise you would have had an error upon creation.
If they are removed (either the package or the function in the package) then "my_func" will automatically become invalid and you won't even be able to execute it.
Test case :
create or replace package some_package as
function somefunction(a number) return number;
end;
/
create or replace package body some_package as
function somefunction(a number) return number is
begin
return 1;
end;
end;
/
create or replace function my_func return number is
begin
return some_package.somefunction(1);
end;
/
set serveroutput on size 2000
begin
dbms_output.put_line(my_func());
end;
/
drop package some_package;
begin
dbms_output.put_line(my_func());
end;
/
Yields :
Package created.
Package body created.
Function created.
1
PL/SQL procedure successfully completed.
Package dropped.
dbms_output.put_line(my_func()); * ERROR at line 2: ORA-06550: line 2, column 24: PLS-00905: object DEMO.MY_FUNC is invalid ORA-06550: line 2, column 3: PL/SQL: Statement ignored
The only way to prevent "my_func" from becoming invalid is to call the function using an "execute immediate" and then trap the error.
New "my_func" would look like this and would NEVER become invalid.
create or replace function my_func return number is
v_return number;
begin
execute immediate 'begin :r := some_package.somefunction(1); end;' using out v_return;
return v_return;
exception
when others then
dbms_output.put_line(SQLERRM);
dbms_output.put_line('Package or function does not exists anymore');
return -1;
end;
/
And would yield this result :
Package created.
Package body created.
Function created.
1
PL/SQL procedure successfully completed.
Package dropped.
ORA-06550: line 1, column 13:
PLS-00201: identifier 'SOME_PACKAGE.SOMEFUNCTION'
must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Package or function does not exists anymore
-1
PL/SQL procedure successfully completed.
