G-9217
🆓Warning
Always follow naming conventions for object types.
Reason
SQL identifiers share the same namespace as PL/SQL identifiers. Follow naming conventions to prevent naming conflicts, improve readability, and clearly indicate the scope without forcing the use of qualified names. A common practice is to use a prefix and/or suffix to distinguish the identifier types.
Recommendations
The name of an object type is built by its content (singular) followed by a _ot suffix.
Optionally prefixed by a project abbreviation.
Example: employee_ot
Example
Non-Compliant Example
create or replace type dept as object ( department_id number -- ... ); /
Issues
| Line | Column | Message |
|---|---|---|
| 1 | 24 | Object type dept does not match '(?i)^[a-z][a-z0-9$#_]*_ot$'. |
Compliant Solution - ★★★★★
create or replace type dept_ot as object ( department_id number -- ... ); /
Tests
Test SQL query
select owner || '.' || type_name as identifier,
'Object type ' || type_name || ' does not match '''
|| lower(#ObjectTypePattern#) || '''.' as message
from dba_types
where typecode = 'OBJECT'
and owner in (#SchemaNames#)
and not regexp_like(type_name, replace(lower(#ObjectTypePattern#), '(?i)', null), 'i')
order by identifierTest results
| Identifier | Message | Migration |
|---|---|---|
| DBL_OWNER.DEPT | Object type DEPT does not match '(?i)^[a-z][a-z0-9$#_]*_ot$'. | - |
Parameters
Use parameters to customize the rule to your needs.
| Parameter | Description | Default Value |
|---|---|---|
| ObjectTypePattern | Regular expression pattern for SQL object types. | (?i)^[a-z][a-z0-9$#_]*_ot$ |
| SchemaNames | Comma-separated list of database schemas owning the database objects of an application. | dbl_owner |