G-1310
🆓Error
Never keep database objects in an invalid state.
Reason
Relying on Oracle’s implicit recompilation can mask issues until runtime, leading to unexpected behavior or even silent failures. Fixing invalid objects promptly gives you full control over the compilation process, making it easier to spot errors early and maintain a stable codebase. Addressing these issues as soon as they appear simplifies development and reduces last-minute surprises.
Example
Non-Compliant Example
create or replace procedure p is begin null end p; /
Explanation
The statement on line 3 is missing a semicolon.
Compliant Solution - ★★★★★
create or replace procedure p is begin null; end p; /
Explanation
The procedure compiles without errors.
Tests
Test SQL query
select o.owner || '.' || o.object_name as identifier,
initcap(o.object_type) || ' ' || o.owner || '.' || o.object_name || ' is invalid.' as message
from dba_invalid_objects o
left join dba_synonyms s
on s.owner = o.owner
and s.synonym_name = o.object_name
where o.owner in (#SchemaNames#)
or (o.owner = 'PUBLIC' and o.object_type = 'SYNONYM' and s.table_owner in (#SchemaNames#))
order by o.owner, o.object_type, o.object_nameTest results
| Identifier | Message | Migration |
|---|---|---|
| DBL_OWNER.P | Procedure DBL_OWNER.P is invalid. | - |
Parameters
Use parameters to customize the rule to your needs.
| Parameter | Description | Default Value |
|---|---|---|
| SchemaNames | Comma separated list of database schemas owning the database objects of an application. | dbl_owner |