Oracle Object Naming Standards

This section outlines the standards we follow for naming objects in the Oracle environment. Please note the following general items:

  • In general Oracle does not distinguish between upper and lower case when referencing objects, unless the name is contained in quotes.
  • We do not support quoted names
  • Object names can be a max of 30 characters

[edit]

Columns

  • Columns should be given meaningful names.
  • Try to spell out the column name instead of using abbreviations.
  • Primary Keys (PK) columns should be the table name (less the “T_”) suffixed with either “_ID” (if it’s a number) or “_TYPE” (if it’s a character string).
  • Underscores should be used in column names to further the readability and understanding.
  • Columns of type “DATE” where the time is of no importance should be suffixed with a “_DATE” and if the time is of importance the column name should be suffixed with “_DATETM”.
  • If the column is a description column it should be given a meaningful name and suffixed with “_DESCRIPTION”.
  • If the table is a lookup table with a description column, the column name should be just “DESCRIPTION”.
  • Standard Oracle data types such as number, date, timestamp, varchar2, char, blob, and clob should be used.

[edit]

Constraints

The following standards apply to the specific types of constraints, but in geheral the following should apply:

  • Constraints must be named across the board.
  • The only exception to this rule is “NOT NULL” constraints.

[edit]

Check Constraints

  • Check constraint names must be prefixed with “CCK_”
  • Check constraint names must besuffixed with a “_#”
  • Check constraint names must be the same name as the table name less the “T_” (e.g. CCK_WEBMASTER_1).
  • If the name becomes longer than 30 characters in this process, characters from the end of the table name should be removed to reach the 30 character limit.

[edit]

Foreign Key Constraints

  • Foreign key names must be prefixed with “CFK_”
  • Foreign key names must be suffixed with a “_#”
  • Foreign key names must be the same name as the table name less the “T_” (e.g. CFK_WEBMASTER_1).
  • If the name becomes longer than 30 characters in this process, characters from the end of the table name should be removed to reach the 30 character limit.
  • Foreign key columns must be indexed. Note, failure to do so will cause undesirable locking issues. Currently, row level locks on the parent table will cause TABLE level locks for the child table if the columns on the child are not indexed. The following query can be helpful in finding missing child table indexes:

|

column columns format a20 word_wrapped

column table_name format a30 word_wrapped

select decode( b.table_name, NULL, ‘****’, ‘ok’ ) Status,

a.table_name, a.columns, b.columns

from

( select substr(a.table_name,1,30) table_name,

substr(a.constraint_name,1,30) constraint_name,

max(decode(position, 1,     substr(column_name,1,30),NULL)) ||

max(decode(position, 2,’, ‘||substr(column_name,1,30),NULL)) ||

max(decode(position, 3,’, ‘||substr(column_name,1,30),NULL)) ||

max(decode(position, 4,’, ‘||substr(column_name,1,30),NULL)) ||

max(decode(position, 5,’, ‘||substr(column_name,1,30),NULL)) ||

max(decode(position, 6,’, ‘||substr(column_name,1,30),NULL)) ||

max(decode(position, 7,’, ‘||substr(column_name,1,30),NULL)) ||

max(decode(position, 8,’, ‘||substr(column_name,1,30),NULL)) ||

max(decode(position, 9,’, ‘||substr(column_name,1,30),NULL)) ||

max(decode(position,10,’, ‘||substr(column_name,1,30),NULL)) ||

max(decode(position,11,’, ‘||substr(column_name,1,30),NULL)) ||

max(decode(position,12,’, ‘||substr(column_name,1,30),NULL)) ||

max(decode(position,13,’, ‘||substr(column_name,1,30),NULL)) ||

max(decode(position,14,’, ‘||substr(column_name,1,30),NULL)) ||

max(decode(position,15,’, ‘||substr(column_name,1,30),NULL)) ||

max(decode(position,16,’, ‘||substr(column_name,1,30),NULL)) columns

from user_cons_columns a, user_constraints b

where a.constraint_name = b.constraint_name

and b.constraint_type = ‘R’

group by substr(a.table_name,1,30), substr(a.constraint_name,1,30) ) a,

( select substr(table_name,1,30) table_name, substr(index_name,1,30) index_name,

max(decode(column_position, 1,     substr(column_name,1,30),NULL)) ||

max(decode(column_position, 2,’, ‘||substr(column_name,1,30),NULL)) ||

max(decode(column_position, 3,’, ‘||substr(column_name,1,30),NULL)) ||

max(decode(column_position, 4,’, ‘||substr(column_name,1,30),NULL)) ||

max(decode(column_position, 5,’, ‘||substr(column_name,1,30),NULL)) ||

max(decode(column_position, 6,’, ‘||substr(column_name,1,30),NULL)) ||

max(decode(column_position, 7,’, ‘||substr(column_name,1,30),NULL)) ||

max(decode(column_position, 8,’, ‘||substr(column_name,1,30),NULL)) ||

max(decode(column_position, 9,’, ‘||substr(column_name,1,30),NULL)) ||

max(decode(column_position,10,’, ‘||substr(column_name,1,30),NULL)) ||

max(decode(column_position,11,’, ‘||substr(column_name,1,30),NULL)) ||

max(decode(column_position,12,’, ‘||substr(column_name,1,30),NULL)) ||

max(decode(column_position,13,’, ‘||substr(column_name,1,30),NULL)) ||

max(decode(column_position,14,’, ‘||substr(column_name,1,30),NULL)) ||

max(decode(column_position,15,’, ‘||substr(column_name,1,30),NULL)) ||

max(decode(column_position,16,’, ‘||substr(column_name,1,30),NULL)) columns

from user_ind_columns

group by substr(table_name,1,30), substr(index_name,1,30) ) b

