plsql Exception Handling Internally defined exceptions
Example
An internally defined exception doesn't have a name, but it has its own code.
When to use it?
If you know that your database operation might raise specific exceptions those which don't have names, then you can give them names so that you can write exception handlers specifically for them. Otherwise, you can use them only with others exception handlers.
Syntax
declare
my_name_exc exception;
pragma exception_init(my_name_exc,-37);
begin
...
exception
when my_name_exc then
do something
end;
my_name_exc exception; that is the exception name declaration.
pragma exception_init(my_name_exc,-37); assign name to the error code of internally defined exception.
Example
We have an emp_id which is a primary key in emp table and a foreign key in dept table. If we try to remove emp_id when it has child records, it will be thrown an exception with code -2292.
create or replace procedure remove_employee
is
emp_exception exception;
pragma exception_init(emp_exception,-2292);
begin
delete from emp where emp_id = 3;
exception
when emp_exception then
dbms_output.put_line('You can not do that!');
end;
/
Oracle documentation says: "An internally defined exception with a user-declared name is still an internally defined exception, not a user-defined exception."
