rules repository

G-9216

🆓
Warning

Always follow naming conventions for procedures.

Naming SQL

Rule Details

Severity Level
Minor
Quality Area
Maintainability
Fixing times
60 mins
Other
Database Support
OracleDB (v7.3.4 +)

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 a verb followed by a noun. The name of the procedure should answer the question “What is done?”

Procedures and functions are often named with underscores between words because some editors write all letters in uppercase in the object tree, so it is difficult to read them.

Optionally prefixed by a project abbreviation.

Examples

  • calculate_salary
  • set_hiredate
  • check_order_state

Example

Non-Compliant Example

create or replace package department_api is
   -- ...
   procedure fermer_département(in_id in number);
end;
/
Issues
LineColumnMessage
314Procedure fermer_département does not match '(?i)^[a-z][a-z0-9$#_]*$'.

We do not want accented letters in procedure names.

Compliant Solution - ★★★★★

create or replace package department_api is
   -- ...
   procedure fermer_departement(in_id in number);
end;
/

All accented letters are replaced with plain Latin letters.

Tests

Test SQL query

select owner || '.' || object_type || '.' || object_name
       || case when procedure_name is not null then '.' || procedure_name end as identifier,
       case object_type
          when 'PACKAGE' then 'Procedure ' || procedure_name || ' in package ' || object_name
          when 'TYPE' then 'Procedure ' || procedure_name || ' in type ' || object_name
          else initcap(object_type) || ' ' || object_name
       end || ' does not match ''' || lower(#ProcedurePattern#) || '''.' as message
  from dba_procedures p
 where not exists (
          select 1
            from dba_arguments a
           where p.object_id = a.object_id
             and p.subprogram_id = a.subprogram_id
             and a.position = 0 -- return value of a function
       )
   and object_type != 'TRIGGER'
   and object_name not like 'AQ$%'
   and owner in (#SchemaNames#)
   and not regexp_like(procedure_name, replace(lower(#ProcedurePattern#), '(?i)', null), 'i')
 order by identifier

Test results

IdentifierMessageMigration
DBL_OWNER.PACKAGE.DEPARTMENT_API.FERMER_DÉPARTEMENTProcedure FERMER_DÉPARTEMENT in package DEPARTMENT_API does not match '(?i)^[a-z][a-z0-9$#_]*$'.-

Parameters

Use parameters to customize the rule to your needs.

ParameterDescriptionDefault Value
ProcedurePatternRegular expression pattern for procedures in PL/SQL packages and types.(?i)^[a-z][a-z0-9$#_]*$
SchemaNamesComma-separated list of database schemas owning the database objects of an application.dbl_owner

References

← Previous
G-9215
Always follow naming conventions for functions.
Next →
G-9217
Always follow naming conventions for object types.