Details
-
New Feature
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
None
-
None
Description
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.
Attachments
Issue Links
- blocks
-
MDEV-34323 Oracle compatibility project 3
-
- Open
-