rules repository

G-9218

🆓
Warning

Always follow naming conventions for object type attributes.

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

Singular name of what is stored in the attribute (unless the attribute data type is a collection, in this case you use plural names).

Example

Non-Compliant Example

create or replace type dept_ot as object (
   département_id number
   -- ...
);
/
Issues
LineColumnMessage
24Attribute département_id does not match '(?i)^[a-z][a-z0-9$#_]*$'.

We do not want accented letters in attribute names.

Compliant Solution - ★★★★★

create or replace type dept_ot as object (
   departement_id number
   -- ...
);
/

All accented letters are replaced with plain Latin letters.

Tests

Test SQL query

select a.owner || '.' || a.type_name || '.' || a.attr_name as identifier,
       'Attribute ' || a.attr_name || ' in ' || a.type_name || ' does not match '''
       || lower(#AttributePattern#) || '''.' as message
  from dba_types t
  join dba_type_attrs a
    on t.owner = a.owner
       and t.type_name = a.type_name
 where t.typecode = 'OBJECT'
   and t.owner in (#SchemaNames#)
   and not regexp_like(a.attr_name, replace(lower(#AttributePattern#), '(?i)', null), 'i')
 order by identifier

Test results

IdentifierMessageMigration
DBL_OWNER.DEPT_OT.DÉPARTEMENT_IDAttribute DÉPARTEMENT_ID in DEPT_OT does not match '(?i)^[a-z][a-z0-9$#_]*$'.-

Parameters

Use parameters to customize the rule to your needs.

ParameterDescriptionDefault Value
AttributePatternRegular expression pattern for SQL object type attributes.(?i)^[a-z][a-z0-9$#_]*$
SchemaNamesComma-separated list of database schemas owning the database objects of an application.dbl_owner

References