Oracle PL/SQL Coding Standards and Naming Conventions

Introduction

Provide standards and guidelines for the development of business and technical names that will help promote a common understanding of data within the Generic Conversion Development Initiative. By developing standardized and descriptive names, developers and designers in upcoming projects will be able to locate, reuse, merge, and analyze data to meet their project needs. Reusing data can reduce redundant data stores and data collection applications and can improve the quality and utility of newly developed applications.

Developing and implementing naming standards is also a prerequisite to the successful implementation of a metadata repository environment. To move forward on this initiative, we must begin to build its foundation now.

Note : This document covers the standard naming conventions and best practices associated with the creation of new data objects by the Security & Safety IT Development Services for various initiativesThe copy numbers referenced above should be written into the Copy Number space on the cover of each distributed copy.  If the document is not controlled, you can delete this table, the Note To Holders, and the Copy Number label from the cover page.

Object Naming Standards and Best Practices

Documents

Naming Convention for MD Documents

1. Conversions :

·         CV060_AACONV_Long Name.Doc

·         MD120_AACONV_Long Name.Doc

·         TE020_AACONV_Long Name.Doc

Tables

Naming Structure

A table name is a maximum of thirty characters.  Table names are derived from using the convention XXOA_<module name>_<Business Name Plural>_<Indicator>

All tables will include the mandatory Applications standard who columns: LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN (insert –1 for this column where value is unknown), CREATION_DATE and CREATED_BY.

Example

XXOA_ONT_PRODUCTS_T

ONT = module name

PRODUCTS = business name plural

T = indicator

• Table Creation script has description and comments

/*

##########################################################################

# File Name: XXOA_ONT_ORDER_LINES_STG.sql

# Description: Installation Script for Order lines staging table to import Sales orders from Legacy to Oracle

#

# History:

# ----------------------------------------------------------------------

#   Date       | Name                           | Remarks

# ----------------------------------------------------------------------

#

#

##########################################################################

*/

• A staging table that eventually populates a base table should have the name as base table with STG suffixed. e.g. XXOA_ONT_ORDER_HEADERS_IFACE_STG_T
• The table is created in XXR10 Custom Schema.
• Table creation file name ends with .sql
• Creation Script should have appropriate grants to APPS and table synonym is created in APPS
• Column Names should be meaningful and similar to the attributes from which they are derived
• Primary key Constraints follow the format: table_short_name_pk
• Foreign Key Constraints follow the format: <table_short_name>_FKNN, where NN can be any number from 1 to 99
• Unique Keys follow the format: <table_short_name>_UNQn, where n is between 1-9
• Table creation script file should have .sql extention.

Indexes

Naming Structure

Indexes should follow the form <Table Name>_Un/Nn, where lower case ‘n’ indicates the index number. ‘U’ stands for Unique index and ‘N’ stands for non unique index.

Sequences

Naming Structure

Sequences have the format XXOA_<module Name>_<Table Business name>_S

e.g. XXOA_HR_EMPLOYEE_STATUS_S

Views

Naming Structure

View Name should follow the convention, XXOA_<Module Name>_<Business Name>_V

e.g. XXOA_AR_CUSTOMERS_V

View Name is defined in Plural View Script should have the description and comments to indicate the purpose

Database Triggers

As a guideline, do not become trigger-happy.  Database triggers are extremely useful tools to ensure that a certain action occurs each and every time upon insert, update, or delete.  When the requirement states that the action must occur upon insert, update, or delete regardless of who does it, or how the data change happens, use a trigger.  Otherwise, look at some of the other methods (stored procedures, external libraries, etc.) in addition to triggers.

If a large amount of logic is required in a trigger, consider building a procedure to perform that logic and call it from the trigger.  The procedure is stored in complied form in the database, while the trigger is not.

Structure and style

Database triggers must follow the standards general standards for PL/SQL standards.

XXOA_<module name>_<Business Name Plural>_<Indicator>

Indicator is to be named as below:

      t = Trigger

      X = B or A (before or after event)

      X = R or S (row or statement)

      X = I or U or D  (insert, update or delete)

      X = opt U - update

      X = opt D - delete

