ORACLE PRAGMA

Definition:
In Oracle PL/SQL, PRAGMA refers to a compiler directive or "hint" it is used to provide an instruction to the compiler. The directive restricts member subprograms to query or modify database tables and packaged variables. Pragma directives are processed at compile time where they pass necessary information to the compiler; they are not processed at runtime.

The 5 types of Pragma directives available in Oracle are listed below:

1. PRAGMA AUTONOMOUS_TRANSACTION: This pragma can perform an autonomous transaction within a PL/SQL block between a BEGIN and END statement without affecting the entire transaction.


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


3. PRAGMA RESTRICT_REFRENCES: Defines the purity level of a packaged program. After Oracle8i this is no longer required.


4. PRAGMA EXCEPTION_INIT: This directive binds a user defined exception to a particular error number.


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

Example Syntax:

CREATE OR REPLACE [FUNCTION | PROCEDURE] [NAME] IS

IS

[PRAGMA];

BEGIN

 ...

 ...

END;

Note that PRAGMA resides in the Declarative section of a PL/SQL block.

Example Usage:

The procedure P_ERR_LOG uses the PRAGMA AUTONOMOUS_TRANSACTION directive to capture the error occuring in a program unit.

CREATE OR REPLACE PROCEDURE P_ERR_LOG  (P_UNIT VARCHAR2, P_SQLCODE NUMBER, P_SQLERRM VARCHAR2)

IS

   PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

   INSERT INTO ERR_TABLE

   VALUES (P_UNIT, P_SQLCODE, P_SQLERRM);

   COMMIT;

END;