What are the different types of pragma and where can we use them?
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.
PRAGMA EXCEPTION_INIT (I_give_up, -20000);
EXCEPTION WHEN I_GIVE_UP
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. T