sql_mode=ORACLE: CREATE PACKAGE: PRAGMA RESTRICT_REFERENCES

Let's add support for the RESTRICT_REFERENCES pragma inside the CREATE PACKAGE statement
for sql_mode=ORACLE.

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

Example:

CREATE OR REPLACE PACKAGE pkg1 IS

  FUNCTION add_numbers(A IN NUMBER, B IN NUMBER) RETURN NUMBER;

  PRAGMA RESTRICT_REFERENCES (add_numbers, WNDS, WNPS, RNPS);

  FUNCTION factorial(NUM IN NUMBER) RETURN NUMBER;

  PRAGMA RESTRICT_REFERENCES (factorial, WNDS, WNPS, RNPS);

END pkg1;

/

The grammar of the PRAGMA RESTRICT_REFERENCES statement:

PRAGMA RESTRICT_REFERENCES

 ( { routine_name | DEFAULT } ,

   { RNDS | WNDS | RNPS | WNPS | TRUST }

   [, { RNDS | WNDS | RNPS | WNPS | TRUST } ]... ) ;

Specifying routines the pragma is applicable to:

• routine - The name of a stored routine, typically a function.
  If a function invoke a procedure, then this pragma should also be specified
  for the procedure.

• DEFAULT - Applies the pragma to all routine in the package specification.
  Pragmas in individual routines override the DEFAULT pragma.

Specifying the asserts:

• RNDS - The routine reads no database state (does not query database tables).

• WNDS - The routine writes no database state (does not modify tables).

• RNPS - The routine reads no package state (does not reference the values of package variables).
  One cannot specify RNPS if the routine invokes the SQLCODE or SQLERRM function.

• WNPS - The routine writes no package state (does not change the values of package variables).
  One cannot specify WNPS if the subprogram invokes the SQLCODE or SQLERRM function.

• TRUST - Asserts that the routine can be trusted not to violate the other specified assertions.

The pragma asserts we'll be mapped to SQL/PSM flags:

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

The exact mapping we'll be decided later.