Using PRAGMA - II

[debasisdas]

This thread contains some useful tips/sample codes regarding some of advance concepts in Use of PRAGMA in oracle, that the forum members may find useful.

PRAGMA EXCEPTION_INIT
=============== =====
This is used to bind a user defined exception to a particular error number.

For example: To display USER DEFINED MESSAGE FOR ORACLE DEFINED NUMBER
---------------------
[CODE=oracle]
DECLARE
I EXCEPTION;
PRAGMA EXCEPTION_INIT( I,-00001);
BEGIN
INSERT INTO DEPT VALUES(&DNO,'&D NAME','&LOC');
DBMS_OUTPUT.PUT _LINE('ONE RECORD INSERTED');
EXCEPTION
WHEN I THEN
DBMS_OUTPUT.PUT _LINE('DUPLICAT E VALUE');
END;
[/CODE]

Few more predefined number for predefined exception
=============== =============== ==========

dup_val_on_inde x, -0001
timeout_on_reso urce, -0051
invalid_cursor, -1001
not_logged_on, -1012
login_denied, -1017
too_many_rows, -1422
zero_divide, -1476
invalid_number, -1722
storage_error, -6500
program_error, -6501
value_error, -6502
rowtype_mismatc h, -6504
cursor_already_ open, -6511
access_into_nul l, -6530
collection_is_n ull , -6531
subscript_outsi de_limit, -6532
subscript_beyon d_count , -6533

[debasisdas]

RESTRICT_REFERE NCES Pragma
=============== ============
This pragma was used to assert to the PL/SQL compiler the purity level of a packaged procedure or function. The RESTRICT_REFERE NCES pragma had to be included in the package specification if you were to use that program inside a SQL statement (directly or indirectly).

This is not required starting with Oracle8i.

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_REFERE NCES(function_n ame, 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.

You can declare the pragma RESTRICT_REFERE NCES only in a package spec or object type spec. You can specify up to four constraints (RNDS, RNPS, WNDS, WNPS) in any order. To call the function from parallel queries, you must specify all four constraints. No constraint implies another. For example, WNPS does not imply RNPS.

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.

If you specify DEFAULT instead of a function name, the pragma applies to all functions in the package spec or object type spec (including, in the latter case, the system-defined constructor). You can still declare the pragma for individual functions. Such pragmas override the default pragma.

A RESTRICT_REFERE NCES pragma can apply to only one function declaration. So, a pragma that references the name of overloaded functions always applies to the nearest foregoing function declaration.

syntax
========
[code=oracle]
PRAGMA RESTRICT_REFERE NCES (
function_name, WNDS [, WNPS] [, RNDS] [, RNPS] [, TRUST] );
[/code]

In the following example, the pragma applies to the second declaration of isok:
[code=oracle]
CREATE PACKAGE showrec AS
FUNCTION isok (amount NUMBER) RETURN BOOLEAN;
FUNCTION isok (net_time DATE) RETURN BOOLEAN;
PRAGMA RESTRICT_REFERE NCES (isok, WNDS);
...
END showrec;
[/code]

DEFAULT
----------------
This specifies that the pragma applies to all functions in the package spec or object type spec. You can still declare the pragma for individual functions. Such pragmas override the default pragma.

[debasisdas]

SERIALLY_REUSAB LE:
=============== =====
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_REUSAB LE pragma. This pragma is used in packages only and must be defined BOTH in specification and in the body. A bodiless package can be marked 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.

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.

Note :---Serially reusable packages cannot be accessed from database triggers.

SAMPLE CODE OF PRAGMA SERIALLY_REUSAB LE;
=============== =============== =============
[CODE=oracle]
CREATE PACKAGE pkg1 IS
PRAGMA SERIALLY_REUSAB LE;
num NUMBER := 0;
PROCEDURE init_pkg_state( n NUMBER);
PROCEDURE print_pkg_state ;
END pkg1;

CREATE PACKAGE BODY pkg1 IS
PRAGMA SERIALLY_REUSAB LE;
PROCEDURE init_pkg_state (n NUMBER) IS
BEGIN
pkg1.num := n;
END;

PROCEDURE print_pkg_state IS
BEGIN
dbms_output.put _line('Number: ' || pkg1.num);
END;
END pkg1;
[/CODE]