Mega Code Archive

 

Categories / Oracle PLSQL / System Tables Views

 

Query user_objects table for stored procedure before and after recompile

SQL> SQL> SQL> set serveroutput on SQL> SQL> CREATE OR REPLACE PROCEDURE P2 AS   2  BEGIN   3    DBMS_OUTPUT.PUT_LINE('Inside P2!');   4  END P2;   5  / Procedure created. SQL> SQL> CREATE OR REPLACE PROCEDURE P1 AS   2  BEGIN   3    DBMS_OUTPUT.PUT_LINE('Inside P1!');   4    P2;   5  END P1;   6  / Procedure created. SQL> SQL> SQL> SELECT object_name, object_type, status   2    FROM user_objects   3    WHERE object_name IN ('P1', 'P2'); OBJECT_NAME -------------------------------------------------------------------------------- OBJECT_TYPE         STATUS ------------------- ------- P1 PROCEDURE           VALID P2 PROCEDURE           VALID SQL> SQL> ALTER PROCEDURE P2 COMPILE; Procedure altered. SQL> SQL> SELECT object_name, object_type, status   2    FROM user_objects   3    WHERE object_name IN ('P1', 'P2'); OBJECT_NAME -------------------------------------------------------------------------------- OBJECT_TYPE         STATUS ------------------- ------- P1 PROCEDURE           INVALID P2 PROCEDURE           VALID SQL>