PRAGMA Types in Oracle

PRAGMA Autonomous Transaction:

See the Below Link.

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

DECLARE
I EXCEPTION;
PRAGMA EXCEPTION_INIT(I,-00001);
BEGIN
INSERT INTO DEPT VALUES(&DNO,'&DNAME','&LOC');
DBMS_OUTPUT.PUT_LINE('ONE RECORD INSERTED');
EXCEPTION
WHEN I THEN
DBMS_OUTPUT.PUT_LINE('DUPLICATE VALUE');
END;


Some Predefined exceptions with oracle number:
====================================
dup_val_on_index, -0001
timeout_on_resource, -0051
invalid_cursor, -1001
not_logged_on, -1012
login_denied, -1017


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

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.

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.


PRAGMA RESTRICT_REFERENCES (
    function_name, WNDS [, WNPS] [, RNDS] [, RNPS] [, TRUST] );


CREATE PACKAGE showrec AS
   FUNCTION isok (amount NUMBER) RETURN BOOLEAN;
   FUNCTION isok (net_time DATE) RETURN BOOLEAN;
   PRAGMA RESTRICT_REFERENCES (isok, WNDS);
   ...
END showrec;

Pragma violation errors :
==================

Here is an example of two different purity level assertions for functions in the company_financials package:

PACKAGE company_financials IS  
   FUNCTION company_type (type_code_in IN VARCHAR2)       RETURN VARCHAR2;
   FUNCTION company_name (company_id_in IN company.company_id%TYPE)   RETURN VARCHAR2;  
   PRAGMA RESTRICT_REFERENCES (company_type, WNDS, RNDS, WNPS, RNPS);
   PRAGMA RESTRICT_REFERENCES (company_name, WNDS, WNPS, RNPS);
END company_financials;

In this package, the company_name function reads from the database to obtain the name for the specified company.
 Notice that I placed both pragmas together at the bottom of the package specification


If your function violates its pragma, you will receive the PLS-00452 error.

 Suppose, for example, that the body of the company_financials package looks like this:

CREATE OR REPLACE PACKAGE BODY company_financials IS
 FUNCTION company_type (type_code_in IN VARCHAR2)  RETURN VARCHAR2    IS
    v_sal NUMBER;
    BEGIN  
  SELECT sal INTO v_sal FROM emp WHERE empno = 1;
     RETURN 'bigone';
 END;  
 FUNCTION company_name (company_id_in IN company.company_id%TYPE)  RETURN VARCHAR2    IS
  BEGIN    
 UPDATE emp SET sal = 0;
    RETURN 'bigone';
  END;
 END company_financials;
 /
When I attempt to compile this package body I will get the following error:

3/4    PLS-00452: Subprogram 'COMPANY_TYPE' violates its associated pragma
because the company_type function reads from the database and I have asserted the RNDS purity level.
 If I remove that silly SELECT statement, I will then receive this error:

11/4   PLS-00452: Subprogram 'COMPANY_NAME' violates its associated pragma
because the company_name function updates the database and I have asserted the WNDS level.

SERIALLY_REUSABLE:
====================
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.

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 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.

CREATE PACKAGE pkg1 IS
   PRAGMA SERIALLY_REUSABLE;
   num NUMBER := 0;
   PROCEDURE init_pkg_state(n NUMBER);
   PROCEDURE print_pkg_state;
END pkg1;

CREATE PACKAGE BODY pkg1 IS
   PRAGMA SERIALLY_REUSABLE;
   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;



Error:Pragma restrict_references does not support strings.

Action:Correct syntax or check the spelling of parameters.


Ur's
AmarAlam