When using a packaged function in a query, what is true?
A.
The COMMIT and ROLLBACK commands are allowed in the packaged function.
B.
You can not use packaged functions in a query statement.
C.
The packaged function cannot execute an INSERT, UPDATE, or DELETE statement against the table that is being queried.
D.
The packaged function can execute and INSERT, UPDATE, or DELETE statement against the table that is being queried if it is used in a subquery.
E.
The packaged function can execute an INSERT, UPDATEM or DELETE statement against the table that is being queried if the pragma RESTRICT REFERENCE is used.
Explanation:
A function, stand-alone or package can’t execute DML (INSERT, UPDATE & DELETE) against the table that is being queried. This will result in a mutating table and generate a runtime error.
Incorrect AnswersA: The function must not end the current transaction with COMMIT or ROLLBACK, or ROLLBACK to a
savepoint prior to the function execution.
B: You can use packaged functions in query statements provided they do not violate certain restrictions.
D: This results in a mutating table and will generate a runtime error. E: The PRAGMA RESTRICT_REFERENCES is a compiler directive compiler as to what a function can and cannot do. This compiler processing so that the function conforms to its purity level. This will not prevent the problem of a mutating table.
The question is not a good question: when pragma restrict_reference, means defined RNDS, RNPS, WNDS and WNPS
We can see that WNDS means that “write no database state”, the how the function can execute DML to modify the database state?
RNDS
Asserts that the subprogram reads no database state (does not query database tables).
RNPS
Asserts that the subprogram reads no package state (does not reference the values of packaged variables)
WNDS
Asserts that the subprogram writes no database state (does not modify database tables).
WNPS
Asserts that the subprogram writes no package state (does not change the values of packaged variables).