Oracle Database: Advanced PL/SQL — Question 64

Which three are true about PL/SQL subprograms? (Choose three.)

Answer options

• A. Results of a subprogram can be cached in the SGA such that sessions connected to the same instance can reuse these results when available.
• B. Users granted execute privilege on a procedure compiled with definer's rights require grants to access objects belonging to the definer that are referenced in the procedure.
• C. Subprograms are cached by default and shared among users, thereby reducing memory requirements.
• D. Reuse of parsed PL/SQL code from the shared SQL area reduces parsing overhead.
• E. A subprogram's session state is retained even if any of the session’s instantiated subprograms are invalidated and revalidated.
• F. Host variables can be referenced inside any PL/SQL subprogram.
• G. A PL/SQL procedure can invoke an external code block written in a different programming language.

Correct answer: C, D, G

Explanation

Option C is correct because subprograms being cached by default allows for sharing and reduces memory use. Option D is also accurate as it highlights the efficiency gained by reusing parsed code, which minimizes overhead. Option G is true as PL/SQL procedures can indeed invoke external code, while the other options either misrepresent privileges or session states.