rules repository

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
LineColumnMessage
127Synonym départements does not match '(?i)^[a-z][a-z0-9$#_]*$'.

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

IdentifierMessageMigration
HR.DÉPARTEMENTSSynonym DÉPARTEMENTS for DEPARTMENTS does not match '(?i)^[a-z][a-z0-9$#_]*$'.-

Parameters

Use parameters to customize the rule to your needs.

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

References