PRAGMA:
A pragma is generally a line of source code prescribing an action you want the compiler to take. It’s like an option that you give the compiler. it can result in different run time behavior for the program, but it doesn’t get translated directly into byte-code.

SYNTAX:  PRAGMA instruction_to_compiler;

PL/SQL offers several pragmas as listed below:

1. PRAGMA AUTONOMOUS_TRANSACTION
2. PRAGMA EXCEPTION_INIT
3. PRAGMA SERIALLY_REUSABLE
4. PRAGMA RESTRICT_REFERENCES
5. PRAGMA INLINE

1. PRAGMA AUTONOMOUS_TRANSACTION:

Transaction flow in autonomous transaction explained in the below picture:

Defining Autonomous Transactions:

This pragma instructs the PL/SQL compiler to establish a PL/SQL block as autonomous or independent. For the purposes of the
autonomous transaction, a PL/SQL block can be any of the following:

Top-level (but not nested) anonymous PL/SQL blocks

Functions and procedures, defined either in a package or as standalone programs

Methods (functions and procedures) of an object type

Database triggers.

About It:

Once started, an autonomous transaction is fully independent. It shares no locks, resources, or commit-dependencies with the main transaction.

You can log events, increment retry counters, and so on, even if the main transaction rolls back.

Unlike regular triggers, autonomous triggers can contain transaction control statements such as COMMIT and ROLLBACK,and can issue DDL statements (such as CREATE and DROP) through the EXECUTE IMMEDIATE statement.

Changes made by an autonomous transaction become visible to other transactions when the autonomous transaction commits.The changes also become visible to the main transaction when it resumes, but only if its isolation level is set to READ COMMITTED (the default).

If you set the isolation level of the main transaction to SERIALIZABLE, changes made by its autonomous transactions are not visible
to the main transaction when it resumes. (SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;).

If an autonomous transaction attempts to access a resource held by the main transaction (which cannot resume until the autonomous routine exits), a deadlock can occur. Oracle raises an exception in the autonomous transaction, which is rolled back if the exception goes unhandled.

Note:

Transaction properties set in the main transaction apply only to that transaction, not to its autonomous transactions, and vice versa.

Cursor attributes are not affected by autonomous transactions.

Declaring an Autonomous Function in a Package:

CREATE OR REPLACE PACKAGE emp_actions AS — package specification
FUNCTION raise_salary (emp_id NUMBER, sal_raise NUMBER) RETURN NUMBER;
END emp_actions;

CREATE OR REPLACE PACKAGE BODY emp_actions AS — package body
— code for function raise_salary
FUNCTION raise_salary (emp_id NUMBER, sal_raise NUMBER) RETURN NUMBER IS
PRAGMA AUTONOMOUS_TRANSACTION;
new_sal NUMBER(8,2);
BEGIN
UPDATE employees SET salary = salary + sal_raise WHERE employee_id = emp_id;
COMMIT;
SELECT salary INTO new_sal FROM employees WHERE employee_id = emp_id;
RETURN new_sal;
END raise_salary;
END emp_actions;

Declaring an Autonomous Standalone Procedure:

CREATE PROCEDURE lower_salary (emp_id NUMBER, amount NUMBER) AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
UPDATE employees SET salary = salary – amount WHERE employee_id = emp_id;
COMMIT;
END lower_salary;

Declaring an Autonomous PL/SQL Block:

DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
emp_id NUMBER(6);
amount NUMBER(6,2);
BEGIN
emp_id := 200;
amount := 200;
UPDATE employees SET salary = salary – amount WHERE employee_id = emp_id;
COMMIT;

Declaring an Autonomous Trigger:

CREATE TABLE emp_audit ( emp_audit_id NUMBER(6), up_date DATE,
new_sal NUMBER(8,2), old_sal NUMBER(8,2) );

CREATE OR REPLACE TRIGGER audit_sal
AFTER UPDATE OF salary ON employees FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
— bind variables are used here for values
INSERT INTO emp_audit VALUES( :old.employee_id, SYSDATE,
:new.salary, :old.salary );
COMMIT;

