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