Use Pragma Exception_Init for Error Handling

Format: PRAGMA EXCEPTION_INIT ( Exception_Name , SqlCode# )

This pragma (compiler directive) is necessary in two situations:

• Use this to name any unnamed ORA- error that might be raised by your program. If you don't, you have to catch those errors with 'WHEN OTHERS.'


• For a user-defined exception, assign a code between –20999 and –20000 to SQLCode so it can be raised.

Situation #1:

---

For instance, if ORA-00068 had a possibility of popping up when your program ran, you'd want to code as shown in the example.

ORA-00068: invalid value string for parameter string, must be between string and string

DECLARE
    e_InvalParam Exception;
    Pragma Exception_Init(e_InvalParam, -68);
BEGIN
    ...
EXCEPTION
    when e_InvalParam then ...
END;

The following ORA- errors have already been named. (See the package named Standard in All_Source.)

Pragma Exception_Init( Access_Into_Null ,' -6530 '); Pragma Exception_Init( Case_Not_Found ,' -6592 '); Pragma Exception_Init( Collection_Is_Null ,' -6531 '); Pragma Exception_Init( Cursor_Already_Open ,' -6511 '); Pragma Exception_Init( Dup_Val_On_Index ,' -0001 '); Pragma Exception_Init( Invalid_Cursor ,' -1001 '); Pragma Exception_Init( Invalid_Number ,' -1722 '); Pragma Exception_Init( Login_Denied ,' -1017 '); Pragma Exception_Init( No_Data_Found , 100 ); Pragma Exception_Init( No_Data_Needed ,' -6548 '); Pragma Exception_Init( Not_Logged_On ,' -1012 '); Pragma Exception_Init( Program_Error ,' -6501 '); Pragma Exception_Init( Rowtype_Mismatch ,' -6504 '); Pragma Exception_Init( Self_Is_Null ,' -30625 '); Pragma Exception_Init( Storage_Error ,' -6500 '); Pragma Exception_Init( Subscript_Beyond_Count ,' -6533 '); Pragma Exception_Init( Subscript_Outside_Limit ,' -6532 '); Pragma Exception_Init( Sys_Invalid_Rowid ,' -1410 '); Pragma Exception_Init( Timeout_On_Resource ,' -0051 '); Pragma Exception_Init( Too_Many_Rows ,' -1422 '); Pragma Exception_Init( Userenv_CommitScn_Error ,' -1725 '); Pragma Exception_Init( Value_Error ,' -6502 '); Pragma Exception_Init( Zero_Divide ,' -1476 ');

Situation #2:

---

I want to check for a valid code, and raise an error if the value is wrong.

Procedure p_CkCode
                ( i_Emp# NUMBER
                , i_Code NUMBER) is
    v_Code    NUMBER;
    v_SqlCode NUMBER;
    
    e_InvalCode Exception; 
    Pragma Exception_Init(e_InvalCode,-20456); 
Begin
    ...
Exception
    when e_InvalCode then
        v_SqlCode := SqlCode;
        Insert Into AuditTrack (errors)
                         values(i_emp#||’ ’||v_SqlCode);
        Raise;
End;

By default, all user exceptions have a SqlCode of 1.
The code must be between –20,000 and –20,999. Anything else will raise ORA-21000.

If no code number was assigned to the user-defined unhandled exception, you'll see the following:

ORA-06510: PL/SQL: unhandled user-defined exception