DOCUMENT HISTORY 2

PL/SQL CODING STANDARDS 3

2. Coding Format 3

3. Comments 5

4. General Guidelines 6

5. Naming conventions 7

5.1 Naming conventions for Stored Packages, Procedures and Functions: 7

5.2 Naming Conventions for Variables 7

6. Templates 8

6.1 Package Specification Template & Package Body Template 8

6.2 Procedure Template 10

6.3 Function Template 11

7. Common Packages 12

8. Sequences 12

9. Messages 12

10. Rules for improving performance 12

1
 PL/SQL Coding Standards

1. All programs should have a standard header that identifies

 the program name
 the author and the date it was written
 version of the code
 the code owner – group name that maintain the code and handle the 3 rd level support(if
available). For example, the BB team name or the bundle team name
 any parameters passed to the program
 a short description of the purpose of the program
 the change history

2. Coding Format

 Declaration sequence and variable/parameter initialization ( in package specs./ body):

 Constants should be defined before the variables.
 Cursor should be declared after the variables
 All Boolean variables should be initialized to TRUE / FALSE as per the requirement of the
function.
 Variables declared in PL/SQL blocks should use <table>.<column>% TYPE instead of
hard coded datatypes.

 It is recommended that all ORACLE reserved words should be in upper case and the rest in lower
case. For example, IF, END IF, DECLARE, BEGIN, END, EXCEPTION etc. No matter what case
that a programmer decides to use for the reserved words, procedure/function/trigger names,
variable names, table and column names, it should be consistent throughout the whole program.

 All program code is required to be formatted with consistent indentation and spacing that must
meet the following requirements:
 The indentation must reflect the block structure of the code so that the nested blocks
are easily apparent.
 There must be consistent indentation increment by which interior blocks are indented
relative to the blocks that contain them.
 Do not use the <TAB> character for the indentation, use 2 spaces instead

 Only one statement should be coded per line of source code. This includes statements such as
"IF", END IF and the associated conditionally executed statements. Very complex statements
should be split over several lines.

 There should always be a space following a keyword (e.g. if, for, while).

 Binary operators should be surrounded on both sides by spaces

 Long code lines should be split up to fit on one frame of the editor as far as possible.

 Use parentheses when doing complex comparisons. For example,

(100 < l_qty ) AND ( l_qty < 500 )

 For create statement, do not specify the column names in the create statement. So, use ‘CREATE
VIEW name AS SELECT’ instead of ‘CREATE VIEW name (col, col) AS SELECT’

 SELECT query should be properly indented. Use separate line for each reserved word. The
ORDER BY clause is always last in a SELECT statement unless it is a SELECT FOR UPDATE. For
example,
SELECT emp_no, emp_name
INTO h_emp_no, h_emp_name

2
 FROM EMP
WHERE (EMP_NO >= 100
AND EMP_NO <= 1000)
OR (EMP_NO <100
AND SALARY > 10000);

CREATE object name

parameters
AS SELECT /*+ hints */
col AS name,
col AS name,
col AS name
FROM
table alias,
table alias
WHERE cond
AND cond
AND cond
GROUP BY
col,
col
HAVING cond
AND cond
ORDER BY
col,
col;

 Exception Handlers should be properly indented. For example,

EXCEPTION
WHEN NO_DATA_FOUND THEN
message(No data found);
WHEN TOO_MANY_ROWS THEN
message(Too many rows);
WHEN OTHERS THEN
message(sqlerrm);
END;

3
3. Comments

 In-line comments must be added as separate lines above the code, wherever complex processing is
involved to improve understandability.

 There should not be any comments inside a SQL statement. The comments should be right above the
code instead.
NOTE: Commenting inside a SQL may be necessary if it is complex and a couple of pages long.

 No other language than English is used for comments.

 No other language than English is used for variables and other naming conventions.

 Comment on one line

-- This is a one-line comment that describes the following code

 Multi-line Comment
/* These lines of comment will describe the following block
of code in much more detail. In particular comments will
out any problem areas.
*/

4
4. General Guidelines

 PL/SQL should be used instead of Unix shell to control program flow. This will avoid or at least
minimize costly and bad-performing approaches with shell script calling sqlplus in the loop. A rule of
thumb is to process as much data as possible in as few calls to Oracle as possible. Efficient coding
should accomplish all processing or a major division of work in a single invocation of Oracle.

 Transaction Handling: Commit should always be in a new line and should be done at the end of the
