Oracle Pragma RESTRICT_REFERENCES

The pragma was introduced to control and preserve the database state. It worked as a watchdog for the packaged function so that they should not modify the package or database state. It restricted the creation of the package, if its member function violated the specified purity level.

The pragma checks purity level of the function in four states

WNDS – DML operations restricted on any table in the database

WNPS –Modification restricted on package variables

RNDS – SELECT query restricted on the database tables

RNPS – Restriction on the selection of package variables

Pragma RESTRICT_REFERENCES Syntax

 [FUNCTION SPECIFICATION]
PRAGMA RESTRICT_REFERENCES([WNDS | WNPS | RNDS | RNPS])

While the Pragma must use minimum one restricted state in its specification, it may contain all the four too.

Pragma RESTRICT_REFERENCES Example Code

Let’s take a look at the usage of pragma and impact of purity level in the below snapshot illustrations.

A package function F_CHANGE_LOC is created in the package PKS_REF_REF with WNPS level check. This implies that it cannot modify the package variable L_LOC.

Impact: Package PKS_RES_REF is created with compilation errors.

The function is created in RNDS purity check. The function would not allow the querying of database tables within its declare section or executable body section.

Impact: Package PKS_RES_REF is created with compilation errors.

The function F_UPD_SAL is created in RNDS purity level specification. The function would not allow the DML operations on the database table within its body.

Impact: Package PKS_RES_REF is created with compilation errors.

{mospagebreak title=Oracle Pragma EXCEPTION_INIT}

Oracle Pragma EXCEPTION_INIT

The pragma EXCEPTION INIT allows the association of a self chosen error number with a user defined exception. The error number can be any of the system recognized error number.

Pragma EXCEPTION_INIT Syntax

PRAGMA EXCEPTION_INIT([EX NAME],[ERROR NUMBER], [TRUE | FALSE])

In the PL/SQL block below, the LOCAL_EXCEPTION exception is linked with the error number -100 through the PRAGMA EXCEPTION_INIT. Note the usage of SQLCODE, which returns the same associated error number.

Pragma EXCEPTION_INIT Example Code

The procedure P_GET_ORDERS has been created to get the order code and order quantity for a given date. If no order booking has been made, procedure must display appropriate message.

CREATE OR REPLACE PROCEDURE P_GET_ORDERS (P_ORD_DATE DATE)
IS
   L_ORD_CODE VARCHAR2(100);
   L_ORD_QTY NUMBER;
   ORD_EXCEPTION EXCEPTION;
   PRAGMA EXCEPTION_INIT(ORD_EXCEPTION, -100);
CURSOR C IS
   SELECT ORD_CODE, ORD_QTY
   FROM ORDERS
   WHERE ORD_DATE= P_ORD_DATE;
BEGIN
   OPEN C;
   FETCH C INTO L_ORD_CODE, L_ORD_QTY;
   IF C%ROWCOUNT = 0 THEN
      RAISE ORD_EXCEPTION;
   END IF;
   CLOSE C;
