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:
1.
PRAGMA EXCEPTION_INIT: This directive binds a user defined exception to a particular error number.
2.
PRAGMA RESTRICT_REFRENCES: Defines the purity level of a packaged program. After Oracle8i this is no longer required.
3.
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.
4.
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.
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.
1.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:
create or replace function string2date (str in varchar2) return date is
retDate date;
begin
retDate := to_date(str,'yyyy-mm-dd');
return retDate;
end;
select string2date('2010-01-31')
from dual;
STRING2DATE
---------
31-JAN-10
select string2date('werrwer')
from dual;
select string2date('werrwer')
from dual
*
Error at line 1
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
ORA-06512: at "HR.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:
create or replace function string2date (str in varchar2) return date is
retDate date;
not_valid_date exception;
PRAGMA EXCEPTION_INIT(not_valid_date,-1841);
begin
retDate := to_date(str,'yyyy-mm-dd');
return retDate;
exception
when not_valid_date then
dbms_output.put_line('Error: the string '||str||' cannot be converted to a date!');
return null;
end;
SQL> select string2date('werrwer') from dual;
STRING2DA
---------
Error: the string werrwer cannot be converted to a date!
SQL>
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.
2.PRAGMA RESTRICT_REFRENCES:
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 following is a simple example:
Let’s define a package made of a single function that updates a db table and returns a number:
SQL> create or replace package pack is
2 function a return number;
3 end pack;
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 pack;
8 /
If we try to use the function pack.a in a query statement we’ll get an error:
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: at "HR.PACK", line 4
SQL>
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:
SQL> create or replace package pack is
2 function a return number;
3 pragma restrict_references(a,'WNDS');
4 end pack;
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:
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.
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/5 PLS-00452: Subprogram 'A' violates its associated pragma
He(She)’ll get an error at compile time…
Pragma RESTRICT_REFERENCE is deprecated and could be removed from future versions of Oracle.
3.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.
A package once executed will have its variables in UGA (User Global Area). This is by default.
If you are creating a package which will be executed only once, then the memory used by the package could be freed up after its execution.
It is using a pragma (hint to compiler) that we do so. This directive is called as serially_reusable.
It tells the compiler to run the package and free the space once its executed.
The compiler on getting this directive does not save the program variables in UGA but it does in
SGA (Shared Global Area).
Each time the package with serially_reusable directive is called,
its public variables are initialized. But in a normal package its not
initialized every time the package is called.
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 pack;
4 /
Package created.
SQL> set serveroutput on
SQL> begin
2 pack.var:=1;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> exec dbms_output.put_line('Var='||pack.var);
Var=1
PL/SQL procedure successfully completed.
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
PL/SQL procedure successfully completed.
SQL> exec dbms_output.put_line('Var='||pack.var);
Var=
PL/SQL procedure successfully completed.
PRAGMA SERIALLY_REUSABLE is a way to change the default behavior of package variables
that is as useful as heavy for memory.
4. PRAGMA AUTONOMOUS_TRANSACTION:
Autonomous transactions allow you to leave the context of the calling transaction,
perform an independant transaction, and return to the calling transaction without affecting it's state.
The autonomous transaction has no link to the calling transaction,
so only commited data can be shared by both transactions.
he following types of PL/SQL blocks can be defined as autonomous transactions:
Stored procedures and functions.
Local procedures and functions defined in a PL/SQL declaration block.
Packaged procedures and functions.
Type methods.
Top-level anonymous blocks.
CREATE TABLE at_test (
id NUMBER NOT NULL,
description VARCHAR2(50) NOT NULL
);
INSERT INTO at_test (id, description) VALUES (1, 'Description for 1');
INSERT INTO at_test (id, description) VALUES (2, 'Description for 2');
SELECT * FROM at_test;
ID DESCRIPTION
---------- --------------------------------------------------
1 Description for 1
2 Description for 2
2 rows selected.
SQL>
Next, we insert another 8 rows using an anonymous block declared as an autonomous transaction,
which contains a commit statement.
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
FOR i IN 3 .. 10 LOOP
INSERT INTO at_test (id, description)
VALUES (i, 'Description for ' || i);
END LOOP;
COMMIT;
END;
/
PL/SQL procedure successfully completed.
SELECT * FROM at_test;
ID DESCRIPTION
---------- --------------------------------------------------
1 Description for 1
2 Description for 2
3 Description for 3
4 Description for 4
5 Description for 5
6 Description for 6
7 Description for 7
8 Description for 8
9 Description for 9
10 Description for 10
10 rows selected.
SQL>
As expected, we now have 10 rows in the table. If we now issue a rollback statement we get the following result.
ROLLBACK;
SELECT * FROM at_test;
ID DESCRIPTION
---------- --------------------------------------------------
3 Description for 3
4 Description for 4
5 Description for 5
6 Description for 6
7 Description for 7
8 Description for 8
9 Description for 9
10 Description for 10
8 rows selected.
SQL>
The 2 rows inserted by our current session (transaction) have been rolled back, while the rows inserted by the autonomous transactions remain. The presence of the PRAGMA AUTONOMOUS_TRANSACTION compiler directive made the anonymous block run in its own transaction, so the internal commit statement did not affect the calling session. As a result rollback was still able to affect the DML issued by the current statement.
Autonomous transactions are commonly used by error logging routines, where the error messages must be preserved, regardless of the the commit/rollback status of the transaction.
For example, the following table holds basic error messages.
CREATE TABLE error_logs (
id NUMBER(10) NOT NULL,
log_timestamp TIMESTAMP NOT NULL,
error_message VARCHAR2(4000),
CONSTRAINT error_logs_pk PRIMARY KEY (id)
);
CREATE SEQUENCE error_logs_seq;
We define a procedure to log error messages as an autonomous transaction.
CREATE OR REPLACE PROCEDURE log_errors (p_error_message IN VARCHAR2) AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO error_logs (id, log_timestamp, error_message)
VALUES (error_logs_seq.NEXTVAL, SYSTIMESTAMP, p_error_message);
COMMIT;
END;
/
The following code forces an error, which is trapped and logged.
BEGIN
INSERT INTO at_test (id, description)
VALUES (998, 'Description for 998');
-- Force invalid insert.
INSERT INTO at_test (id, description)
VALUES (999, NULL);
EXCEPTION
WHEN OTHERS THEN
log_errors (p_error_message => SQLERRM);
ROLLBACK;
END;
/
PL/SQL procedure successfully completed.
SELECT * FROM at_test WHERE id >= 998;
no rows selected
SELECT * FROM error_logs;
ID LOG_TIMESTAMP
---------- -------------------------------
ERROR_MESSAGE
--------------------------------------------------------------------------------
2 17-MAR-14 01.18.23.828000 AM
ORA-01400: cannot insert NULL into ("HR"."AT_TEST"."DESCRIPTION")
1 row selected.
From this we can see that the LOG_ERRORS transaction was separate to the anonymous block.
If it weren't, we would expect the first insert in the anonymous block to be preserved by
the commit statement in the LOG_ERRORS procedure.
5.PRAGMA INLINE:
When you call subprogram(procedure or function) in your code multiple time, in every call it will be loaded again so it will take more time to execute.
To enhance previous drawback we use inline to replace the subprogram call with the copy of already called subprogram before.
PRAGMA INLINE directive is new feature provided since oracle database 11g.
The PRAGMA INLINE directive is used to determine if subprogram call is inline or not.
The PRAGMA INLINE is controlled by PLSQL_OPTIMIZE_LEVEL parameter value
You can change PLSQL_OPTIMIZE_LEVEL by any of below commands
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=2;
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL=3;
1-If PLSQL_OPTIMIZE_LEVEL is equal to 2, then the inline is done regarding PRAGMA INLINE Value, IF PRAGMA INLINE value is YES, it will be inlined otherwise not inlined
2-If PLSQL_OPTIMIZE_LEVEL is equal to 3, then the inline is done with high priority regarding PRAGMA INLINE Value, IF PRAGMA INLINE value is YES, it will be inlined with high priority otherwise not inlined
How to use PRAGMA INLINE
You can use it anywhere in your code and it has below syntax
PRAGMA INLINE(subprogram_name,'YES');
PRAGMA INLINE(subprogram_name,'NO');
You can use PRAGMA INLINE in precedence of the following statements
1- Assignment
2- Call
3- Conditional
4- CASE
5- CONTINUE-WHEN
6- EXECUTE IMMEDIATE
7- EXIT-WHEN
8- LOOP
9- RETURN
Demo
1- I will create anonymous block for calling subprogram without using PRAGMA INLINE and print the time used to execute the code
DECLARE
LN$START_TIME NUMBER;
LN$END_TIME NUMBER;
LN$RESULT NUMBER;
FUNCTION ADD_FIVE (IN_NUMBER1 NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN NVL (IN_NUMBER1, 0) + 5;
END;
BEGIN
LN$START_TIME := DBMS_UTILITY.GET_TIME;
FOR I IN 1 .. 10000000
LOOP
LN$RESULT := ADD_FIVE (I);
END LOOP;
LN$END_TIME := DBMS_UTILITY.GET_TIME;
DBMS_OUTPUT.
PUT_LINE ('Time Elapsed is ' || TO_CHAR (LN$END_TIME - LN$START_TIME));
END;
The output in DBMS Output Console:
Time Elapsed is 678
PL/SQL procedure successfully completed.
DECLARE
LN$START_TIME NUMBER;
LN$END_TIME NUMBER;
LN$RESULT NUMBER;
FUNCTION ADD_FIVE (IN_NUMBER1 NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN NVL (IN_NUMBER1, 0) + 5;
END;
BEGIN
LN$START_TIME := DBMS_UTILITY.GET_TIME;
FOR I IN 1 .. 10000000
LOOP
PRAGMA INLINE (ADD_FIVE, 'YES');
LN$RESULT := ADD_FIVE (I);
END LOOP;
LN$END_TIME := DBMS_UTILITY.GET_TIME;
DBMS_OUTPUT.
PUT_LINE ('Time Elapsed is ' || TO_CHAR (LN$END_TIME - LN$START_TIME));
END;
The output in DBMS Output Console:
Time Elapsed is 235
PL/SQL procedure successfully completed.
Conclusion
Executing calling subprogram with PRAGMA INLINE takes 678 and without it takes 235, So using using PRAGMA INLINE provide us with high performance.
Cheers
Rajani