transaction. However, if a transaction involves more than one update (including insertion / deletion)
to the database, commit should be given when all such updates are completed successfully. Explicitly
commit all transactions upon completion instead of relying on implicit commits.

 Exception Handling: All procedures, functions and triggers should have an exception handler
wherever possible. Explicitly rollback all transactions when necessary. Warning and Error Messages
should include complete information for support personnel to find where the problem occurred, what
is the data or process that failed the possible causes for this error of warning, etc.

 No Oracle package shall implement the serially_reusable pragma (There is an Oracle8i bug).
In general, any advanced feature manipulating shared memory areas should not be applied
without consultation from DBA/BB experts.

 Bind Variables vs. Static Variables: When programming for on-line transaction, using bind
variables will improve the performance. However, this cannot be always true when programming for
batch processing. This is because Oracle needs static variables, not bind variables, to create Explain
Plan for performance tuning.

 A Boolean variable is itself either true or false. So comparisons with Boolean values TRUE or FALSE
are redundant. For example, if ‘l_done’ is Boolean, then the following statement can be simplified as
“IF l_done THEN…”

 The scope of identifiers must be made as local as possible. With the exception of global identifiers,
which cannot be passed, Procedures and Functions should operate only on objects that are passed to
them. All variables should be defined only at the entry to a function/procedure.

 Package:
 When procedures & functions are logically related and likely to be loaded into the memory -- SGA
(System Global Area) at the same time, they should be written into a package.
 The procedures/functions should have minimal “access” calls to the database.
 Whenever possible, one should use packages and procedures that rely on IN and OUT parameters
for passing data between the procedures. Function return values can also be used.
 All OUT parameters should be passed by Reference instead of by Value. Use NO COPY as the hint
for all OUT parameters within the declaration of procedures & functions. All IN parameters are by
default passed by reference into procedure block.
 A procedure should be used to perform a significant task, not just for a minor action. Following
are the examples of inappropriate usage of subprograms within a package and inappropriate
naming (The naming standards are in the following sections):

Example 1

PROCEDURE P_WRITE_TO_FILE (FILE utl_file.file_type, line VARCHAR2)

IS
BEGIN
utl_file.put_line(FILE, line);
END P_WRITE_TO_FILE;

Example 2

5
 PROCEDURE P_INITIALIZE_VARIABLES IS
BEGIN
current_delivery := NULL;
previous_delivery := NULL;
prev_origssid := NULL;
status_index := 0;
delivery_index := 0;
END P_INITIALIZE_VARIABLES;

5. Naming conventions

5.1 Naming conventions for Stored Packages, Procedures and Functions:

PL/SQL name should be same as specified in the Batch and Procedure Specification. For any New
Pakage/Procedure/Function not in the specification use the following Naming convention :

Packages: (pkg_)<meaningful name>

Procedures: (P_)<meaningful name>
Functions: (F_)<meaningful name>

5.2 Naming Conventions for Variables

(data type)<appropriate name>

data type – optional for scalar data type, such as varchar2, date, number, integer, and Boolean
type, etc.
 user-defined exception name: e
 variable constrained with the CONSTANT clause: c
 Boolean b
 utl_file.file_type (file handler): fh
 cursor: cur
 cursor_variable: cv
 pl/sql record: rec
 pl/sql table: tab
 varray: var
 nested table: nst

appropriate name
 for a database column: column_name
 otherwise: meaningful_name

Examples:
 Input Parameter: p<Meaning full Name> like pMPANCore, pRetCode
 Constant: c<Meaning full Name> like cName
 Variables: v<Meaning full Name> like vMPANCore, vAmount etc.
 Cursor: cur<Meaning full Name> like curContract

6
6. Templates

6.1 Package Specification Template & Package Body Template

/*********************************************************
* File Name:
*
* Package Name:
*
* Date:
*
* Author:
*
* Code Owner:
*
* Overview:
*
* Change History:
* Date Programmer Version Reason
* ----- -------------- -------- --------
*
**********************************************************/

CREATE OR REPLACE PACKAGE (pkg_)<meaningful_name>

IS
Public variable declarations
Public type declarations
Public Subprogram specifications
END (pkg_)<meaningful_name>;
/
show errors;

CREATE OR REPLACE PACKAGE BODY (pkg_)<meaningful_name>

IS
Private variable (but global within the body) declarations
Private type declarations

Subprogram bodies

/*********************************************************
Name:
Purpose:
Parameters:
Calls:
**********************************************************/
PROCEDURE P_<meaningful_name>
(parameter list)
IS
Local variable and types declarations
BEGIN
Procedure body part
EXCEPTION

