News, views, tips and tricks on Oracle and other fun stuff

PRAGMAtism in Oracle PL/SQL

What is a pragma? A pragma is compiler directive. Pragmas are processed at compile time, not at run time. They pass information to the compiler.

The pragma notion is not limited to PL/SQL. Other programming languages have pragmas too. defines a pragma, in the context of Computer Science, as:

A message written into the source code that tells the compiler to compile the program in some fashion that differs from the default method. For example, pragmas may alter the kinds of error messages that are generated or optimize the machine code in some way.

Wikipedia defines pragma, in the context of the word origin, as:

a Greek word (πραγμα), plural pragmata (πραγματα), whose root meaning is “that which has been done, an act, a deed, a fact”, and whose connotations and more extended senses cover a wealth of meanings, including: action, affair, annoyance, business, …

and in the context of Computer Science as:

a compiler directive, data embedded in source code by programmers to tell compilers some intention about compilation. A compiler directive often tells the compiler how to compile; other source code tells the compiler what to compile.

Back to PL/SQL. A PRAGMA is a reserved PL/SQL keyword. In your code, you write a pragma like this:

PRAGMA compiler directive

In general, the compiler expects a PRAGMA (if it exists) to be in the declaration section of a PL/SQL block.

What are the PL/SQL compiler directives that are available to us? As of the current release of PL/SQL, we have 4 directives: AUTONOMOUS_TRANSACTION, RESTRICT_REFERENCES, EXCEPTION_INIT and SERIALLY_REUSABLE.


A subprogram marked with this pragma can do SQL operations and commit or roll back those operations, without committing or rolling back the data in the main transaction. Usage of this pragma should be very limited to specific situations, such as error logging or custom auditing.


Autonomous Transactions article by Tim Hall


To be callable from SQL statements, a stored function must obey certain purity rules, which control side-effects. If any SQL statement inside the function body violates a rule, you get an error at run time. To check for violations of the rules at compile time, you can use the compiler directive PRAGMA RESTRICT_REFERENCES. This pragma asserts that a function does not read and/or write database tables and/or package variables. You can declare the pragma RESTRICT_REFERENCES only in a package spec or object type spec.

RESTRICT_REFERENCES Pragma documentation

Using Pragma RESTRICT_REFERENCES with Dynamic SQL


The pragma EXCEPTION_INIT associates an exception name with an Oracle error number. You can intercept any ORA- error and write a specific handler for it instead of using the OTHERS handler.

EXCEPTION_INIT Pragma documentation

Associating a PL/SQL Exception with a Number: Pragma EXCEPTION_INIT


The pragma SERIALLY_REUSABLE indicates that the package state is needed only for the duration of one call to the server. An example could be an OCI call to the database or a stored procedure call through a database link. After this call, the storage for the package variables can be reused, reducing the memory overhead for long-running sessions.

This pragma is appropriate for packages that declare large temporary work areas that are used once and not needed during subsequent database calls in the same session. Serially reusable packages cannot be accessed from database triggers or other PL/SQL subprograms that are called from SQL statements.

SERIALLY_REUSABLE Pragma documentation

Related AskTom thread

Filed in Oracle on 01 Mar 06 | Tags: ,

Reader's Comments

  1. |

    Hi! building new 10.2 instance is saw other pragmas srcolling on my console … * building the standard package the BUILTIN Pragma :

    create or replace
      2  package STANDARD ...
       function "EXISTS" return BOOLEAN;
    235      pragma BUILTIN('EXISTS',10,240,240); -- This is special cased in PH2 -- Pj

    the FIPSFLAG Pragma …

    function SQLERRM (code PLS_INTEGER) return varchar2;
    294      pragma BUILTIN('SQLERRM',46, 10, 1); -- PEMS_DB, DB_SQLERRM
    295      pragma FIPSFLAG('SQLERRM', 1452);

    the INTERFACE Pragma …

    2734   --#### All user-visible declarations should preceed this point.  The
    2735    --#### following are implementation-oriented pragmas that may need
    2736    --#### editing in the future; we would prefer to be able to edit them
    2737    --#### without affecting the rft numbering of user-visible items.
    2739    --#### interface pragmas
    2741    --#### Note that for any ICD which maps directly to a PVM
    2742    --#### Opcode MUST be mapped to pes_dummy.
    2743    --#### An ICD which invokes another ICD by flipping operands is
    2744    --#### mapped to pes_flip, and an ICD whose result is the inverse of
    2745    --#### another ICD is mapped to pes_invert
    2746    --#### New ICDs should be placed at the end of this list, and a
    2747    --#### corresponding entry must be made in the ICD table in pdz7
    2749    PRAGMA interface(c,length,"pes_dummy",1);
    2750    PRAGMA interface(c,substr,"pes_dummy",1);
    2751    PRAGMA interface(c,instr,"pesist",1);
    2752    PRAGMA interface(c,UPPER,"pesupp",1);
    2753    PRAGMA interface(c,LOWER,"peslow",1);

    Do not know what they mean and if thy can be used in user pl/sql Code. Greetings Karl

  2. |

    […] A few weeks ago I blogged about PRAGMAs in PL/SQL. As you all know, as of the current release of PL/SQL, we have 4 documented pragma directives: AUTONOMOUS_TRANSACTION, RESTRICT_REFERENCES, EXCEPTION_INIT and SERIALLY_REUSABLE. […]

  3. |

    Hello Sir, Your blog helped me gain knowledge about existing Pragma’s but i would like to know if Oracle uses any COMPLIER as such for compiling the Pl/SqL programs

  4. |

    This blog post is giving me a flashback of trying to formulate the explanation for PRAGMA SERIALLY_REUSABLE in the PL/SQL Guide. I’m still wondering if it’s ever needed in real-life situations.

  5. |

    @John: I have used PRAGMA SERIALLY_REUSABLE in real life. In fact, it is in use in one of my production PL/SQL packages today. Here is a related post and comments: