nerdexam
Oracle

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.

Managing Schema Objects

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)
  • A
    89% (40)
  • B
    4% (2)
  • C
    2% (1)
  • D
    4% (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.

ARecompile the PL/SQL procedure.Correct

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.

BModify the procedure to run with a definer's right.

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.

CModify the procedure to run with an invoker's right.

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.

DGrant the EXECUTE privilege on the PL/SQL procedure to PUBLIC.

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

#invalid object#PL/SQL recompile#object status#schema object validity

Community Discussion

No community discussion yet for this question.

Full 1Z0-052 Practice