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
Line | Column | Message |
---|---|---|
7 | 24 |
★★★★★
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
Identifier | Message | Migration |
---|---|---|
DBL_OWNER.DEPTS | Collection type DEPTS does not match '(?i)^[a-z][a-z0-9$#_]*_ct$'. | - |
Parameters
Use parameters to customize the rule to your needs.
Parameter | Description | Default Value |
---|---|---|
CollectionTypePattern | Regular expression pattern for SQL collection types. | (?i)^[a-z][a-z0-9$#_]*_ct$ |
SchemaNames | Comma-separated list of database schemas owning the database objects of an application. | dbl_owner |