Package Spec

Naming Structure

Package name should match following convention: XXOA_< product short name>_<name>_PKG, where name is meaningful name of business purpose of package

The header section is updated with the relevant details (e.g. File Name, Description, Parameters, History). The standard form of comments will be to precede them with the /* and end them with */. Each module: function, procedure or package will contain a comment block at the beginning.

Only public Procedures/Functions declared in the spec

·         Package spec file name should end with .pks for version control.

·         Exit statement should exit at the end of package creation script.

Package Comments

1.      Purpose of the package

2.      Usage of the package

3.      Remarks

4.      Revision history including the date, name of resource who performed the change, what has changed and the construct. The newest entries should be at the bottom. The first (oldest) line should state 'Creation'.

Package Body

Naming Structure

·         Package body file name should end with .pkb for version control.

·         Exit statement should exit at the end of package body creation script.

·         The Procedures/Functions name are listed in the Package body are action oriented

·         If any local variable is being used which is going to hold a value from a column then it should be declared as <Table Name>.<Column Name>%TYPE

·         If any local variable is being used which is going to hold a value from a column then it should be declared as <Table Name>.<Column Name>%TYPE

·         Implicit cursors are minimized

·         All procedures/functions have an exception handling block.

·         All reserved words should be in CAPS, all other in small letters eg., calls to standard packages like FND_ and APP_

·         All DML operations should have exception block

·         All hard coding should be in terms of global variables.

·         Check Performance of SQL queries e.g. no cartesian joins, NOT Exists instead of NOT IN, joins instead of exists etc.

·         Check if the following prefixes as part of the naming conventions in the program.

·         Local variable      l_

·         Global variable    g_

·         Local Constant    lc_

·         Global Constant  gc_

·         Cursor                  cur_

·         Record                 rec_

·         Types                   typ_

·         PL/SQL Table     tab_

·         For Procedures directly invoked from concurrent manager programs the  first two parameter should be errbuf OUT VARCHAR2 and retcode OUT NUMBER

·         The code in the Procedures/Functions is properly indented and no tabs are used. Suggest formatting using tools like Toad, Formatter Plus

·         No unqualified inserts

·         No functions on indexed columns in the where clause

Package Comments

1.      Purpose of the package

2. Usage of the package
3. Remarks

4.      Revision history including the date, name of resource who performed the change, what has changed and the construct. The newest entries should be at the bottom. The first (oldest) line should state 'Creation'.

Comment each packaged private program unit in the package body. Each program unit should be documented with 

1. Purpose
2. Usage
3. Possible remarks

Comment each packaged public program unit in the package specification.

Each program unit should be documented with

1. Purpose
2. Usage
3. Possible remarks

Each parameter must be listed and described

Any requirements/constraints on the values that may be passed in for the parameter must be indicated.

Any specific security or performance issues that apply to the program unit should also be given (for example, the procedure may take extremely long to perform or it contains some additional privileges)

Post-conditions (changes brought about by the procedure) may be outlined if they are not clear from the purpose of the program unit.

Procedures must be given a meaningful, preferably pronounceable, name.

If procedures are defined to support other procedures & functions (for example, conversion), a short name for the supported procedure or function can be part of that procedure’s name

/*

    ***********************************************************

    *                  - COPYRIGHT NOTICE -                  *

    ***********************************************************

    ***********************************************************

    * Application:                                   *

    * Program Name:                                  *

    * Version #:                                     *

    * Title:                                         *

    * Utility:                                       *

    * Remarks:                                       *

    * Created by:                                    *

    * Creation  Date:                                *

    *                                                *

    * Description:                                   *

    *                                                *

    *                                                *

    *                                                *

    * Change History:                                *

    *                                                *

    *=========================================================*

    * Date       | Name             | Remarks               *

    *=========================================================*

    *           CREATED                          *

    ***********************************************************

*/

Exception Handling

• In case of errors use the common error handling package XXOA_COMMON_ERR_PKG.INSERT_ERROR_LOG_PRC
• Details of the same are given in Generic Conversion Strategy Document.

SQL Script

·         Comply with XXOA  Naming Standard. E.g.  XXOA_ONT_CORP_EXCH_RATE.sql

