What is Pragma and its type with examples

 

PRAGMA IN ORACLE

          
In Oracle PL/SQL, PRAGMA refers to a compiler directive or "hint" it is used to provide an instruction to the compiler.
          
Type of Pragma
    1. Pragma Autonomous_Transactions
    2. Pragma Restrict References
    3. Pragma Exception_INIT
    4. Pragma Serially Reusable
  
  
Pragma Autonomous Transaction

In below example we create a function, and we use DML statement in function. If we use DML in function we can not call these function in SELECT statement directly.
But if we use Pragma Autonomous_Transactions in function then we call that function in SELECT statement.

    CREATE OR REPLACE FUNCTION XXC_PRAGMA_AUTONOMOUS_TRANS
    RETURN NUMBER
    IS
        PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
        UPDATE EMPLOYEES
        SET SALARY = SALARY+(SALARY*20)/100;
        COMMIT;
    RETURN 1;  
    END XXC_PRAGMA_AUTONOMOUS_TRANS;


SELECT XXC_PRAGMA_AUTONOMOUS_TRANS FROM DUAL;



Pragma Exception_INIT

CREATE OR REPLACE PROCEDURE XXC_PRAGMA_EXCEPTION_INIT
IS
    XXC_EXCEPTION EXCEPTION;
    PRAGMA EXCEPTION_INIT(XXC_EXCEPTION,100);
    EMP_ID NUMBER;
BEGIN
    SELECT EMPLOYEE_ID
    INTO EMP_ID
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID=1000;
EXCEPTION
WHEN XXC_EXCEPTION
THEN
        DBMS_OUTPUT,PUT_LINE('NO DATA FOUND ERROR');
END XXC_PRAGMA_EXCEPTION_INIT;



CREATE OR REPLACE PROCEDURE XXC_PRAGMA_EXCEPTION
IS
    XXC_EXCEPTION EXCEPTION;
    PRAGMA EXCEPTION_INIT(XXC_EXCEPTION,100);
    EMP_ID NUMBER;
BEGIN
    SELECT EMPLOYEE_ID
    INTO EMP_ID
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID=1000;
EXCEPTION
WHEN XXC_EXCEPTION
THEN
        RAISE_APPLICATION_ERROR(-20100,SQLERRM);
END XXC_PRAGMA_EXCEPTION;



Pragma Serially_Reusable

This is also used in package specification.

    CREATE OR REPLACE PACKAGE XXC_PRAGMA_SERIALLY
    IS
    PRAGMA SERIALLY_REUSABLE;
    ID NUMBER :=1000;
    END XXC_PRAGMA_SERIALLY;
  
    DECLARE
    BEGIN
    DBMS_OUTPUT.PUT_LINE(XXC_PRAGMA_SERIALLY.ID);
    END;
  
    Output:-  1000
  
In this block we assign 2000 value in package variable. But its signification only within only this block.
  
    DECLARE
    BEGIN
    XXC_PRAGMA_SERIALLY.ID:=2000;
    DBMS_OUTPUT.PUT_LINE(XXC_PRAGMA_SERIALLY.ID);
    END;
  
    Output:-  2000
  
If again we want to access package variable then print previous value.

    DECLARE
    BEGIN
    DBMS_OUTPUT.PUT_LINE(XXC_PRAGMA_SERIALLY.ID);
    END;
  
    Output:-  1000


Pragma Restrict References

Pragma Restrict References only use in Package Specification.
Attributes of Pragma Restrict References
    1. WNDS (Write No Database State)
    2. WNPS (Write No Package State)
    3. RNDS (Read No Database State)
    4. RNPS (Read No Package State)

Example of Pragma Restrict References


CREATE OR REPLACE PACKAGE XXC_PRAGMA_RESTRICT_REFE
IS
    EMPID NUMBER :=100;
    PROCEDURE XXC_PROC1;
    PROCEDURE XXC_PROC2;
    PROCEDURE XXC_PROC3(ID NUMBER);
    PROCEDURE XXC_PROC4(ID NUMBER);
    PROCEDURE XXC_PROC5(ID NUMBER);
  
    PRAGMA RESTRICT_REFERENCES(XXC_PROC1,'WNDS');
    PRAGMA RESTRICT_REFERENCES(XXC_PROC2,'WNPS');
    PRAGMA RESTRICT_REFERENCES(XXC_PROC3,'RNDS');
    PRAGMA RESTRICT_REFERENCES(XXC_PROC4,'RNPS');
    PRAGMA RESTRICT_REFERENCES(XXC_PROC5,'RNPS',TRUST);
  
END XXC_PRAGMA_RESTRICT_REFE;



WNDS-
Asserts that the subprogram writes no database state (does not modify database tables).

WNPS-
Asserts that the subprogram writes no package state (does not change the values of packaged variables).

RNDS-
Asserts that the subprogram reads no database state (does not query database tables).

RNPS-
Asserts that the subprogram reads no package state (does not reference the values of packaged variables)

TRUST
Asserts that the subprogram can be trusted not to violate one or more rules. This value is needed for functions written in C or Java that are called from PL/SQL, since PL/SQL cannot verify them at run time.

Thanks
Sajal Agarwal
http://oraappstutorials.blogspot.in