rules repository

G-9219

🆓
Warning

Always follow naming conventions for collection 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

A collection type should include the name of the collected objects in their name. Furthermore, they should have the suffix _ct to identify it as a collection.

Optionally prefixed by a project abbreviation.

Examples

  • employees_ct
  • orders_ct

Example

Non-Compliant Example

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

create or replace type depts as table of dept_ot;
/
Issues
LineColumnMessage
724Collection type depts does not match '(?i)^[a-z][a-z0-9$#_]*_ct$'.

Compliant Solution - ★★★★★

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

create or replace type dept_ct as table of dept_ot;
/

Tests

Test SQL query

select owner || '.' || type_name as identifier,
       'Collection type ' || type_name || ' does not match '''
       || lower(#CollectionTypePattern#) || '''.' as message
  from dba_types
 where typecode = 'COLLECTION'
   and owner in (#SchemaNames#)
   and not regexp_like(type_name, replace(lower(#CollectionTypePattern#), '(?i)', null), 'i')
 order by identifier

Test results

IdentifierMessageMigration
DBL_OWNER.DEPTSCollection type DEPTS does not match '(?i)^[a-z][a-z0-9$#_]*_ct$'.-

Parameters

Use parameters to customize the rule to your needs.

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

References