7 Days of Oracle: Day #4 - Invalid Objects

You receive an emergency request to execute a script that removes a column from a table.  This can be a simple enough task, but you should ask the question "Did removing this column impact other database objects such as views or stored procedures?".  There is a quick and easy way to find out!

 

Before executing a script you should first check to see if there are existing invalid objects.  If you only check after you execute a script then you would need to sift through more data to determine if your change actually broke the object.  If you have a picture of what things looked like before the change, then it's easy to compare after the fact.  Let's go through the process...

 

Check for invalid objects:

 

SQL> select OBJECT_TYPE, OWNER, OBJECT_NAME, STATUS, LAST_DDL_TIME from dba_objects where STATUS='INVALID'

 

OBJECT_TYPE            OWNER                 OBJECT_NAME           STATUS    LAST_DDL_

-----------------------        ----------------------  ----------------------           -------          ---------

PROCEDURE               DEMO                    SELECT_DEMO           INVALID   20-OCT-18

 

 

WOW!  We are just getting started and there's already an invalid object.  Let's try to fix the object with a compile:

 

SQL> ALTER PROCEDURE DEMO.SELECT_DEMO compile;

 

Warning: Procedure altered with compilation errors.

 

It would seem something more than a compile is needed here.  We can get more information about the error and hopefully identify where to focus for resolution.

 

SQL> show err

Errors for PROCEDURE DEMO.SELECT_DEMO:

 

LINE/COL ERROR

-------- -----------------------------------------------------------------

1/28     PLS-00103: Encountered the symbol ")" when expecting one of the

         following:

         <an identifier> <a double-quoted delimited-identifier>

         current delete exists prior

 

Once you have fixed the issue, run the check again to verify there are no invalid objects.

SQL> select OBJECT_TYPE,OWNER,OBJECT_NAME,STATUS,LAST_DDL_TIME from dba_objects where STATUS='INVALID';

no rows selected

 

Now you can execute your script removing the column and check again.  As you can see below, my script broke the same procedure we just fixed.  I just need to repeat the same steps as above to find what broke, then decide how to handle the resolution.

 

OBJECT_TYPE             OWNER                  OBJECT_NAME        STATUS   LAST_DDL_

-----------------------   ----------------------    --------------------       -------       ---------

PROCEDURE               DEMO                     SELECT_DEMO        INVALID   20-OCT-18

 

SQL> ALTER PROCEDURE DEMO.SELECT_DEMO compile;

Warning: Procedure altered with compilation errors.

 

SQL> show err

Errors for PROCEDURE DEMO.SELECT_DEMO:

LINE/COL ERROR

-------- -----------------------------------------------------------------

4/1      PL/SQL: SQL Statement ignored

4/24     PL/SQL: ORA-00904: "ATTRIB3": invalid identifier

SQL>

 

This blog entry merely skimmed the surface of invalid objects.  There are many more things to know and you should take time to do more reading on the topic.   Oracle documentation even offers a solution to compile all objects that are invalid.