PRAGMA directives in Oracle PL/SQL

In Oracle PL/SQL, PRAGMA refers to a compiler directive or "hint" it is used to provide an instruction to the compiler. The directive restricts member subprograms to query or modify database tables and packaged variables. Pragma directives are processed at compile time where they pass necessary information to the compiler; they are not processed at runtime.

The 5 types of Pragma directives available in Oracle are listed below (Four of them exist since Oracle8i while the last one has been introduced with Oracle11g):

1. PRAGMA AUTONOMOUS_TRANSACTION: This pragma can perform an autonomous transaction within a PL/SQL block between a BEGIN and END statement without affecting the entire transaction.


2. PRAGMA SERIALLY_REUSABLE: This directive tels Oracle that the package state is needed only for the duration of one call to the server. After the call is made the package may be unloaded to reclaim memory.


3. PRAGMA RESTRICT_REFRENCES: Defines the purity level of a packaged program. After Oracle8i this is no longer required.


4. PRAGMA EXCEPTION_INIT: This directive binds a user defined exception to a particular error number.


5. PRAGMA INLINE: (Introduced in Oracle 11g) This directive specifies that a subprogram call either is or is not to be inlined. Inlining replaces a subprogram call with a copy of the called subprogram.

Let’s begin with each one by one

PRAGMA EXCEPTION_INIT

This directive allows us to associate an ORA error code to an user-defined PL/SQL exception.
Once the association as been done we’ll be able to manage the exception in our code as it was a predefined exception (just like NO_DATA_FOUND or TOO_MANY_ROWS).
Let’s see an example.

We need a function that converts a string to a date using the ‘YYYY-MM-DD’ format:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

SQL> create or replace function string2date (str in varchar2) return date is   2  retDate date;   3  begin   4    retDate := to_date(str,'yyyy-mm-dd');   5    return retDate;   6  end;   7  / SQL> select string2date('2010-01-31')   2  from dual; STRING2DA --------- 31-JAN-10 SQL> select string2date('werrwer')   2  from dual; select string2date('werrwer')        * ERROR at line 1: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 ORA-06512: at "MAXR.STRING2DATE", line 4

As the example shows, if the input string does not conform to the format we get the ORA-1841 error.
We want to manage this error using the PRAGMA EXCEPTION_INIT directive:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

SQL> create or replace function string2date (str in varchar2) return date is   2  retDate date;   3  not_valid_date exception;   4  PRAGMA EXCEPTION_INIT(not_valid_date,-1841);   5  begin   6    retDate := to_date(str,'yyyy-mm-dd');   7    return retDate;   8  exception   9    when not_valid_date then  10     dbms_output.put_line('Error: the string '||str||' cannot be converted to a date!');  11     return null;  12  end;  13  / SQL> set serverout on SQL> select string2date('werrwer')   2  from dual; STRING2DA --------- Error: the string werrwer cannot be converted to a date!

We’re defining a new exception not_valid_date, but it will be never called if we don’t associate it to the ORA-1841 error using the PRAGMA.
Once we have made the association Oracle knows that, in case of the ORA-1841 error, the not_valid_date exception must be raised.

PRAGMA RESTRICT_REFERENCES 

 It allows us to explicitly declare that a PL/SQL program doesn’t read/write in db objects or in package variables. In some situations, only functions that guarantee those restrictions can be used.

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.

pragma_declaration ::= 
PRAGMA RESTRICT_REFERENCES 
 ({function_name | DEFAULT},
 {RNDS | WNDS| RNPS| WNPS | TRUST} 
 [, {RNDS | WNDS| RNPS| WNPS | TRUST}]...);

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.

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 database 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, WNPS) in any order. To call a function from parallel queries, you must specify all four constraints. No constraint implies another.

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.

The following is a simple example:
Let’s define a package made of a single function that updates a db table and returns a number:

1 2 3 4 5 6 7 8 9 10 11 12 13

SQL> create or replace package pack is   2  function a return number;   3  end;   4  / SQL> create or replace package body pack is   2  function a return number is   3  begin   4    update emp set empno=0 where 1=2;   5    return 2;   6  end;   7  end;   8  /

If we try to use the function pack.a in a query statement we’ll get an error:

1 2 3 4 5 6

SQL> select pack.a from dual; select pack.a from dual        * ERROR at line 1: ORA-14551: cannot perform a DML operation inside a query ORA-06512: a "MAXR.PACK", line 4

PL/SQL functions can be used inside a query statement only if they don’t modify neither the db nor packages’ variables.

This error can be descovered only at runtime, when the select statement is executed.
How can we check for this errors at compile time? We can use PRAGMA RESTRICT_REFERENCES!
If we know that the function will be used in SQL we can define it as follows:

1 2 3 4 5

SQL> create or replace package pack is   2  function a return number;   3  pragma restrict_references(a,'WNDS');   4  end;   5  /

Declaring that the function A will not modify the database state (WNDS stands for WRITE NO DATABASE STATE).
Once we have made this declaration, if a programmer, not knowing that the function has to be used in a query statement, tries to write code for A that violates the PRAGMA:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

