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
Line | Column | Message |
---|---|---|
1 | 17 |
★★★★★
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
Identifier | Message | Migration |
---|---|---|
DBL_OWNER.EMPS | Sequence EMPS does not match '(?i)^[a-z][a-z0-9$#_]*_seq$'. | - |
Parameters
Use parameters to customize the rule to your needs.
Parameter | Description | Default Value |
---|---|---|
SequencePattern | Regular expression pattern for SQL sequences. | (?i)^[a-z][a-z0-9$#_]*_seq$ |
SchemaNames | Comma-separated list of database schemas owning the database objects of an application. | dbl_owner |