rules repository

G-9211

🆓
Warning

Always follow naming conventions for sequences.

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

Name is built from the table name (or its abbreviation) the sequence serves as primary key generator and the suffix _seq or the purpose of the sequence followed by a _seq.

Optionally prefixed by a project abbreviation.

Examples

  • employees_seq
  • order_number_seq

Example

Non-Compliant Example

create sequence emps;
Issues
LineColumnMessage
117Sequence emps does not match '(?i)^[a-z][a-z0-9$#_]*_seq$'.

Compliant Solution - ★★★★★

create sequence emp_seq;

Tests

Test SQL query

select sequence_owner || '.' || sequence_name as identifier,
       'Sequence ' || sequence_name || ' does not match ''' ||
       lower(#SequencePattern#) || '''.' as message
  from dba_sequences
 where sequence_name not like 'AQ$%' -- generated by Oracle Advanced Queuing
   and sequence_owner in (#SchemaNames#)
   and not regexp_like(sequence_name, replace(lower(#SequencePattern#), '(?i)', null), 'i')
 order by identifier

Test results

IdentifierMessageMigration
DBL_OWNER.EMPSSequence EMPS does not match '(?i)^[a-z][a-z0-9$#_]*_seq$'.-

Parameters

Use parameters to customize the rule to your needs.

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

References