The pragma PRAGMA RESTRICT_REFERENCES asserts that a function does not read or write database tables or package variables.

The different conditions that you can impose are :
RNDS : The subprogram should not read any database state. It should not query any database tables.
RNPS : The subprogram should not read any package state. It should not reference the values of package variables.
WNDS : The subprogram should not write any database state. It should not modify(insert/delete/update) any database tables.
WNPS : The subprogram should not write any package state. It should not modify the values of package variables.

To use the PRAGMA, just add the following line the package specification:
PRAGMA RESTRICT_REFERENCES(my_function,RNDS,WNDS);
In this case the function ‘my_function’ should not have any insert/update/delete statements(WNDS) and should not query any database tables(RNDS).
WNDS–Write no database state;
RNDS– Read no database state;

CREATE OR REPLACE PACKAGE test_pragma AS
lv_emp_id  number;
FUNCTION test_test1 RETURN NUMBER;
PRAGMA RESTRICT_REFERENCES(test_test1,RNDS,WNDS);
END;

CREATE OR REPLACE PACKAGE BODY test_pragma AS
FUNCTION test_test1 RETURN NUMBER IS
BEGIN

SELECT emp_id INTO lv_emp_id FROM employees
WHERE ROWNUM < 2;
dbms_output.put_line(lv_emp_id );
INSERT INTO employees(emp_id) VALUES (1234);
COMMIT;
RETURN lv_emp_id;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('cant read with restrict references');
END;
END;

On compilation of this package we get the error:

Compilation errors for PACKAGE BODY USR.TEST_PRAGMA
Error: PLS-00452: Subprogram ‘TEST_TEST1′ violates its associated pragma
Line: 2
Text: FUNCTION test_test1 RETURN NUMBER IS[/sql]

as it is both reading and writing a database state.
WNDS–Write no database state;
RNDS– Read no database state;

To correct the changes, either the select employeeid… statement and the insert into statements
should be removed(as restrict references does not allow you to have both WNDS and RNDS statements) or we can use another PRAGMA AUTONOMOUS_TRANSACTION to modify the package body as shown below (although it beats the very purpose of using PRAGMA RESTRICT_REFERENCES :P )

CREATE OR REPLACE PACKAGE BODY test_pragma AS
FUNCTION test_test1 RETURN NUMBER IS PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

SELECT emp_id INTO lv_emp_id FROM employees
WHERE ROWNUM < 2;
dbms_output.put_line(lv_emp_id );
INSERT INTO employees(emp_id) VALUES (1234);
COMMIT;
RETURN lv_emp_id;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('cant read with restrict references');
END;
END;

A better solution would be to have either RNDS or WNDS depending upon the logic.

Note :
1. The pragma RESTRICT_REFERENCES should only be declared in the package specification.
2. You can use either 1,2,3 or all 4 constraints : RNDS, RNPS, WNDS, WNPS.

Related posts:

  1. PRAGMA AUTONOMOUS_TRANSACTION
  2. What is a one-time-only procedure in pl/sql?
  3. Objects in PL/SQL
  4. ROW_NUMBER in ORACLE
  5. '&' vs '&&' in ORACLE

Leave a Reply

*