Pragma

Example of Pragmas in Oracle PLSQL

——————————- PRAGMA SERIALLY_REUSABLE ———-

create or replace package XXC05_TEST_PACKAGES
is
PRAGMA SERIALLY_REUSABLE;
id number:=10;
end XXC05_TEST_PACKAGES;

—–1

begin
XXC05_TEST_PACKAGES.id:=20;
dbms_output.put_line(XXC05_TEST_PACKAGES.id);
end;

—-2

begin
XXC05_TEST_PACKAGES.id:=50;
dbms_output.put_line(XXC05_TEST_PACKAGES.id);
end;

—-3

begin
dbms_output.put_line(XXC05_TEST_PACKAGES.id);
end;

When we Execute 1 then give 20.
When we execute 3 then give 10;
when we execute 2 then give 50;
When we execute 3 then give 10;

———————————————  PRAGMA AUTONOMOUS_TRANSACTION  ——————————————-

In function, If we perform DML then we cant call in select.
But if we use PRAGMA AUTONOMOUS_TRANSACTION in function then we call these function in select;

create or replace function XXC05_FUN002
return number
is
PRAGMA AUTONOMOUS_TRANSACTION;
emp_id number;
begin
insert into XXC05_TEST
values(60,’F’);
commit;
select id
into emp_id
from XXC05_TEST
where id=60;
return (emp_id);
end XXC05_FUN002;

select XXC05_FUN002 from XXC05_TEST;

CREATE OR REPLACE TRIGGER XXC05_PRAGMA_AT_EXAMPLE
BEFORE INSERT OR UPDATE ON XXC05_TEST
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO XXC05_TEST1 VALUES(100,’ABC’);
COMMIT;
END XXC05_PRAGMA_AT_EXAMPLE;

INSERT INTO XXC05_TEST VALUES(100,’SAJAL’);

—————————————— PRAGMA EXCEPTION_INIT  —————————

create or replace function XXC05_PRAGMA_INIT(emp_date in varchar2)
return date
is
e_date date;
begin
e_date:= to_date(emp_date,’YYYY-MON-DD’);
return e_date;
end XXC05_PRAGMA_INIT;

select XXC05_PRAGMA_INIT(‘sajal’) from dual;

——– Solution

create or replace function XXC05_PRAGMA_INIT(emp_date in varchar2)
return date
is
data_exception exception;
pragma exception_init(data_exception,-1841);
e_date date;
begin
e_date:= to_date(emp_date,’YYYY-MON-DD’);
return e_date;
exception
when data_exception
then
dbms_output.put_line(‘Exception created by Sajal’);
return null;
end XXC05_PRAGMA_INIT;

select XXC05_PRAGMA_INIT(‘sajal’) from dual;

Example 2——–

create or replace function XXC05_TOO_MANY_ROW_EXCEP(d_id number)
return number
is
cus_execp_hdl exception;
pragma exception_init(cus_execp_hdl,-1422);
emp_id number;
begin
select employee_id
into emp_id
from employees
where department_id= d_id;
return(emp_id);
exception
when  cus_execp_hdl
then
dbms_output.put_line(‘Custom Error’);
return null;
end XXC05_TOO_MANY_ROW_EXCEP;

select XXC05_TOO_MANY_ROW_EXCEP(20) from dual;

——————————————- PRGMA RESTRICT_REFERENCES

WNDS:-  Write no database State;
RNDS:-  Read no database State;
WNPS:-  Write no package State;
RNPS:-  Read no package State;

If we use “trust” then effect of WNDS/RNDS/WNPS/RNPS is nor reflected;

create or replace package XXC05_PRAGMA_RES_REF
IS
PROCEDURE P1;
PROCEDURE P2;
pragma restrict_references(P1,’RNDS’,’TRUST’);
pragma restrict_references(P2,’RNPS’,’TRUST’);
END XXC05_PRAGMA_RES_REF;

create or replace package BODY XXC05_PRAGMA_RES_REF
IS
PROCEDURE P1
IS
DEP_ID NUMBER;
BEGIN
SELECT DEPARTMENT_ID
INTO DEP_ID
FROM EMPLOYEES
WHERE EMPLOYEE_ID=100;
DBMS_OUTPUT.PUT_LINE(DEP_ID);
END P1;
PROCEDURE P2
IS
DEP_ID NUMBER;
EMP_ID NUMBER:=10;
BEGIN
DBMS_OUTPUT.PUT_LINE(EMP_ID);
END P2;
END XXC05_PRAGMA_RES_REF;