Database DevOps for Oracle

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.

get_invalid_objects.sql Toggle source code

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.

Worked example

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 Toggle source code

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
  )
)

Database DevOps for Oracle

Find invalid objects

Worked example

Cookies on red-gate.com