When to Use Autonomous Transactions:

Logging mechanism

Perform commits and rollbacks in your database triggers

Reusable application components

Avoid mutating table trigger errors for queries

Call user-defined functions in SQL that modify tables

Retry counter

2. PRAGMA SERIALLY_REUSABLE:

About It:

The SERIALLY_REUSABLE Pragma specifies that the package state is needed for only one call to the server (for example, an OCI call to the database or a stored procedure invocation through a database link). After this call, the storage for the package variables can be reused, reducing the memory overhead for long-running sessions.

The SERIALLY_REUSABLE Pragma can appear in the declare_section of the specification of a package without body, or in both the specification and body of a package, but not in only the body of a package.

The state of a serially reusable package persists only for the lifetime of a CALL to the server. On a subsequent call to the server, if a reference is made to the serially reusable package, Oracle creates a new instantiate of the serially reusable package and initializes all the global variables to NULL or to the default values provided. Any changes made to the serially reusable package state in the previous Calls to the server are not visible.

When to use Serially reusable:

This Pragma is appropriate for packages that declare large temporary work areas that are used once in the same session.

Why Serially Reusable Packages?

Because the state of a non-reusable package persists for the lifetime of the session, this locks up UGA memory for the whole session. In some applications, such as Oracle Office, a log-on session typically exists for days. Applications often need certain packages only for short periods of the session. Ideally, such applications could de-instantiate the package state in after they finish using the package (the middle of the session).

SERIALLY_REUSABLEpackages enable you to design applications that manage memory better for scalability.

For serially reusable packages, the package global memory is not kept in the UGA for each user; rather, it is kept in a small pool and reused for different users. Therefore, the global memory for such a package is only used in a unit of work. After that unit of work, the memory can be released to the pool to be reused by another user.

Drawbacks of Non-reusable Package:

PL/SQL packages usually consume user global area (UGA) memory corresponding to the number of package variables and cursors in the package. This limits scalability, because the memory increases linearly with the number of users. The solution is to allow some packages to be marked as SERIALLY_REUSABLE.

Examine the package with & without SERIALLY_REUSABLE

without SERIALLY_REUSABLE

create or replace package without_serially_reusable_pkg as

v_without_sr int := 0;

Package created.

With serially reusable:

create or replace package with_serially_reusable_pkg as

pragma serially_reusable;

v_with_sr int := 0;

Now, let us assign values to packaged variables

begin

without_serially_reusable_pkg.v_without_sr := 100;

with_serially_reusable_pkg.v_with_sr := 100;

Print the assigned value:

begin

dbms_output.put_line (‘without_serially_reusable_pkg.v_without_sr value is -> ‘ || without_serially_reusable_pkg.v_without_sr );

dbms_output.put_line (‘with_serially_reusable_pkg.v_with_sr values is ->’ || with_serially_reusable_pkg.v_with_sr );

Output : without_serially_reusable_pkg.v_without_sr value is -> 100

with_serially_reusable_pkg.v_with_sr values is ->0

Summary : with_serially_reusable_pkg.v_with_sr is showing 0 because the package is marked as serially_reusable which resets the packaged variable global values to default immediately after its call.

Now, clubbing assignment of variables & displaying the values of variables in single PL/SQL block.

begin

without_serially_reusable_pkg.v_without_sr := 100;

with_serially_reusable_pkg.v_with_sr := 100;

dbms_output.put_line (‘without_serially_reusable_pkg.v_without_sr value is -> ‘ || without_serially_reusable_pkg.v_without_sr );

dbms_output.put_line (‘with_serially_reusable_pkg.v_with_sr values is ->’ || with_serially_reusable_pkg.v_with_sr );

Output: without_serially_reusable_pkg.v_without_sr value is -> 100

with_serially_reusable_pkg.v_with_sr values is ->100

PL/SQL procedure successfully completed.

example 2: (Without serially reusable)

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

