PRAGMA in Oracle PL SQL

In Oracle PL/SQL, PRAGMA refers to the compiler directive and it is used to provide an instruction to the compiler. PRAGMA are processes at compile time, not run time. They do not affect the meaning of program, they simply convey information to the compiler. This is the generally asked interview question.
There are five types of PRAGMA in oracle PL SQL:

PRAGMA AUTONOMOUS_TRANSACTION:

This PRAGMA instructs the PL/SQL compiler to mark routine as autonomous i.e. independent. An Autonomous transaction is an independent transaction started by another transaction (main transaction). It let you suspend main transaction, do SQL Operations, commit or rolled back those operations, then resume the main transaction. It does not affect the main transaction.

PRAGMA EXCEPTION_INIT:

This PRAGMA associates an exception name with an oracle error number. It let you refer to any internal exception by name and to write a specific handler for it instead of using the OTHERS handler.

PRAGMA SERIALLY_REUSABLE: This PRAGMA tells the oracle that the state of package is needed only for the duration of one call to the server. After the call is made the package may be unloaded to reclaim the memory.

PRAGMA RESTRICT_REFERENCES: It defines the purity level of packaged program. After oracle 8i, this is no longer required.

PRAGMA INLINE(Introduced in Oracle 11g): This directive tells compiler that whether to to subprogram call to be inlined or not. Inlining replaces a subprogram call with a copy of the called subprogram.

PRAGMA in Oracle PL SQL

Leave a Reply