Wednesday, August 18, 2010

Different PRAGMA's Defined by Oracle

There are various pragma directives used in Oracle PL/SQL interface. I read them recently and thought of listing the useful ones at my blog. Out these fours I've used just two in real life cases, EXECEPTION_INIT and AUTONOMOUS_TRANSACTION.

RESTRICT_REFERENCES Pragma

To be callable from SQL statements, a stored function must obey certain purity rules, which control side-effects. The fewer side-effects a function has, the better it can be optimized within a query, particular when the PARALLEL_ENABLE or DETERMINISTIC hints are used. The same rules that apply to the function itself also apply to any functions or procedures that it calls.

If any SQL statement inside the function body violates a rule, you get an error at run time (when the statement is parsed). 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. Functions that do any of these read or write operations are difficult to optimize, because any call might produce different results or encounter errors.

Syntax

PRAGMA RESTRICT_REFERENCES({Function | DEFAULT}, Database State );

Example: PRAGMA RESTRICT_REFERENCES(log_msg, WNDS, RNDS);

Keyword and Parameter Description

DEFAULT: Specifies that the pragma applies to all subprograms in the package spec or object type spec. You can still declare the pragma for individual subprograms. Such pragmas override the default pragma.

function_name: A user-defined function or procedure.

PRAGMA: Signifies that the statement is a compiler directive. Pragmas are processed at compile time, not at run time. They do not affect the meaning of a program; they convey information to the compiler.

RNDS: Asserts that the subprogram reads no database state (does not query database tables).

RNPS: Asserts that the subprogram reads no package state (does not reference the values of packaged variables)

TRUST: Asserts that the subprogram can be trusted not to violate one or more rules. This value is needed for functions written in C or Java that are called from PL/SQL, since PL/SQL cannot verify them at run time.

WNDS: Asserts that the subprogram writes no database state (does not modify tables).

WNPS: Asserts that the subprogram writes no package state (does not change the values of packaged variables).

You can declare the pragma RESTRICT_REFERENCES only in a package spec or object type spec. You can specify up to four constraints (RNDS, RNPS, WNDS, and WNPS) in any order. To call a function from parallel queries, you must specify all four constraints. No constraint implies another. Typically, this pragma is specified for functions. If a function calls procedures, then specify the pragma for those procedures as well.

When you specify TRUST, the function body is not checked for violations of the constraints listed in the pragma. The function is trusted not to violate them. Skipping these checks can improve performance.

If you specify DEFAULT instead of a subprogram name, the pragma applies to all subprograms in the package spec or object type spec (including the system-defined constructor for object types). You can still declare the pragma for individual subprograms, overriding the default pragma.

A RESTRICT_REFERENCES pragma can apply to only one subprogram declaration. A pragma that references the name of overloaded subprograms always applies to the most recent subprogram declaration.

AUTONOMOUS_TRANSACTION Pragma

The AUTONOMOUS_TRANSACTION pragma changes the way a subprogram works within a 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.

Syntax

PRAGMA AUTONOMOUS_TRANSACTION;

Usage Notes

You can apply this pragma to:

Top-level (not nested) anonymous PL/SQL blocks

Local, standalone, and packaged functions and procedures

Methods of a SQL object type

Database triggers

You cannot apply this pragma to an entire package or an entire an object type. Instead, you can apply the pragma to each packaged subprogram or object method. You can code the pragma anywhere in the declarative section. For readability, code the pragma at the top of the section.

Once started, an autonomous transaction is fully independent. It shares no locks, resources, or commit-dependencies with the main transaction. You can log events, increment retry counters, and so on, even if the main transaction rolls back. Unlike regular triggers, autonomous triggers can contain transaction control statements such as COMMIT and ROLLBACK, and can issue DDL statements (such as CREATE and DROP) through the EXECUTE IMMEDIATE statement.

Changes made by an autonomous transaction become visible to other transactions when the autonomous transaction commits. The changes also become visible to the main transaction when it resumes, but only if its isolation level is set to READ COMMITTED (the default). If you set the isolation level of the main transaction to SERIALIZABLE, changes made by its autonomous transactions are not visible to the main transaction when it resumes.

In the main transaction, rolling back to a savepoint located before the call to the autonomous subprogram does not roll back the autonomous transaction. Remember, autonomous transactions are fully independent of the main transaction. If an autonomous transaction attempts to access a resource held by the main transaction (which cannot resume until the autonomous routine exits), a deadlock can occur. Oracle raises an exception in the autonomous transaction, which is rolled back if the exception goes unhandled.

If you try to exit an active autonomous transaction without committing or rolling back, Oracle raises an exception. If the exception goes unhandled, or if the transaction ends because of some other unhandled exception, the transaction is rolled back.

EXCEPTION_INIT Pragma

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.

Example

DECLARE

deadlock_detected EXCEPTION;

PRAGMA EXCEPTION_INIT(deadlock_detected, -60);

BEGIN

NULL; -- Some operation that causes an ORA-00060 error

EXCEPTION

WHEN deadlock_detected THEN

NULL; -- handle the error

END;

/

Keyword and Parameter Description

error_number: Any valid Oracle error number. These are the same error numbers (always negative) returned by the function SQLCODE.

exception_name: A user-defined exception declared within the current scope.

Usage Notes

You can use EXCEPTION_INIT in the declarative part of any PL/SQL block, subprogram, or package. The pragma must appear in the same declarative part as its associated exception, somewhere after the exception declaration. Be sure to assign only one exception name to an error number.

SERIALLY_REUSABLE Pragma

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 means that every individual call to the code is treated as a new call. That means that no package states or variables are retained.

Syntax

PRAGMA SERIALLY_REUSABLE;

Here's an extreme example of how you might want to use it. Let's say you have a process that takes 2 hours to run. The first thing the process does is call a procedure that requires 500M of memory, but only runs for 30 seconds. Using normal PL/SQL, that memory might stay allocated for 2 hours until the whole process completes. Using this pragma, the memory can be released as soon as the procedure completes, allowing it to be reused while the next 2 hours of processing is completed.

Keyword and Parameter Description

Usage Notes

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. You can mark a bodiless package as serially reusable. If a package has a spec and body, you must mark both.

You cannot mark only the body. The global memory for serially reusable packages is pooled in the System Global Area (SGA), not allocated to individual users in the User Global Area (UGA). That way, the package work area can be reused. When the call to the server ends, the memory is returned to the pool. Each time the package is reused, its public variables are initialized to their default values or to NULL.

Serially reusable packages cannot be accessed from database triggers or other PL/SQL subprograms that are called from SQL statements. If you try, Oracle generates an error.

Pragma SERIALLY REUSABLE is responsible for removing a packages temporary area (for example global variables) after each call and allows for (re)compiling (a new version) of that package without causing an exception such as "ORA-04061: existing state of package has been invalidated". A similar behavior can be achieved by omitting global public (and private) variables in the specification and body of a package. The downside of this pragma is that no package function can be used in a SQL statement.

No comments:

Post a Comment