G-9218

🆓

Warning

Always follow naming conventions for object type attributes.

Rule Details

Severity Level: Minor
Quality Area: Maintainability
Fixing times: 60 mins
Other
Database Support: OracleDB (v8.0.5 +)

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

Line	Column	Message
2	4	Attribute 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

Identifier	Message	Migration
DBL_OWNER.DEPT_OT.DÉPARTEMENT_ID	Attribute 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.

Parameter	Description	Default Value
AttributePattern	Regular expression pattern for SQL object type attributes.	(?i)^[a-z][a-z0-9$#_]*$
SchemaNames	Comma-separated list of database schemas owning the database objects of an application.	dbl_owner

References

same as plsql:NamingObjectAttribute
similar to Trivadis Database Object Naming Conventions - Column
Columns and attributes should follow the same naming conventions.