Oracle Pragma -- A compiler directive or "hint"

The language compiler

In a programming language, compiler plays a crucial role in execution of a program. Compiler is a language component which converts a source code, written using one of the interactive programming languages, into machine code, which the server can interpret and act upon.

As a refresher, Oracle compilation technique took a wide turn in 11g release with the induction of real native compilation. The C compiler has been replaced with the native one, thus justifying the ‘real native compilation technique’.

Pragma: The HOW factor in compilation

Oracle has inducted few keywords in its glossary which behave as a clue to the compiler. These keywords are named as Pragma(s), which are read by the compiler to be decisive over the method used for the compilation of current program unit. Note that the method of compilation of a program unit manipulates its run-time behavior.

Being the part of source code, the pragma statement tips the compiler to differently compile the program unit, thus manipulating the behavior of the program unit, currently under compilation. Note that the pragma is interpreted only at the time of compilation and not at the time of execution.

Types of Pragmas

Until Oracle 11g, PL/SQL programming language could make use of five types of pragma(s) which are listed as below.

AUTONOMOUS_TRANSACTION – Compiler allows schema objects like subprograms, PL/SQL blocks, or triggers to commit their transaction within their scope.

RESTRICT_REFERENCES – Allows the compiler to scan the database purity during package compilation. It can be used in packages only.

EXCEPTION_INIT – Compiler associates a user defined exception name with an exception number.

SERIALLY_REUSABLE – Compiler deduces the package state from session level to just a server call. It can be used in Package spec only.

PRAGMA INLINE – New member of oracle pragma family. It is used to explicitly inline a program unit.

Oracle Pragma AUTONOMOUS_TRANSACTION

Oracle 8i release 1 saw the introduction of a new language feature, which allowed having simultaneous active transactions in the same database session.

The autonomous pragma allows a separate active transaction, which is independent of the main transaction in the session. The COMMIT/ROLLBACK done within the autonomous object does not affect the main transaction and vice versa. All in all, it is an individual transaction and not the part of main transaction.

Within a database schema, a standalone or nested subprogram, a trigger, an anonymous PL/SQL block, object type member method or packaged subprogram can be declared as autonomous.

Autonomous Transactions: Working

Often, users refer autonomous transaction as nested transaction, which is wrong. It would be completely mislead the philosophy behind the autonomous transactions. Vocally, there is nothing called nested transactions in database. There is only one main running transaction in a session, which contains all DML statements issued in the session. Single TCL command (commit/rollback) feeds the changes permanently into the database. Autonomous transactions are the voluntary ones which are activated within the main transaction, but share no relationship with it.

For better understanding purpose, autonomous transactions are executed in a separate temporary session created by the database server. The main transaction remains in passive or suspended state until the autonomous transaction is active. As soon as the autonomous transaction is completed, the main transaction resumes the action.

Pragma AUTONOMOUS_TRANSACTION Syntax

 [DECLARE | SUBPROGRAM HEADER]
   PRAGMA AUTONOMOUS_TRANSACTION
   [VARIABLE DECLARATION]
BEGIN
   [EXECUTABLE SECTION]
   [COMMIT | ROLLBACK]
END;

Pragma AUTONOMOUS_TRANSACTION Example Code 1

Suppose I have to implement error tracing mechanism in an application. The mechanism should trace all the exceptions raised in the application along with its host object details. The mechanism must handle the exception propagation situations and rolling back situations.

I create a procedure P_TRACK_ERROR as below. I capture and insert the required information into a table and mark this operation as autonomous transaction. In this way, I segregate the transaction performed in this procedure, from the main transaction. Therefore, even if the raised exception causes rollback to the current ongoing transaction, P_TRACK_ERROR would successfully capture the exception details.

CREATE OR REPLACE PROCEDURE P_TRACK_ERROR(P_CALLED_FROM VARCHAR2,
                                          P_OBJ_TYPE VARCHAR2,
                                          P_LINE VARCHAR2,
                                          P_SQLCODE VARCHAR2,
                                          P_SQLMSG VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO SESSION_ERROR_TRACK (USERNAME,
                                 ERR_DATE,
                                 OBJ_NAME, 
                                 OBJ_TYPE,
                                 LINE,
                                 ERR_CODE,
                                 ERR_MSG)
VALUES (USER, SYSDATE, P_CALLED_FROM, P_OBJ_TYPE, P_LINE, P_SQLCODE, P_SQLMSG);
COMMIT;
END;

Let’s take a look at the above procedure to record the errors.

Pragma AUTONOMOUS_TRANSACTION Example Code 2

DECLARE
   L_ID VARCHAR2(10):='a';
   L_DESC VARCHAR2(1000):='TEST_DESC';
BEGIN
   INSERT INTO PRODUCT VALUES (L_ID, L_DESC);
   UPDATE PRODUCT
   SET PRODUCT_DESCRIPTION = 'Product '||PRODUCT_ID;
EXCEPTION
   WHEN OTHERS THEN
      P_TRACK_ERROR(COALESCE ($$plsql_unit, 'anonymous block'),$$PLSQL_TYPE,$$PLSQL_LINE,SQLCODE,SQLERRM);
END;

Query the error log table SESSION_ERROR_TRACK to check the entry from the above block

Pragma AUTONOMOUS_TRANSACTION Example Code 3

SQL>SELECT USERNAME, ERR_DATE, OBJ_NAME, ERR_CODE, ERR_MSG 
     FROM SESSION_ERROR_TRACK;
 
USERNAME   ERR_DATE  OBJ_NAME        ERR_CODE   ERR_MSG
---------- --------- --------------- ---------- -------------------------
SCOTT      21-JUN-11 anonymous block -1722      ORA-01722: invalid NUMBER

{mospagebreak title=Oracle Pragma RESTRICT_REFERENCES}

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