PRAGMA EXCEPTION_INIT Scenario

PRAGMA EXCEPTION_INIT will associates exception name along with error number.


Pragma: Its a Keyword and tells that "Statement will Compile time"

EXCEPTION_INIT: It will take 2 arguments

PRAGMA EXCEPTION_INIT(exception_name, -error_number);

We can take any error number(-negative) along with proper exception name for scenario.

Take below table for one scenario:

create table Exp_Test(Id integer);

SQL> select * from exp_test;

 ID
---
 -1
  2
  3
 -2
  6

eg:


DECLARE
  Invalid_Num Exception;
  Pragma Exception_Init(Invalid_Num, -100);
BEGIN
  FOR K IN (SELECT ID FROM EXP_TEST) LOOP
    BEGIN
      IF SIGN(K.ID) = -1 THEN
        RAISE Invalid_Num;
      ELSE
        DBMS_OUTPUT.PUT_LINE(' Id is ' || k.id);
      END IF;
    EXCEPTION
      WHEN Invalid_Num THEN
        DBMS_OUTPUT.PUT_LINE(' Id in Exception ' || k.id);
    END;  
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(' Err msg  is ' || SQLERRM);

END;

Output:
 Id in Exception -1
 Id is 2
 Id is 3
 Id in Exception -2
 Id is 6


PL/SQL procedure successfully completed