PLSQL handling custom exception

Question

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

1. some_package doesnt exist.
2. somefunction doesnt exist.

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?

Answer 1

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.

Answer 2

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;