The pragma EXCEPTION_INIT associates an exception name with an Oracle error number. You can intercept any ORA- error and write a specific handler for it instead of using the OTHERS handler.
Case 1: Without Pragma Exception_Int
DECLARE
MissingNullExp EXCEPTION;
--PRAGMA EXCEPTION_INIT(MissingNullExp, -1400);
BEGIN
INSERT INTO empvj (id) VALUES (NULL);
EXCEPTION
WHEN MissingNullExp then
DBMS_OUTPUT.put_line('MissingNullExp:'||SQLERRM);
WHEN OTHERS then
dbms_output.put_line('When Others:'||SQLERRM);
END;
Output: When Others:ORA-01400: cannot insert NULL into ("APPS"."EMPVJ"."ID")
Case 2: With Pragma Exception_Int
DECLARE
MissingNullExp EXCEPTION;
PRAGMA EXCEPTION_INIT(MissingNullExp, -1400);
BEGIN
INSERT INTO empvj (id) VALUES (NULL);
EXCEPTION
WHEN MissingNullExp then
DBMS_OUTPUT.put_line('MissingNullExp:'||SQLERRM);
WHEN OTHERS then
dbms_output.put_line('When Others:'||SQLERRM);
END;
Output:MissingNullExp:ORA-01400: cannot insert NULL into ("APPS"."EMPVJ"."ID")
Case 3: With Pragma Exception_Int if both standard and custom exception present in Exception handling block..
DECLARE
NoDataFoundExp EXCEPTION;
PRAGMA EXCEPTION_INIT(NoDataFoundExp, 100);
v_x varchar2(100);
BEGIN
select first_name into v_x from empvj where id = 999999; --no emp with id 9999999
EXCEPTION
WHEN NoDataFoundExp then
dbms_output.put_line('When Custom NoDataFoundExp:'||SQLERRM);
WHEN NO_DATA_FOUND then
dbms_output.put_line('When Standard NO Data Found:'||SQLERRM);
WHEN OTHERS then
dbms_output.put_line('When Others:'||SQLERRM);
END;
Compilation Error:
ORA-06550: line 8, column 3:
PLS-00484: redundant exceptions 'NO_DATA_FOUND' and 'NODATAFOUNDEXP' must appear in same exception handler
ORA-06550: line 0, column 0:
PL/SQL: Compilation unit analysis terminated
