G-9212
🆓Warning
Always follow naming conventions for synonyms.
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
Synonyms should be used to address an object in a foreign schema rather than to rename an object. Therefore, synonyms should share the name with the referenced object.
Example
Non-Compliant Example
create or replace synonym départements for hr.departments;
Issues
Line | Column | Message |
---|---|---|
1 | 27 |
We do not want accented letters in synonym names.
★★★★★
Compliant Solution -
create or replace synonym departements for hr.departments;
All accented letters are replaced with plain Latin letters.
Tests
Test SQL query
select owner || '.' || synonym_name as identifier, 'Synonym ' || synonym_name || ' for ' || table_name || ' does not match ''' || lower(#SynonymPattern#) || '''.' as message from dba_synonyms where (owner in ('DBL_OWNER') or table_owner in (#SchemaNames#)) and not regexp_like(synonym_name, replace(lower(#SynonymPattern#), '(?i)', null), 'i') order by identifier
Test results
Identifier | Message | Migration |
---|---|---|
HR.DÉPARTEMENTS | Synonym DÉPARTEMENTS for DEPARTMENTS does not match '(?i)^[a-z][a-z0-9$#_]*$'. | - |
Parameters
Use parameters to customize the rule to your needs.
Parameter | Description | Default Value |
---|---|---|
SynonymPattern | Regular expression pattern for SQL synonyms. | (?i)^[a-z][a-z0-9$#_]*$ |
SchemaNames | Comma-separated list of database schemas owning the database objects of an application. | dbl_owner |