rules repository

G-9214

🆓
Warning

Always follow naming conventions for PL/SQL packages.

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 content that is contained within the package.

Optionally prefixed by a project abbreviation.

Examples

  • employees_api - API for the employee table
  • logging_up - Utilities including logging support

Example

Non-Compliant Example

create or replace package département_api is
   -- ...
end;
/
Issues
LineColumnMessage
127Package département_api does not match '(?i)^[a-z][a-z0-9$#_]*$'.

We do not want accented letters in package names.

Compliant Solution - ★★★★★

create or replace package departement_api is
   -- ...
end;
/

All accented letters are replaced with plain Latin letters.

Tests

Test SQL query

select owner || '.' || object_name as identifier,
       'Package ' || object_name || ' does not match '''
       || lower(#PackagePattern#) || '''.' as message
  from dba_objects
 where owner in (#SchemaNames#)
   and object_type = 'PACKAGE'
   and not regexp_like(object_name, replace(lower(#PackagePattern#), '(?i)', null), 'i')
 order by identifier

Test results

IdentifierMessageMigration
DBL_OWNER.DÉPARTEMENT_APIPackage DÉPARTEMENT_API does not match '(?i)^[a-z][a-z0-9$#_]*$'.-

Parameters

Use parameters to customize the rule to your needs.

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

References