G-9215

🆓

Warning

Always follow naming conventions for functions.

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 in general. Nevertheless, it is not sensible to call a function get_... as a function always gets something.

The name of the function should answer the question “What is the outcome of the function?”

Optionally prefixed by a project abbreviation.

Example: employee_by_id

If more than one function provides the same outcome, you have to be more specific with the name.

Example

Non-Compliant Example

create or replace package departement_api is
   -- ...
   function département_id_par_nom(in_nom in varchar2) return number;
end;
/

Issues

Line	Column	Message
3	13	Function département_id_par_nom does not match '(?i)^[a-z][a-z0-9$#_]*$'.

We do not want accented letters in function names.

Compliant Solution - ★★★★★

create or replace package departement_api is
   -- ...
   function departement_id_par_nom(in_nom in varchar2) return 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 'Function ' || procedure_name || ' in package ' || object_name
          when 'TYPE' then 'Function ' || procedure_name || ' in type ' || object_name
          else object_type || ' ' || object_name
       end || ' does not match ''' || lower(#FunctionPattern#) || '''.' as message
  from dba_procedures p
 where 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 owner in (#SchemaNames#)
   and not regexp_like(procedure_name, replace(lower(#FunctionPattern#), '(?i)', null), 'i')
 order by identifier

Test results

Identifier	Message	Migration
DBL_OWNER.PACKAGE.DEPARTEMENT_API.DÉPARTEMENT_ID_PAR_NOM	Function DÉPARTEMENT_ID_PAR_NOM in package DEPARTEMENT_API does not match '(?i)^[a-z][a-z0-9$#_]*$'.	-

Parameters

Use parameters to customize the rule to your needs.

Parameter	Description	Default Value
FunctionPattern	Regular expression pattern for functions in PL/SQL packages and types.	(?i)^[a-z][a-z0-9$#_]*$
SchemaNames	Comma-separated list of database schemas owning the database objects of an application.	dbl_owner

References

same as Trivadis Database Object Naming Conventions - Function
similar to plsql:PlSql.FunctionAndProcedureNaming
The scope of plsql:PlSql.FunctionAndProcedureNaming is functions and procedures.