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. Answers.com 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.

AUTONOMOUS_TRANSACTION:

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_TRANSACTION Pragma documentation

Autonomous Transactions article by Tim Hall

RESTRICT_REFERENCES:

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

EXCEPTION_INIT:

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

SERIALLY_REUSABLE:

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

Possibly related:

Tagged , | Post a Comment