rules repository

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
LineColumnMessage
131Global temporary table département_tmp does not match '(?i)^[a-z][a-z0-9$#_]*$'.

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

IdentifierMessageMigration
DBL_OWNER.DÉPARTEMENT_TMPGlobal temporary table DÉPARTEMENT_TMP does not match '(?i)^[a-z][a-z0-9$#_]*$'.-

Parameters

Use parameters to customize the rule to your needs.

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

References