SQL> create or replace package body pack is   2  function a return number is   3  begin   4    update emp set empno=0 where 1=2;   5    return 2;   6  end;   7  end;   8  / Warning: Package Body created with compilation errors. SVIL>sho err Errors for PACKAGE BODY PACK: LINE/COL ERROR -------- ----------------------------------------------------------------- 2/1      PLS-00452: Subprogram 'A' violates its associated pragma

He(She)’ll get an error at compile time…

NOTE: Pragma RESTRICT_REFERENCE is deprecated and could be removed from future versions of Oracle.

PRAGMA SERIALLY_REUSABLE 

The PRAGMA tells to the compiler that the package’s variables are needed for a single use. After this single use Oracle can free the associated memory. It’s really useful to save memory when a packages uses large temporary space just once in the session.
Let’s see an example.

Let’s define a package with a single numeric variable “var” not initialized:

1 2 3 4

SQL> create or replace package pack is   2  var number;   3  end;   4  /

If we assign a value to var, this will preserve that value for the whole session:

1 2 3 4 5 6 7

SQL> begin   2  pack.var := 1;   3  end;   4  / SQL> exec dbms_output.put_line('Var='||pack.var); Var=1

If we use the PRAGMA SERIALLY_REUSABLE, var will preserve the value just inside the program that initializes it, but is null in the following calls:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SQL> create or replace package pack is   2  PRAGMA SERIALLY_REUSABLE;   3  var number;   4  end;   5  / SQL> begin   2  pack.var := 1;   3  dbms_output.put_line('Var='||pack.var);   4  end;   5  / Var=1 SQL> exec dbms_output.put_line('Var='||pack.var); Var=

PRAGMA SERIALLY_REUSABLE is a way to change the default behavior of package variables that is as useful as heavy for memory.

PRAGMA AUTONOMOUS_TRANSACTION 

It declare to the compiler that a given program has to run into a dedicated transaction, ignoring all uncommitted data changes made into the original transaction of the calling program.

The sum of salaries in EMP is:

1 2 3 4 5

SQL> select sum(sal) from emp;   SUM(SAL) ----------      29025

Let’s define two functions that do exactly the same thing, read and return the sum of salaries of EMP:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

SQL> create or replace function getsal return number is   2  s number;   3  begin   4    select sum(sal) into s from emp;   5    return s;   6  end;   7  / SQL> create or replace function getsal_AT return number is   2  PRAGMA AUTONOMOUS_TRANSACTION;   3  s number;   4  begin   5    select sum(sal) into s from emp;   6    return s;   7  end;   8  / SQL> select sum(sal), getsal, getsal_AT   2  from emp;   SUM(SAL)     GETSAL  GETSAL_AT ---------- ---------- ----------      29025      29025      29025

The second one uses the PRAGMA AUTONOMOUS_TRANSACTION. Now let’s cut all the salaries:

1 2 3 4 5 6 7 8

SQL>  update emp set sal=10; SQL> select sum(sal), getsal, getsal_AT   2  from emp;   SUM(SAL)     GETSAL  GETSAL_AT ---------- ---------- ----------        140        140      29025

GETSAL is seeing uncommitted changed data while GETSAL_AT, defined using PRAGMA AUTONOMOUS_TRANSACTION, reads data as they where before the UPDATE statement.

PRAGMA INLINE

The only PRAGMA recently added (in Oracle11g) is PRAGMA INLINE.

In Oracle11g has been added a new feature that optimizer can use to get better performances, it’s called Subprogram Inlining.
Optimizer can (autonomously or on demand) choose to replace a subprogram call with a local copy of the subprogram.

For example, assume the following code:

1 2 3 4 5

declare total number; begin  total := calculate_nominal + calculate_interests; end;

Where calculate_nominal and calculate_interests are two functions defined as follows:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

function calculate_nominal return number is s number; begin   select sum(nominal)     into s     from deals;         return s; end; function calculate_interests return number is s number; begin   select sum(interest)     into s     from deals;         return s; end;

Optimizer can change the code to something like this:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

declare total number; v_calculate_nominal number; v_calculate_interests number; begin   select sum(nominal)     into v_calculate_nominal     from deals;   select sum(interest)     into v_calculate_interests     from deals;  total := v_calculate_nominal + v_calculate_interests; end;

Including a copy of the subprograms into the calling program. PRAGMA INLINE is the tool that we own to drive this new feature. 

If we don’t want such an optimization we can do:

1 2 3 4 5 6 7

declare total number; begin  PRAGMA INLINE(calculate_nominal,'NO');  PRAGMA INLINE(calculate_interests,'NO');  total := calculate_nominal + calculate_interests; end;

If we do want subprogram inlining on calculate_nominal we do:

1 2 3 4 5 6

declare total number; begin  PRAGMA INLINE(calculate_nominal,'YES');  total := calculate_nominal + calculate_interests; end;

Subprogram inlining behave differently depending on the level of optimization defined through the db initialization variable PLSQL_OPTIMIZE_LEVEL.

If this variable is set to 2 (that’s the default value) optimizer never uses subprogram inlining unless the programmer requests it using PRAGMA INLINE YES.

If PLSQL_OPTIMIZE_LEVEL=3 optimizer can autonomously decide whether to use subprogram inlining or not. In this case PRAGMA INLINE YES does not force the optimizer, it’s just an hint.