CREATE OR REPLACE PACKAGE BODY pkg1 IS
–PRAGMA SERIALLY_REUSABLE;
PROCEDURE init_pkg_state (n NUMBER) IS
BEGIN
pkg1.num := n;
PROCEDURE print_pkg_state IS
BEGIN
dbms_output.put_line(‘Num: ‘ || pkg1.num);
END pkg1;

execute  init_pkg_state(10);

execute  print_pkg_state;

Output: 6

Output : 0  (un-comment the pragma line in the above query)  with serially reusable.

3. PRAGMA RESTRICT_REFERENCES:

PRAGMA RESTRICT_REFERENCES uses to control the side effects of PL/SQL Subprograms. Every PL/SQL Subprograms must follow some rules in terms of transaction control and security.

The RESTRICT REFERENCES Pragma asserts that a user-defined subprogram does not read or write database tables or package variables.

syntax:

PRAGMA RESTRICT_REFERENCES ( [SUBPROGRAM_NAME/DEFAULT] , [RNDS, WNDS, RNPS, WNPS, TRUST]);

DEFAULT

Specifies that the pragma applies to all subprograms in the package specification or object type specification (including the system-defined constructor for object types).

You can still declare the pragma for individual subprograms, overriding the DEFAULT pragma.

Asserts that the subprogram reads no database state (does not query database tables).

Asserts that the subprogram writes no database state (does not modify tables).

Asserts that the subprogram reads no package state (does not reference the values of packaged variables)

You cannot specify RNPS if the subprogram invokes the SQLCODE or SQLERRM function.

Asserts that the subprogram writes no package state (does not change the values of packaged variables).

You cannot specify WNPS if the subprogram invokes the SQLCODE or SQLERRM function.

TRUST

Asserts that the subprogram can be trusted not to violate one or more rules.

When you specify TRUST, the subprogram body is not checked for violations of the constraints listed in the pragma. The subprogram is trusted not to violate them. Skipping these checks can improve performance. TRUST is needed for functions written in C or Java that are invoked from PL/SQL, since PL/SQL cannot verify them at run time.

About It:

The name of a user-defined subprogram, usually a function.

If subprogram_name is overloaded, the pragma applies only to the most recent subprogram declaration.

A RESTRICT_REFERENCES pragma can appear only in a package specification or object type specification. Typically, this pragma is specified for functions. If a function calls procedures, specify the pragma for those procedures also.

To invoke a subprogram from parallel queries, you must specify all four constraints—RNDS, WNDS, RNPS, and WNPS. No constraint implies another.

However, by definition, autonomous routines never violate the rules read no database state (RNDS) and write no database state (WNDS) no matter what they do. This can be useful

Example: declaring restrict reference with autonomous transaction:

When you invoke the packaged function log_msg from a query, it inserts a message into database table debug_output without violating the rule write no database state.

— create the debug table
CREATE TABLE debug_output (msg VARCHAR2(200));

— create the package spec
CREATE PACKAGE debugging AS
FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES(log_msg, WNDS, RNDS);
END debugging;
— create the package body
CREATE PACKAGE BODY debugging AS
FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2 IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
— the following insert does not violate the constraint
— WNDS because this is an autonomous routine
INSERT INTO debug_output VALUES (msg);
COMMIT;
RETURN msg;
END debugging;
— invoke the packaged function from a query
DECLARE
my_emp_id    NUMBER(6);
my_last_name VARCHAR2(25);
my_count     NUMBER;
BEGIN
my_emp_id := 120;
SELECT debugging.log_msg(last_name)
INTO my_last_name FROM employees
WHERE employee_id = my_emp_id;
— even if you roll back in this scope, the insert into ‘debug_output’ remains
— committed because it is part of an autonomous transaction
ROLLBACK;

example for different state:

RNDS:

CREATE OR REPLACE PACKAGE debugging AS
FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES(log_msg, RNDS); — (read no database state)
END debugging;

CREATE OR REPLACE PACKAGE BODY debugging AS
str VARCHAR2(10);
FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2 IS