7
 Exception handler part
END P_<meaningful_name>;

/*********************************************************
Name:
Purpose:
Parameters:
Calls:
**********************************************************/
FUNCTION F_<meaningful_name>
(parameter list)
return data_type
IS
Local variable and types declarations
BEGIN
Function body part
EXCEPTION
Exception handler part
END F_<meaningful_name>;

END (pkg_)<meaningful_name>;
/
show errors;

8
6.2 Procedure Template
/*********************************************************
* File Name:
*
* Procedure Name:
*
* Date:
*
* Author:
*
* Code Owner:
*
* Parameters:
*
* Purpose:
*
* Change History:
* Date Programmer Reason
* --------- ---------------- -------------------------
*
**********************************************************/
CREATE OR REPLACE PROCEDURE (P_)<meaningful_name>
(parameter list)
IS
Local variable and types declarations

BEGIN
Procedure body part

EXCEPTION
Exception handler part

END (P_)<meaningful_name>;
/
show errors;

9
6.3 Function Template
/*********************************************************
* File Name:
*
* Function Name:
*
* Date:
*
* Author:
*
* Code Owner:
*
* Parameters:
*
* Purpose:
*
* Change History:
* Date Programmer Reason
* --------- ---------------- -------------------------
*
**********************************************************/
CREATE OR REPLACE FUNCTION (F_)<meaningful_name>
(parameter list)
return data_type
IS
Local variable and types declarations

BEGIN
Function body part

EXCEPTION
Exception handler part

END (F_)<meaningful_name>;
/
show errors;

10
7. Common Packages

Common Insert
Package Name: pkg_CommonINsertAD,
pkg_CommonINsertEO,
pkg_CommonINsertPZ,

Description: The packages contain Insert Procedures for all the DADS Tables.

How to use: Choose the package as per the table name. Procedures are alphabetically sorted in
the above packages. All the common procedures have parameters

8. Sequences

If for a specific column, the current standard is ‘’S_Table_Name’.

If for multiple columns, the current standard is ‘S_Table_Name<nn>’, where nn = runnning serial
number.

9. Messages

Warning and Error Messages should include complete information for support personnel to find where the
problem occurred, what is the data or process that failed the possible causes for this error of warning, etc.

10.Rules for improving performance

 Use NOT EXISTS in place of NOT IN
 If a table is indexed on column Y, avoid the following expressions in the WHERE-Clause, in order to
safeguard the use of the index:
IS NULL, IS NOT NULL, NOT, NOT IN, Y != expr, Y like '%pattern', NOT EXISTS
 Use global variables for temporary storage of values wherever meaningful.
 In the statement position the selected tables in the "correct" sequence. For this tables with the
highest selectivity are set at the end of the table list, the table with the second highest on the last but
one, etc.
 A SELECT-Statement with a WHERE-Clause, which uses AND, is shifted from right to left (or from
bottom to top). Set the costliest clause at the position lying on the farthest right.
 A SELECT-Statement with a WHERE-Clause, which uses OR, is shifted from left to right (or top to
bottom). Set the costliest clause at the position lying on the farthest left.
 Use Joins in place of several SELECT-Statements.
 Use WHERE in place of HAVING.
 Minimize the number of Lookup tables wherever possible.
 Use Joins in place of EXISTS, if the part of selected lines of the driving/ drifting table is high.
 Prefer the use of EXISTS, if the number of lines of the driving/ drifting table in the Answer-Set is low.
 Prefer UNION/IN before OR in the WHERE-Clause only when both the relevant columns are indexed.
 Prefer UNION ALL to UNION, if either a sorting or a selection of the duplicate lines is necessary.
 Prefer UNION ALL to OR, if there are several conditions in the WHERE-Clause, which address all the
indexed columns.
 Always use explicit cursors.
 If possible, avoid the transfer to many parameters, particularly PL/SQL tables.
 Avoid the use of ‘ORDER BY’ by skilled usage of WHERE.
 Use Inline functions wherever possible.
 Reduce very large table joins by using functions.
 Sometimes the access to a table can be avoided if the index values of the table suffice for replying to
the query. Example: count(1) is slower than count(*), which is slower than count(indexed column)

11
 If character columns are compared with numeric columns and the character column is indexed and
more selective, then use function TO_CHAR, in order to convert the numerical values explicitly to
character values.

END OF DOCUMENT

12