Oracle Database: Program with PL/SQL — Question 20

Examine this function header:
FUNCTION calc_new_sal (emp_id NUMBER) RETURN NUMBER;
You want to ensure that whenever this PL/SQL function is invoked with the same parameter value across active sessions, the result is not recomputed.
If a DML statement is modifying a table which this function depends upon, the function result must be recomputed at that point in time for all sessions calling this function.
Which two actions should you perform?

Answer options

• A. Ensure RESULT_CACHE_MAX_SIZE is greater than 0.
• B. Enable the result cache by using DBMS_RESULT_CACHE.BYPASS (FALSE).
• C. Add the deterministic clause to the function definition.
• D. Add the RELIES_ON clause to the function definition.
• E. Add the RESULT_CACHE clause to the function definition.

Correct answer: A, C

Explanation

The correct answer includes options A and C. Ensuring RESULT_CACHE_MAX_SIZE is greater than 0 allows the result cache to store results, while adding the deterministic clause guarantees that the function will always yield the same result for the same input. The other options do not contribute to achieving the desired caching behavior effectively.