Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle PL/SQL PRAGMA Keyword      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

Term: 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;



Related Links:
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 99 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?