PRAGMA

Definition:
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:
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.
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.
PRAGMA RESTRICT_REFRENCES: Defines the purity level of a packaged program. After Oracle8i this is no longer required.
PRAGMA EXCEPTION_INIT: This directive binds a user defined exception to a particular error number.
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.
Example Syntax:

CREATE OR REPLACE [FUNCTION | PROCEDURE] [NAME] IS
IS
[PRAGMA];
BEGIN
 ...
 ...
END;


Note that PRAGMA resides in the Declarative section of a PL/SQL block.

PRAGMA AUTONOMOUS_TRANSACTION:
we create a test table and populate it with two rows notice that the data is not commited.
1>>CREATE TABLE TEST
      (ID NUMBER NOT NULL,
       DESC VARCHAR2(10) NOT NULL
      );
2>>INSERT INTO TEST VALUES(1,'DESC01');
      INSERT INTO TEST VALUES(2,'DESC02');
3>>SELECT  * FROM TEST;
Next, we insert 8 rows using an anonymous block declare an PRAGMA AUTONOMOUS_TRANSACTION
which contain a commit statement.
4>>
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
FOR I IN 3..10
LOOP
INSERT INTO TEST
VALUES(I,'DESC'||I);
END LOOP;
COMMIT;
END;
--(IT COMMIT's ONLY 3 TO 10 RECORDS NOT PREVIOUS 2 RECORDS)

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:

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

Where calculate_nominal and calculate_interests are two functions defined as follows:

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:

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:

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:

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.

PRAGMA SERIALLY_REUSABLE:

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