where a.table_name = b.table_name (+)

and b.columns (+) like a.columns || ‘%’

/

[edit]

Primary Key Constraints

  • Primary key names must be prefixed with “CPK_” and must be the same name as the table name less the “T_” (e.g. CPK_WEBMASTER).
  • If the name becomes longer than 30 characters in this process, characters from the end of the table name should be removed to reach the 30 character limit.
  • All tables must have a primary key (the only exception is datawarehouse tables).

[edit]

Unique Key Constraints

  • Unique key names must be prefixed with “CU_”
  • Unique key names must be suffixed with a “_#”
  • Unique key names must be the same name as the table name less the “T_” (e.g. CU_WEBMASTER_1).
  • If the name becomes longer than 30 characters in this process, characters from the end of the table name should be removed to reach the 30 character limit.

[edit]

Indexes

  • Normal index names must be prefixed with a “I_”
  • Bitmap index names must be prefixed with “IB_”
  • Function-based index names must be prefixed with “IF_”.
  • All index names must be suffixed with a “_#”
  • All index names must be the same name as the table name less the “T_” (e.g. I_WEBMASTER_1).
  • The sequence number used should be continued from normal indexes to bitmap and function based indexes (e.g. I_WEBMASTER_1 and IB_WEBMASTER_2).
  • If the name becomes longer than 30 characters in this process, characters from the end of the table name should be removed to reach the 30 character limit.

[edit]

Materialized Views

  • Materialized view names must be prefixed with a “MV_”.
  • If the Materialized view name consists of multiple logical words they must be separated by an underscore “_”.
  • The Materialized view name should be kept singular (e.g. “MV_WEBMASTER” instead of “MV_WEBMASTERS”).

[edit]

Objects

  • Object names must be prefixed with a “O_”.
  • If the object name consists of multiple logical words they must be separated by an underscore “_”.
  • The object name should be kept singular (e.g. “O_WEBMASTER” instead of “O_WEBMASTERS”).

[edit]

Sequences

  • Sequence names must be prefixed with a “S_”
  • Sequence names must be suffixed with a “_#”
  • Sequence names must be the same name as the table name the sequence will be used for less the “T_” (e.g. S_WEBMASTER_1).
  • If the name becomes longer than 30 characters in this process, characters from the end of the table name should be removed to reach the 30 character limit.

[edit]

Refresh Groups

  • Materialized refresh group should be prefixed with SSG_
  • Group name should contain the name of the source schema: “SSG_ACE”
  • If source is from multiple schemas, it may contain some other meaningful description.
  • If the name consists of multiple logical words they must be separated by an underscore “_”.

[edit]

Stored Procedure, Functions and Packages

  • Must be prefixed with :
    • Stored Procedures : “SP_”
    • Function: “SF_”
    • Packages: “SA_”
  • If the name consists of multiple logical words they must be separated by an underscore “_”.

[edit]

Tables

  • If the tablename consists of multiple logical words they must be separated by an underscore “_”.
  • The tablename should be kept singular (e.g. “T_WEBMASTER” instead of “T_WEBMASTERS”).
  • Table level comments should be provided. They should explain what data is contained in the table and how it is used.

[edit]

Triggers

  • Trigger names must be prefixed with a “TR_”
  • Trigger names must be suffixed with a “_[BA][IUD][RS][#]”
  • Trigger names must be the same name as the table name less the “T_”.
  • Explanation of the suffix :
    • [BA] B=Before, A=After triggers
    • [IUD] I=Insert, U=Update, D=Delete triggers
    • [RS] R=Row level, S=Statement level triggers
    • [#] If more than one trigger of a certain type exists a sequence number can be appended
  • If the name becomes longer than 30 characters in this process, characters from the end of the table name should be removed to reach the 30 character limit.

Example: TR_WEBMASTER_BIR; Before, Insert, Row level and TR_WEBMASTER_BIR2; Before, Insert, Row level, trigger #2).

[edit]

Types

Types must be prefixed with a “TY_”. The same rules as for table names applies to type names.

[edit]

Views

  • View names must be prefixed with a “V_”.
  • If the view name consists of multiple logical words they must be separated by an underscore “_”.
  • The view name should be kept singular (e.g. “V_WEBMASTER” instead of “V_WEBMASTERS”).