G-9209
🆓Warning
Always follow naming conventions for global temporary tables.
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
Naming as described for tables.
Optionally suffixed by _tmp
Optionally prefixed by a project abbreviation.
Examples
employees_tmp
contracts_tmp
Example
Non-Compliant Example
create global temporary table département_tmp ( id number(8,0) not null -- ... ) on commit preserve rows;
Issues
Line | Column | Message |
---|---|---|
1 | 31 |
We do not want accented letters in table names.
★★★★★
Compliant Solution -
create global temporary table departement_tmp ( id number(8,0) not null -- ... ) on commit preserve rows;
All accented letters are replaced with plain Latin letters.
Tests
Test SQL query
select owner || '.' || table_name as identifier, 'Global temporary table ' || table_name || ' does not match ''' || lower(#GlobalTemporaryTablePattern#) || '''.' as message from dba_tables t where temporary = 'Y' and owner in (#SchemaNames#) and not regexp_like(table_name, replace(lower(#GlobalTemporaryTablePattern#), '(?i)', null), 'i') and not exists ( select 1 from dba_recyclebin rb where rb.owner = t.owner and rb.object_name = t.table_name ) order by identifier
Test results
Identifier | Message | Migration |
---|---|---|
DBL_OWNER.DÉPARTEMENT_TMP | Global temporary table DÉPARTEMENT_TMP does not match '(?i)^[a-z][a-z0-9$#_]*$'. | - |
Parameters
Use parameters to customize the rule to your needs.
Parameter | Description | Default Value |
---|---|---|
SchemaNames | Comma-separated list of database schemas owning the database objects of an application. | dbl_owner |
GlobalTemporaryTablePattern | Regular expression pattern for SQL global temporary tables. | (?i)^[a-z][a-z0-9$#_]*$ |