·         Has Header with description, comments, who information for the script

In a select query

Oracle reserved words should be in CAPS i.e.

SELECT

FROM

WHERE

AND

GROUP BY

ORDER BY

HAVING

NULL

SUBSTR

DISTINCT

Table names and column names should be in lower case

Cursor name should start with cur_ and its result group with rec_

Select query can be formatted like

SELECT <column1>,

                <column2>,

            <column3>

   FROM  <table1>,

            <table2>

 WHERE <condition1>

      AND <condition2>

      AND <condition3>

Shell Script

·         Comply with XXOA Naming Standard. e.g. XXOA_ONT_CORP_EXCH_RATE.prog

·         Has Header with description, comments, who information for the script

AOL Objects

Concurrent Program

• Program Short Name begins with XXOA_<product_short_name>_<Business_name>.e.g. XXOA_ONT_ORDER_IMPORT etc.

Concurrent Executable

• Executable Short Name begins with XXOA_<product_short_name>_<Business_name>.e.g. XXOA_ONT_ORDER_IMPORT etc.
• Usually, program and executable short name should be the same
• Prefix XXOA_ before every custom program Name
• Custom  Program should be registered under respective custom application

Valueset

• Use of standard value sets where ever possible, instead of creating new ones
• Valueset Name begins with XXOA_<product_name> to indicate it is custom

Application Structure Codes

The following standards should be followed for custom application structures.  These structures include all components of the application setup that have a code to describe them.

Application Structures
Menu Name	XXOA_DDDDDD_MN	DDDDDD = <product_short_name>_<Business_name>	Total length should not exceed 30 characters
Value Set Name	XXOA_DDDDDD_VS	DDDDDD = <product_short_name>_<Business_name>	Total length should not exceed 30 characters
Zoom definition	XXOA_DDDDDD_ZM	DDDDDD = <product_short_name>_<Business_name>	Total length should not exceed 30 characters
Quickcode, Lookup_Type	XXOA_DDDDDD_LK	DDDDDD = <product_short_name>_<Business_name>	Total length should not exceed 30 characters
Profile Option	XXOA_DDDDDD_PF	DDDDDD = <product_short_name>_<Business_name>	Total length should not exceed 30 characters
Generic Error Message	XXOA_DDDDDD_MSG	DDDDDD = <product_short_name>_<Business_name>	Total length should not exceed 30 characters
Specific Error Message	XXOA_DDDDDD_MSG	DDDDDD = <product_short_name>_<Business_name>	Total length should not exceed 30 characters
Concurrent Executable Name	XXOADDDDD	DDDDD= Exact combination of characters used to name the concurrent executable	Total length should not exceed 8 characters
Concurrent Program Name	XXOADDDDD	DDDDD= Exact combination of characters used to name the concurrent program	Total length should not exceed 8 characters

All custom application structures should be registered with the custom applications.

Oracle Reserved Words

The reserved words that should not be used to name objects.