EXCEPTION
   WHEN ORD_EXCEPTION THEN
     DBMS_OUTPUT.PUT_LINE ('No Orders booked on:'||TO_CHAR(P_ORD_DATE,'DD-MM-YYYY');
END;
/
 
SQL>EXEC P_GET_ORDERS(TRUNC(SYSDATE,'YYYY'));
 
No Orders booked ON 01-01-2011
 
PL/SQL PROCEDURE successfully completed.

Oracle Pragma SERIALLY_REUSABLE

The pragma SERIALLY_REUSABLE enables the package state initialization for each and every server call. As per the conventional behavior of the packages, their state is initialized only during the first time they are referenced in a session. This state remains persistent for the complete session and is flushed off only at the termination of the session.

With the use of the pragma SERIALLY_REUSABLE, the package state is persistent only for a sever call. It gets initialized with every call made to the server while invoking any of its member construct.

Pragma SERIALLY_REUSABLE Syntax

CREATE [OR REPLACE] PACKAGE [NAME] IS
PRAGMA SREIALLY_REUSABLE
   [MEMBER CONSTRUCT PROTOTYPES]
END;
 
CREATE [OR REPLACE] PACKAGE BODY [NAME] IS
PRAGMA SREIALLY_REUSABLE
   [MEMBER CONSTRUCT DEFINTIONS]
END;

Pragma SERIALLY_REUSABLE Example Code

The package PKG_TEST_PRAGMA demonstrates non persistent package state at every server call.

CREATE OR REPLACE PACKAGE PKG_TEST_PRAGMA
IS
PRAGMA SERIALLY_REUSABLE;
   L_PKG_STATE NUMBER := 1;
   PROCEDURE ASSIGN_SUM_VALUE(P_FACTOR NUMBER);
   FUNCTION DISPLAY_VALUE RETURN NUMBER;
END;
 
Package created.
 
CREATE OR REPLACE PACKAGE BODY PKG_TEST_PRAGMA
IS
PRAGMA SERIALLY_REUSABLE;
   PROCEDURE ASSIGN_SUM_VALUE (P_FACTOR NUMBER)
   IS
   BEGIN
      L_PKG_STATE:= L_PKG_STATE + P_FACTOR;
      DBMS_OUTPUT.PUT_LINE(‘CHANGE IN package state’|| L_PKG_STATE);
   END;
 
   FUNCTION DISPLAY_VALUE RETURN NUMBER
   IS 
   BEGIN
      RETURN L_PKG_STATE;
   END;
END;
 
Package body created.
 
SET SERVEROUT ON
BEGIN
  DBMS_OUTPUT.PUT_LINE(‘Package state BEFORE CALL: ’||PKG_TEST_PRAGMA.DISPLAY_VALUE);
  PKG_TEST_PRAGMA.ASIGN_SUM_VALUE(5);
  DBMS_OUTPUT.PUT_LINE(‘Package state after CALL: ’||PKG_TEST_PRAGMA.DISPLAY_VALUE);
END;
/
 
Package state BEFORE CALL: 1
CHANGE IN package state: 6
Package state after CALL: 1
 
PL/SQL PROCEDURE successfully completed.

{mospagebreak title=Oracle Pragma Inline}

Oracle Pragma Inline

Inlining is an effective feature to gain performance benefits in a procedural and structural code. During compilation, oracle tends to replace the inline subprogram call with its definition itself.

The INLINE pragma is a latest induction in the family. It is used to manually inlining a subprogram when the optimizer level is set as 2. It has to be specified once, just before the subprogram call. The inlining effect would remain persistent for all the subsequent calls to the subprogram; unless and until the NO pragma is specified for the subprogram.

Oracle Pragma Inline Example Syntax

PRAGMA INLINE (subprogram name, [YES | NO]);

For quick reference, PLSQL_OPTIMIZE_LEVEL parameter specifies the optimization level of the subprogram.

• If it is 0, then the optimizer follows ‘NO inlining’ principle.

• For the value of 1, the optimizer follows intermediate inlining like working with iterative constants.

• For optimizer level 2, the optimizer takes the path of ‘Intelligent inlining’ i.e. it would inline the subprogram by virtue of its intelligence. This is the appropriate and default level for the optimizer and provides scalability to manually inline the subprograms using PRAGMA INLINE.

• At optimizer level 3, optimizer is forced to inline all possible subprograms at high priority, which is not commendable from developer’s perspective.

Oracle Pragma Inline Example Code

For demonstration purpose, the session has been altered to set PLSQL_OPTIMIZE_LEVEL at 2.

ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=2
 
SESSION altered.

Oracle Pragma Inline Example Code

Now, for example, I have to calculate sum of factorial series in the format as 1! + 2! + 3! +…+N! I create a procedure P_CALC_SERIES and define a local function FACTORIAL to get the factorial of a number. I would inline the local function call using PRAGMA INLINE as shown below.

CREATE OR REPLACE PROCEDURE P_CALC_SERIES(P_LIMIT NUMBER)
IS
  L_SERIES NUMBER := 0;
  FUNCTION FACTORIAL (P_NUM NUMBER)    RETURN NUMBER IS
    L_FACT NUMBER := 1;
  BEGIN
    FOR I IN 1..P_NUM
    LOOP
       L_FACT := L_FACT*I;
    END LOOP;
    RETURN L_FACT;
  END FACTORIAL;
 
BEGIN
  FOR J IN 1 .. P_LIMIT 
  LOOP
    PRAGMA INLINE (FACTORIAL, 'YES');
    L_SERIES := L_SERIES + FACTORIAL(I);
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Sum of the series is '||TO_CHAR(L_SERIES));
END;
/
 
PROCEDURE created.