Oracle - Pragma Exception

Oracle - Pragma Exception

Pragma exception_init  allows  you to handle the Oracle predefined message by you'r own message. It means you can instruct compiler to associatethe specific message to oracle predefined message at compile time. This way you Improve the Readability of your program,and handle it according to your own way.

Below is an example of Pragma Exception:

CREATE OR REPLACE PROCEDURE SPTEMPPRAGMAEXAMPLE
(
    DEPTID IN NUMBER,
    DEPTNAME IN VARCHAR2
)

   
AS

    MISSINGNULL EXCEPTION;
    PRAGMA EXCEPTION_INIT(MISSINGNULL,-1400);
BEGIN  
       
     INSERT INTO TEMPDEPT VALUES (DEPTID,DEPTNAME);
        DBMS_OUTPUT.PUT_LINE('RECORD SUCCESSFULLY SAVED');      
     COMMIT;
   
    EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        DBMS_OUTPUT.PUT_LINE('DUPLICATE VALUE FOUND WHILE INSERTING DATA');
    WHEN MISSINGNULL THEN
        DBMS_OUTPUT.PUT_LINE('PRAGMA EXCEPTION RAISED');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('SQL EXCEPTION: ' || SQLERRM);

END;


--EXEC SPTEMPPRAGMAEXAMPLE(NULL,NULL);
--SELECT * FROM TEMPDEPT

OUTPUT:

PRAGMA EXCEPTION RAISED