RESTRICT_REFERENCES and triggers

Question

I add a PRAGMA RESTRICT_REFERENCES to a procedure in a package (for example, RNPS). That procedure implementation inserts a row in a table.

That table has a before insert trigger. that trigger reads a variable from a package and puts it :new.my_column.

I can compile the package body without problems, even though it seems like it is actually reading values from a package variable.

When I execute the procedure, it actually works. But this is the development eviroment, where there are no multiple simultaneous connections usually. I'm afraid that this could fail in the production enviroment.

So, should I be worried, or will this actually work?

Example code:

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 this would still be useful for already existing code.

Answer 1

RNPS is useful only for functions as it tells the compiler that the return value of a function will not have changed between calls if in the meanwhile no package state (or database state for the other pragmas) has been changed. It allows for caching, but in a more implicit way than with RESULT_CACHE or DETERMINISTIC (which is now the preferred way to tell the optimizer what to expect).

Since you use it on a procedure, it does not really matter. You can safely proceed without changing package specifications. However, since you (or your successor) might ask yourselves the same question a year from now, you might as well change the packages right now while you're at it.