What are the different types of pragma and where can we use them?

    Requires Free Membership to View

Pragma is a keyword in Oracle PL/SQL that is used to provide an instruction to the compiler.

The syntax for pragmas are as follows


The instruction is a statement that provides some instructions to the compiler.

Pragmas are defined in the declarative section in PL/SQL.

The following pragmas are available:

Prior to Oracle 8.1, each Oracle session in PL/SQL could have at most one active transaction at a given time. In other words, changes were all or nothing. Oracle8i PL/SQL addresses that short comings with the AUTONOMOUS_TRANSACTION pragma. This pragma can perform an autonomous transaction within a PL/SQL block between a BEGIN and END statement without affecting the entire transaction. For instance, if rollback or commit needs to take place within the block without effective the transaction outside the block, this type of pragma can be used.

The most commonly used pragma, this is used to bind a user defined exception to a particular error number.

For example:

 PRAGMA EXCEPTION_INIT(I_give_up, -20000);


do something..


Defines the purity level of a packaged program. This is not required starting with Oracle8i.

Prior to Oracle8i if you were to invoke a function within a package specification from a SQL statement, you would have to provide a RESTRICT_REFERENCE directive to the PL/SQL engine for that function. This pragma confirms to Oracle database that the function as the specified side-effects or ensures that it lacks any such side-effects.

Usage is as follows:


WNDS: Writes No Database State. States that the function will not perform any DMLs.

WNPS: Writes No Package State. States that the function will not modify any Package variables.

RNDS: Reads No Database State. Analogous to Write. This pragma affirms that the function will not read any database tables.

RNPS: Reads No Package State. Analogous to Write. This pragma affirms that the function will not read any package variables.

This pragma lets the PL/SQL engine know that package-level data should not persist between reference to that data.

Package data (global variables in package specification etc.) by default persists for an entire session (or until a package is recompiled). Globally accessible data structures can cause some side effects. For instance, what if a cursor is left open in a package. In addition, a program can use up lots of real memory (UGA) and then not release it if the data is stored in a package-level structure.

In order to manage this, Oracle8i introduced the SERIALLY_REUSABLE pragma. This pragma is used in packages only and must be defined BOTH in specification and in the body.

The advantage is that based on the pragma, a package state can be reduced to a single call of a program unit in the package as opposed to the package being available for the whole session.

This was first published in March 2004

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: