G-9210
🆓Always follow naming conventions for views.
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
Plural name of what is contained in the view.
Optionally suffixed by an indicator identifying the object as a view (mostly used, when a 1:1 view layer lies above the table layer)
Editioning views are named like the original underlying table to avoid changing the existing application code when introducing edition based redefinition (EBR).
Add a comment to the database dictionary for every view and every column.
Optionally prefixed by a project abbreviation.
Examples
active_ordersorders_v- a view to the orders tablecountries- an editioning view for tablecountries_eb
Why plural names?
We see a table and a view as a collection. A jar containing beans is labeled "beans". In Java we call such a collection also "beans" (List<Bean> beans) and name an entry "bean" (for (Bean bean : beans) {...}). An entry of a table is a row (singular) and a table can contain an unbounded number of rows (plural). This and the fact that the Oracle Database uses the same concept for their tables and views lead to the decision to use the plural to name a table or a view.
Example
Non-Compliant Example
create or replace view salaires_des_départements as
select d.department_id,
d.department_name,
sum(e.salary) as salaire_total
from employees e
join departments d on e.department_id = d.department_id
group by d.department_id, d.department_name;Issues
| Line | Column | Message |
|---|---|---|
| 1 | 24 | view salaires_des_départements does not match '^[a-z][a-z0-9$#_]*$'. |
Explanation
We do not want accented letters in view names.
Compliant Solution - ★★★★★
create or replace view salaires_des_departements as
select d.department_id,
d.department_name,
sum(e.salary) as salaire_total
from employees e
join departments d on e.department_id = d.department_id
group by d.department_id, d.department_name;Explanation
All accented letters are replaced with plain Latin letters.
Parameters
Use parameters to customize the rule to your needs.
| Parameter | Description | Default Value |
|---|---|---|
| ViewPattern | Case-insensitive regular expression pattern for SQL views. | ^[a-z][a-z0-9$#_]*$ |