PRAGMA is a keyword given to the compiler by Oracle to execute in a specific way.
Now lets revise what is a compiler?
A compiler is a computer program (or a set of programs) that transforms source code written in a programming language (the source language) into another computer language (the target language), with the latter often having a binary form known as object code.
How pragma functions?
Being the part of source code, the pragma statement tips the compiler to differently compile the program unit, thus manipulating the behavior of the program unit, currently under compilation. Note that the pragma is interpreted only at the time of compilation and not at the time of execution.
Types of pragma :-
1. AUTONOMOUS_TRANSACTION – Compiler allows schema objects like subprograms, PL/SQL blocks, or triggers to commit their transaction within their scope.
An oracle feature, which allowed having simultaneous active transactions in the same database session. The autonomous pragma allows a separate active transaction, which is independent of the main transaction in the session. The COMMIT/ROLLBACK done within the autonomous object does not affect the main transaction and vice versa. All in all, it is an individual transaction and not the part of main transaction.
Practical Example:- Lets take an example of batch process which processes 100 records. In the 3rd record we get an exception or an error but we want to log the same and simultaneously complete processing 100 records. In such a case another PRAGMA AUTONOMOUS_TRANSACTION procedure can be created which will log the errors in a separate session.
2. RESTRICT_REFERENCES – Allows the compiler to scan the database purity during package compilation. It can be used in packages only.
The pragma was introduced to control and preserve the database state. It worked as a watchdog for the packaged function so that they should not modify the package or database state. It restricted the creation of the package, if its member function violated the specified purity level.
The pragma checks purity level of the function in four states
WNDS – DML operations restricted on any table in the database
WNPS – Modification restricted on package variables
RNDS – SELECT query restricted on the database tables
RNPS – Restriction on the selection of package variables
CREATE OR REPLACE PACKAGE PACKAGE ABC
NAME VARCHAR2(50) := ‘AKS’;
FUNCTION NAME RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES<NAME,WNPS>; — Modification restricted on package variables.
3.EXCEPTION_INIT – Compiler associates a user defined exception name with an exception number.
The pragma EXCEPTION INIT allows the association of a self chosen error number with a user defined exception. The error number can be any of the system recognized error number.
PRAGMA EXCEPTION_INIT(ORD_EXCEPTION, -100);
4. SERIALLY_REUSABLE – Compiler deduces the package state from session level to just a server call. It can be used in Package spec only.
The pragma SERIALLY_REUSABLE enables the package state initialization for each and every server call. As per the conventional behavior of the packages, their state is initialized only during the first time they are referenced in a session. This state remains persistent for the complete session and is flushed off only at the termination of the session.