General PL SQL code Guidelines

1              Guidelines

1.1            GENERAL

·         SQL PROCEDURES should contain a header for easy maintainability of the code.

·         All variables (except some variables used for looping purpose) shall be defined in the declaration section.

·         All the constants shall be declared in the declarative section and should be capital letters.

·         Possible exceptions should be handled in the Exception Section.

·         Anonymous blocks shall not be used.

·         Change History in the Header section is updated whenever the code is modified.

·         For a new procedure / function, add comments in the header section in description portion to explain the overall purpose and approach.

·         Comments should describe the nature and purpose of intended change. Also add a unique identifier along with inline / block comments to identify the location of code change.

·         Single line comments should begin with – and multi line comments should begin with /* and end with */

·         All ORACLE reserved words should be capitalized.

·         No special characters allowed in the name apart from underscore.

·         Maximum length of procedure name/ function name/ package name/ view name should not exceed 30 characters

   

1.2            TABLES, VIEWS AND COLUMNS

·          Naming standards should be followed correctly as given below.

-- Table name must be derived from the business name identified during analysis.

-- View name must be according to business requirement.

·         Table names should accurately reflect the table's content.

·         Each column name must be derived from the business name identified during the business/data analysis process.

·         Check table / object names are not used as variable names.

·         Check reserved or key words like are not used as object / variable names.

1.3            PACKAGES

·         Check the Naming standards are followed correctly as given below:

 --  Packages should have prefix of pkg_

 -- Package name should accurately reflect the overall functionality code.

1.4            PROCEDURES

·         Check that Naming standards are followed correctly as given below.

-- Procedures should have a prefix of  prc_

-- Procedure must have procedure specification and body.

·         Procedure name should accurately reflect the functionality

·         The variables within the Procedure should be given a meaningful name.

1.5            DECLARATION

·         All variables should be declared and with correct size.

·         All variables should be initialized.

·         Check that no unused variables are declared.

·         Check that Single letter is not used for variables, counters and indices or loop controls.

·         Check that Variable Naming standards are followed correctly as given below.

-- Parameter variables should have pi_ , po_ or pio_ as prefix in their name based on IN, OUT, INOUT type of parameter.

-- Global variables should have gv_ prefixed to their name.

-- Local variables should have lv_ prefixed to their name.

-- Global constants should have gc_ prefixed to their name.

-- Local constants should have lc_ prefixed to their name.

-- Cursors should have cur_ prefixed to their name.

·         Check Variable data type and length is match with field data type and length to avoid 

truncation problem.

1.6            BODY SECTION

·         A blank line should separate the code and DECLARE/ BEGIN/ EXCEPTION/ END.

·         Check that there is no infinite loop present.

·         Check that each logical segment has suitable comment to explain the functionality of the 
segment.

·          Check that there should be proper indentation to improve readability of code.

·         All loops should be indented inside of each loop construct.

·         All IF statements should have a corresponding END-IF.

·         SQL should always list the named columns to be returned to the program. SELECT * 
should never  be used

·         Sub queries should be avoided as much as possible and JOIN should be used instead 
wherever  possible.

·         Comma and Colons (to add each host variable in fetch clause) should be placed at the 
beginning of the next line.

·         WHERE Clause conditions should be stated with positive operators rather than using NOT

(Wherever possible).

·         Check that no unwanted data is retrieved as part of any SQL statement.

·          Constructs should use tab-indenting (if they are small) or space-indenting (if they are 
large) to promote readability.

·         Always use a column list in your INSERT statements

·         Use single blank lines to separate logical pieces of Script.

1.7            CURSOR HANDLING

·         Check that an SQL returning more than one row is defined within an explicit cursor.

·         The ORDER BY phrase in SQL should be used with all cursor processing where sequence is important.

·         For explicit cursors: if OPEN statement is used then check CLOSE statement is used.