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_tmpcontracts_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 | Global 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
where temporary = 'Y'
and owner in (#SchemaNames#)
and not regexp_like(table_name, replace(lower(#GlobalTemporaryTablePattern#), '(?i)', null), 'i')
and table_name not like 'BIN$%'
order by identifierTest 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 |
|---|---|---|
| GlobalTemporaryTablePattern | Regular expression pattern for SQL global temporary tables. | (?i)^[a-z][a-z0-9$#_]*$ |
| SchemaNames | Comma-separated list of database schemas owning the database objects of an application. | dbl_owner |