Monday, September 26, 2016

Oracle PL/SQL Coding Standards and Naming Conventions

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.

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
  1. Usage of the package
  2. 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.
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


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.
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



No comments:

Post a Comment

Best Blogger TipsGet Flower Effect