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.
Declare I_GIVE_UP EXCEPTION; PRAGMA EXCEPTION_INIT(I_give_up, -20000); BEGIN .. EXCEPTION WHEN I_GIVE_UP do something.. END;
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:
PRAGMA RESTRICT_REFERENCES(function_name, WNDS [, WNPS] [, RNDS], [, RNPS])
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.
Dig Deeper on Using Oracle PL-SQL
Related Q&A from Azim Fahmi
There are several ways to back up a database, including schema, table and full exports, as well as hot and cold backups. Here are descriptions of ... Continue Reading