OTHERS handler or create your own exception.To create your own exception, you need to define it first. After defining your exception, you initialize your exception by using pragma
EXCEPTION_INIT. This tells the compiler to associate an exception name with an Oracle error number. That lets you refer to any internal exception by name and to write a specific handler for it.Note that a pragma is a compiler directive that is processed at compile time, not at run time.
You code the pragma EXCEPTION_INIT in the declarative part of a PL/SQL block, subprogram, or package using the syntax
PRAGMA EXCEPTION_INIT(exception_name, -Oracle_error_number);where
exception_name is the name of a previously declared exception and the number is a negative value corresponding to an ORA- error number.
DECLARE
deadlock_detected EXCEPTION; -- DECLARED AN EXCEPTION
PRAGMA EXCEPTION_INIT(deadlock_detected, -60); -- INITIALIZE THE EXCEPTION
BEGIN
null; -- Some operation that causes an ORA-00060 error
EXCEPTION
WHEN deadlock_detected THEN -- REFER USER DEFINED EXCEPTION
null; -- handle the error
END;
/
The following sample helps you understand it better
declare
index_not_exists exception;
pragma exception_init(index_not_exists, -01418);
begin
execute immediate 'drop index rhnChecksum_chsum_idx';
execute immediate 'alter table rhnChecksum add constraint rhnChecksum_chsum_uq
unique (checksum, checksum_type_id)
using index tablespace [[32m_tbs]]';
exception
when index_not_exists then
null; -- index was already dropped
end;
/
References:
