PL/SQL: What is PRAGMA in PL SQL

 

PRAGMA in Oracle PL SQL

In Oracle PL/SQL, PRAGMA refers to the compiler directive and it is used to provide an instruction to the compiler. PRAGMA are processes at compile time, not run time. They do not affect the meaning of program, they simply convey information to the compiler. This is the generally asked interview question. There are five types of PRAGMA in oracle PL SQL

PRAGMA AUTONOMOUS_TRANSACTION:

This PRAGMA instructs the PL/SQL compiler to mark routine as autonomous i.e. independent. An Autonomous transaction is an independent transaction started by another transaction (main transaction). It let you suspend main transaction, do SQL Operations, commit or rolled back those operations, then resume the main transaction. It does not affect the main transaction.

PRAGMA EXCEPTION_INIT:

This PRAGMA associates an exception name with an oracle error number. It let you refer to any internal exception by name and to write a specific handler for it instead of using the OTHERS handler.

PRAGMA SERIALLY_REUSABLE:

This PRAGMA tells the oracle that the state of package is needed only for the duration of one call to the server. After the call is made the package may be unloaded to reclaim the memory.

PRAGMA RESTRICT_REFERENCES:

It defines the purity level of packaged program. After oracle 8i, this is no longer required.

PRAGMA INLINE(Introduced in Oracle 11g):

This directive tells compiler that whether to to subprogram call to be inlined or not. Inlining replaces a subprogram call with a copy of the called subprogram.

PL/SQL PRAGMA EXCEPTION_INIT

PL/SQL user named exception. you can define your own error message and error number using Pragma EXCEPTION_INIT or RAISE_APPLICATION_ERROR function.

PL/SQL pragma EXCEPTION_INIT

pragma EXCEPTION_INIT: Pragma is a keyword directive to execute proceed at compile time. pragma EXCEPTION_INIT function take this two argument,

1. exception_name
2. error_number

You can define pragrma EXCEPTION_INIT in DECLARE BLOCK on your program.

PRAGMA EXCEPTION_INIT(exception_name, -error_number);

exception_name and error_number define on yourself, where exception_name is character string up to 2048 bytes support and error_number is a negative integer range from -20000 to -20999.

Syntax

DECLARE
    user_define_exception_name EXCEPTION;
    PRAGMA EXCEPTION_INIT(user_define_exception_name,-error_number);
BEGIN
    statement(s);
    IF condition THEN
        RAISE user_define_exception_name;
    END IF;
EXCEPTION
    WHEN user_define_exception_name THEN
        User defined statement (action) will be taken;
END;

Example

user-named_exp.sql

SQL>edit user-named_exp
DECLARE
    myex EXCEPTION;
    PRAGMA EXCEPTION_INIT(myex,-20015); 
    n NUMBER := &n;
BEGIN
    FOR i IN 1..n LOOP
        dbms_output.put.line(i);
        IF i=n THEN
            RAISE myex;
        END IF;
    END LOOP;
EXCEPTION
    WHEN myex THEN
        dbms_output.put.line('loop finish');
END;
/

Result

SQL>@user-named_exp
n number &n= 5
1
2
3
4
5
loop finish

PL/SQL procedure successfully operation