1Z0-052 · Question #192
In your database, the current status of a PL/SQL procedure has become INVALID. Which action would fix the problem and make the PL/SQL procedure usable?
The correct answer is A. Recompile the PL/SQL procedure. A PL/SQL procedure becomes INVALID when a dependent object it references is altered or dropped. Recompiling the procedure re-parses and re-validates it against the current state of its dependencies.
Question
In your database, the current status of a PL/SQL procedure has become INVALID. Which action would fix the problem and make the PL/SQL procedure usable?
Options
- ARecompile the PL/SQL procedure.
- BModify the procedure to run with a definer's right.
- CModify the procedure to run with an invoker's right.
- DGrant the EXECUTE privilege on the PL/SQL procedure to PUBLIC.
How the community answered
(45 responses)- A89% (40)
- B4% (2)
- C2% (1)
- D4% (2)
Why each option
A PL/SQL procedure becomes INVALID when a dependent object it references is altered or dropped. Recompiling the procedure re-parses and re-validates it against the current state of its dependencies.
Executing ALTER PROCEDURE <name> COMPILE forces Oracle to recompile the stored procedure, resolving any dependency invalidations by re-checking referenced objects and regenerating the compiled p-code; if all dependencies are valid, the procedure status returns to VALID and becomes executable.
Changing the rights model to definer's rights affects which schema's privileges are used at runtime, but does not recompile the procedure or remove the INVALID status caused by a broken dependency.
Switching to invoker's rights similarly changes runtime privilege resolution and has no effect on the compilation error or INVALID status of the stored object.
Granting EXECUTE to PUBLIC allows more users to call the procedure but does not trigger recompilation or resolve the underlying dependency issue that caused the INVALID status.
Concept tested: Recompiling invalid PL/SQL stored procedures
Source: https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/ALTER-PROCEDURE-statement.html
Topics
Community Discussion
No community discussion yet for this question.