BEGIN
SELECT msg INTO debugging.str FROM debug_output WHERE msg=’chinu’; –this statement will cause error
INSERT INTO debug_output VALUES (msg);
COMMIT;
RETURN msg;

WNDS:

CREATE OR REPLACE PACKAGE debugging AS
FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES(log_msg, WNDS); — (read no database state)
END debugging;

CREATE OR REPLACE PACKAGE BODY debugging AS
str VARCHAR2(10);
FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2 IS

BEGIN
SELECT msg INTO debugging.str FROM debug_output WHERE msg=’chinu’;
INSERT INTO debug_output VALUES (msg); –this statement will cause error
COMMIT;
RETURN msg;

RNPS:

CREATE OR REPLACE PACKAGE debugging AS
FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES(log_msg, RNPS);
END debugging;

CREATE OR REPLACE PACKAGE BODY debugging AS
str VARCHAR2(10);
FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2 IS

BEGIN
IF str=’hi’ THEN       –this statement will cause error
dbms_output.put_line(str); END IF;
SELECT msg INTO debugging.str FROM debug_output WHERE msg=’chinu’;
INSERT INTO debug_output VALUES (msg);
COMMIT;
RETURN msg;
END debugging;

WNPS:

CREATE OR REPLACE PACKAGE debugging AS
FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES(log_msg, WNPS);
END debugging;

CREATE OR REPLACE PACKAGE BODY debugging AS
str VARCHAR2(10);
FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2 IS

BEGIN
IF str=’hi’ THEN
dbms_output.put_line(str); END IF;
SELECT msg INTO debugging.str FROM debug_output WHERE msg=’chinu’; –this statement will cause error
INSERT INTO debug_output VALUES (msg);
COMMIT;
RETURN msg;
END debugging;

TRUST: This state is something, which is not checking the subprogram body whether it is violating the constraint imposed by pragma or not. generally it is used when a function (written in C or java) is calling from the PL/SQL code.

Example: (Try the below program which imposed “TRUST” pragma state.)

CREATE OR REPLACE PACKAGE BODY debugging AS
str VARCHAR2(10);
FUNCTION log_msg (msg VARCHAR2) RETURN VARCHAR2 IS

BEGIN
IF str=’hi’ THEN
dbms_output.put_line(str);  END IF;
SELECT msg INTO debugging.str FROM debug_output WHERE msg=’chinu’;
INSERT INTO debug_output VALUES (msg);
COMMIT;
RETURN msg;
END debugging;

for other pragma states except “TRUST” will throw error in above subprogram.

4. PRAGMA EXCEPTION_INIT:

The EXCEPTION_INIT pragma associates a user-defined exception name with an Oracle Database error number. You can intercept any Oracle Database error number and write an exception handler for it, instead of using the OTHERS handler.

Syntax: PRAGMA EXCEPTION_INIT(exception_name, -error_number);

Where to use : To handle error conditions (typically ORA- n messages) that have no predefined name, you must use the OTHERS handler or the pragma EXCEPTION_INIT . A pragma is a compiler directive that is processed at compile time, not at run time.

About: In PL/SQL, the pragma EXCEPTION_INIT tells the compiler to associate an exception name with an Oracle Database error number. That lets you refer to any internal exception by name and to write a specific handler for it. When you see an error stack, or sequence of error messages, the one on top is the one that you can trap and handle.

For Example: Lets consider the product table and order_items table from sql joins.

Here product_id is a primary key in product table and a foreign key in order_items table.
If we try to delete a product_id from the product table when it has child records in order_id table an exception will be thrown with oracle code number -2292.
We can provide a name to this exception and handle it in the exception section as given below.

DECLARE
Child_rec_exception  EXCEPTION;
PRAGMA
EXCEPTION_INIT (Child_rec_exception, -2292);
BEGIN
Delete FROM product where product_id= 104;
EXCEPTION
WHEN Child_rec_exception THEN
Dbms_output.put_line(‘Child records are present for this product_id.’);
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy