RESTRICT_REFERENCES and triggers

Question

I am adding PRAGMA RESTRICT_REFERENCES to a procedure in a batch (e.g. RNPS). This implementation of the procedure inserts a row into the table.

This table has a trigger before insertion. this trigger reads the variable from the package and puts it: new.my_column.

I can compile the package body without problems, although it seems that it really reads the values ​​from the package variable.

When I execute the procedure, it really works. But this is an evolutionary rationale where usually there are no multiple simultaneous connections. I am afraid that this may fail in the production environment.

So, should I worry, or will it really work?

Code example:

CREATE TABLE MY_TABLE ( ID VARCHAR2(20) NOT NULL , USER_ID VARCHAR2(50) , CONSTRAINT MY_TABLE_PK PRIMARY KEY ( ID ) ENABLE ); CREATE OR REPLACE PACKAGE PUSER IS PROCEDURE saveUser( pUserId VARCHAR2 ); PRAGMA RESTRICT_REFERENCES (saveUser, WNDS, RNDS, RNPS); FUNCTION getUser RETURN VARCHAR2; PRAGMA RESTRICT_REFERENCES (getUser, WNDS, RNDS, WNPS); END PUSER; CREATE OR REPLACE PACKAGE BODY PUSER AS userId VARCHAR2(50); PROCEDURE saveUser( pUserId VARCHAR2 ) IS BEGIN userId := pUserId; END saveUser; FUNCTION getUser RETURN VARCHAR2 IS BEGIN RETURN userId; END getUser; END PUSER; CREATE OR REPLACE PACKAGE MY_PACKAGE IS PROCEDURE insertMyTable( pId VARCHAR2 ); PRAGMA RESTRICT_REFERENCES (insertMyTable, RNPS); END MY_PACKAGE; CREATE OR REPLACE PACKAGE BODY MY_PACKAGE AS PROCEDURE insertMyTable( pId VARCHAR2 ) IS BEGIN INSERT INTO MY_TABLE(id) VALUES(pId); END insertMyTable; END MY_PACKAGE; CREATE OR REPLACE TRIGGER MY_TABLE_TRIGGER BEFORE INSERT ON MY_TABLE FOR EACH ROW DECLARE BEGIN :new.USER_ID := PUSER.getUser; END MY_TABLE_TRIGGER; 

Edit: I know that RESTRICT_REFERENCES is deprecated, but knowing that it will still be useful for existing code.

Answer 1

RNPS is only useful for functions because it tells the compiler that the return value of the function will not change between calls if at the same time the state of the package (or the state of the database for other pragmas) has not been changed. It allows you to cache, but in a more implicit way than with RESULT_CACHE or DETERMINISTIC (now this is the preferred way to tell the optimizer what to expect).

Since you use it in the procedure, it does not really matter. You can safely continue to work without changing package specifications. However, since you (or your successor) can ask yourself the same question in a year, you can also change packages right now while you are on it.