ABORT	DELAY	MIN	ROWS
ACCEPT	DELETE	MINUS	ROWTYPE
ACCESS	DELTA	MLSLABEL	RUN
ADD	DESC	MOD	SAVEPOINT
ALL	DIGITS	MODE	SCHEMA
ALTER	DISPOSE	MODIFY	SEPARATE
AND	DISTINCT	NATURAL	SESSION
ANY	DO	NATURALN	SET
ARRAY	DROP	NEW	SIZE
ARRAYLEN	DUAL	NEXTVAL	SMALLINT
AS	ELSE	NOAUDIT	SPACE
ASC	ELSIF	NOCOMPRESS	SQL
ASSERT	END	NOT	SQLCODE
ASSIGN	ENTRY	NOWAIT	SQLERRM START
AT	EXCEPTION	NULL	STATEMENT
AUDIT	EXCEPTION_INIT	NUMBER	STDDEV
AUTHORIZ	EXCLUSIVE	NUMBER_BASE	SUBTYPE
ATION	EXISTS	OF	SUCCESSFUL
AVG	EXIT	OFFLINE	SUM
BASE_TABLE	FALSE	ON	SYNONYM
BEGIN	FETCH	ONLINE	SYSDATE
BETWEEN	FILE	OPEN	TABAUTH
BINARY_INTEGER	FLOAT	OPTION	TABLE
BODY	FOR	OR	TABLES
BOOLEAN	FORM	ORDER	TASK
BY	FROM	OTHERS	TERMINATE
CASE	FUNCTION	OUT	TIME
CHAR	GENERIC	PACKAGE	THEN
CHAR_BASE	GOTO	PARTITION	TO
CHECK	GRANT	PCTFREE	TRIGGER
CLOSE	GROUP	PLS_INTEGER	TRUE
CLUSTER	HAVING	POSITIVE	TYPE
CLUSTERS	IDENTIFIED	POSITIVEN	UID
COLAUTH	IF	PRAGMA	UNION
COLUMN	IMMEDIATE	PRIOR	UNIQUE
COMMENT	IN	PRIVATE	UPDATE
COMMIT	INCREMENT	PRIVILEGES	USE
COMPRESS	INDEX	PROCEDURE	USER
CONNECT	INDEXES	PUBLIC	VALIDATE
CONSTANT	INDICATOR	RAISE	VALUES
CRASH	INITIAL	RANGE	VARCHAR
CREATE	INSERT	RAW REAL	VARCHAR2
CURRENT	INTEGER	RECORD	VARIANCE
CURRVAL	INTERFACE	REF	VIEW
CURSOR	INTERSECT	RELEASE	VIEWS
DATA_BASE	INTO	REMR	WHEN
DATABASE	IS	RENAME	WHENEVER
DATE	LEVEL	RESOURCE	WHERE
DBA	LIKE	RETURN	WHILE
DEBUGOFF	LIMITED	REVERSE	WITH
DEBUGON	LOCK	REVOKE	WORK
DECIMAL	LONG	ROLLBACK	WRITE
DECLARE	LOOP	ROW ROWID	XOR
DEFAULT	MAX	ROWLABEL
DEFINITION	MAXEXTENTS	ROWNUM

Valid Class Words

List of standard Class words to maintain consistency in their usage.

Class Word	Description
_amt	Amount field for currency information.  Is always numeric.  Precision best practices outlined in ‘Column Naming Guidelines should be applied.
_flag	One character field indicating a ‘Y’ for yes or an ‘N’ for no.
_desc	Text description - often many characters long and used mostly for reporting purposes. Usually related to a code field.
_date	Used for date or date/time fields.
_id	Numeric identifier - sequential number used only for record uniqueness. ID's can grow infinitely. ID's generally relate to another table that includes a description.
_name	Text information that provides naming information.
_nbr	Numeric information that does not identify a record.
_bin	Binary or other information stored in the database. Not to be viewed.  Exp. BLOB & CLOB.
_text	Text information in free format. Covers text that is not described in any of the options above.
_number	Text information used to provide further identification.
_cnt	Numeric information used to identify a value derived from a mathematical process.  May or may not be sequential in nature.

Valid Object Indicator Types

This is a list of valid indicators that identify a type of object. 

Indicator	Entity Description
_t	Oracle Table and/or Table definition procedure
_v	Oracle View and/or View definition procedure
_mv	Oracle Materialized View and/or Materialized View definition procedure
_Un/Nn	Oracle Index and/or Index definition procedure. ‘n’ is the numeric value added for uniqueness starting at 1.
_pk	Primary key constraint
_fkN	Foreign key constraint.   ‘N’ is the numeric value added for uniqueness starting at 1.
_Un/Nn	Unique key constraint.   ‘n’ is the numeric value added for uniqueness starting at 1.
_s	Oracle Sequence and/or Sequence definition procedure.
_prc	PL/SQL Procedure and/or Procedure definition file
_fnc	PL/SQL Function and/or Function definition file
_pkg	PL/SQL Package and/or Package definition file
_tXXXXX	Oracle Trigger and/or Trigger definition file       t = Trigger       X = B or A (before or after event)       X = R or S (row or statement)       X = I or U or D  (insert, update or delete)       X = opt U - update       X = opt D - delete
_rl	Oracle Role and/or Role definition file