Find invalid objects
Published 14 February 2018
An object can be rendered "invalid" if it references objects that don't exist. This is a common occurrence when objects are renamed without also renaming the references in dependent objects.
If an object is invalid it cannot compile and therefore the existence of invalid objects in your database is a good reason to fail the build.
Fortunately it is relatively straightforward to identify invalid objects in Oracle using the following query.
SELECT 'Invalid Object', object_type, object_name FROM dba_objects WHERE status != 'VALID' AND owner = 'SOCO_TEST' ORDER BY object_type;
If invalid objects have slipped into production, after resolving the issue it is recommended for performance reasons to explicitly recompile rather than relying on run-time recompilation.
Here is an example of how to fail the build if invalid objects are detected.
- We start by embedding the above SQL, saves it to a file, get_invalid_objects.sql so we can pass this into sqlplus.
- Using sqlplus, run get_invalid_objects.sql against our test database.
- Using the find command, search the output for "Invalid Object" and, if found, set the ERRORLEVEL to a value that will fail the build.
CI Build - Invalid Objects
rem Save the script that lists invalid objects to a file echo SELECT 'Invalid Object', object_type, object_name FROM dba_objects WHERE status != 'VALID' AND owner = 'SOCO_TEST' ORDER BY object_type; > get_invalid_objects.sql rem Execute the script on the database echo on Call exit | sqlplus SOCO_TEST/demopassword@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=localhost)(Port=1521))(CONNECT_DATA=(SID=XE))) @get_invalid_objects.sql > Artifacts/invalid_objects.txt echo off rem Type the output of the invalid objects query to the console type Artifacts\invalid_objects.txt rem Now search for instances of "Invalid Object" call find /c "Invalid Object" Artifacts/invalid_objects.txt for /f %%A in ('find /c "Invalid Object" ^< Artifacts/invalid_objects.txt') do ( if %%A == 0 ( echo No Invalid Objects SET ERRORLEVEL=0 ) else ( echo Invalid Objects found SET ERRORLEVEL=1 ) )