• PL/SQL variables, except for fields in record structures, should not have names identical to column names.
• Temporary variables should be named after the database column or column alias that will populate them, prefixed with a "v_". Temporary variables, which do not have a direct relation with a column should be given a meaningful name and also prefixed with a "v_".
• Parameter variable names should be prefixed with a “p_”.
• Cursor Bind variable names should be prefixed with "b_".
• Global variables should be prefixed with “g_”.
• Constants should be prefixed with “cn_”
Exceptions
Exception names should be prefixed with an “e_”. They should indicate what caused the exception, not the actions that should be taken to handle the exception.
Avoid In Favor Of
EXCEPTION EXCEPTION
when e_stop_processing then when e_no_customer_found then
.... ....
when e_error_occurred then when e_stock_levels_low then
.... ....
END; END;
Table Names
Prefix the table name with the name of the custom application where the table will belong (e.g. XX).
The descriptive part of the table name should include the plural form of the noun that describes the contents of the table.
For example, a table that stores approval hierarchy relationships can be named:
XX_PO_APPROVAL_HIERARCHIES
Tables that are designed to store data that is logically partitioned by organization (i.e., multi-org tables) should be named using the suffix “_ALL”. For example: XX_PO_APPROVAL_HIERARCHIES_ALL
Staging Tables
Staging tables may need to be created. Developer should follow the guideline of using correct table name standard to create interface table.
Naming convention for the Staging tables for Open Interface tables should follow this convention:
E.g. for INV_ITEM, staging table should look like:
XX_INV_ITEM_<IF/CV/CDS/DS/>_STG
Or
XX_INV_ITEM_<IF/CV/CDS/DS/DSCDS>_STG
The package name to manipulate the staged table data is XX_INV_ITEM_<IF/CV/CDS/DS>
PLSQL packaged procedures to clean up data should look like:
XX_INV_ITEM_<IF/CV/CDS/DS/DSCDS>.PURGE (…);
The API load data from staging tables into Oracle Open interface tables should be named as:
XX_INV_ITEM_<IF/CV/CDS/DS/DSCDS>.LOAD (…);
Where:
IF – Interface
CV – Conversion
CDS – Cross Domain Solutions
DS – Data Synchronization
Sequences
Every primary key in a custom table should have a sequence.
Sequence Names
Name your sequences “<table name>_S”. For example, a sequence used to derive key values for a table named “XX_FND_STAGE_LOAD_LOGS” would be named “XX_FND_STAGE_LOAD_LOGS_S”.
