1. Header
Script Files
Section Heading
Section Contents
Author
The name of the person who created it
Date Written
The completion date for the intial code release/ file creation
Description
A brief explanation of the task the code performed
Version Number
The Present Version Number
Example: Script file insertscript will contain:
Rem Author : Rhodes G.
Rem Created On : 09/09/2004
Rem Purpose : to populate master data
Rem Version No : v1.0
Triggers
Section Heading
Section Contents
Name
The name of the trigger.
Author
The name of the person who created it
Date Written
The completion date for the intial code release/ file creation
Description
A brief explanation of the task the code performed
Calls
(Optional) A list of procedures and functions by the code.
Updates
A history of code revision listing author, date and revision description
Version Number
The Present Version Number
Example:
/*
Name : tg_bl_updatePolReg
Description : to maintain history
Author : Rhodes G.
Date Written : 09/09/2004
Calls : pr_insert_his
Change Register :
-------------------------------------------------------------------------------------------------------------
Change # Date Author Description Review by
-------------------------------------------------------------------------------------------------------------
9999 99/99/9999 XXXXXXX XXXXXXXXXXXXXXXXXX XXXXXXX
XXXXXXXXXXXXXXXXXX
-------------------------------------------------------------------------------------------------------------
Version Number : v1.0
*/
Procedures and Functions
Section Heading
Section Contents
Name
The name of the trigger.
Description
A brief explanation of the task the code performed
Author
The name of the person who created it
Date Written
The completion date for the intial code release/ file creation
Inputs
List of incoming parameters with a brief description of each
Outputs
List of Output parameters with a brief description of each
Table Accessed
List of tables on which insert, update & delete is performed
Calls Called by
(Optional) A list of procedures and functions by the code.
Change Register
A history of code revision listing author, date and revision description
Version Number
The Present Version Number
Example:
/*
Name : pkg_manual_bills
Description : to generate manual bills
Author : Rhodes G.
Date Written : 09/09/2004
Inputs : policy_no – Policy no for which bills to be generated
Period - Billing Year & month YYYYMM
Outputs : Bill_no - System generated Bill no.
Table Accessed : Insert on –
Update on -
Delete on -
Calls :
Change Register :
-------------------------------------------------------------------------------------------------------------
Change # Date Author Description Review by
-------------------------------------------------------------------------------------------------------------
9999 99/99/9999 XXXXXXX XXXXXXXXXXXXXXXXXX XXXXXXX
XXXXXXXXXXXXXXXXXX
-------------------------------------------------------------------------------------------------------------
Version Number : v1.0
*/
2. Comments
There are a number of locations in PL/SQL code where comments are expected, the following instances,
Before each Loop structure.
Before each Begin….End sub-block.
Before each conditional logic expression (IF
Before any other logically significant statements.
If a comment is required placed the comment on the line immediately preceding the line of code. Do not append comments to the end of code. If a comment is warranted by the complexity of the code and you have used meaningful identifiers, the comment should be complicated enough that you need to explain the situation using more than one or two words.
For example
Correct way:
--
--If the balance is over the maximum balance allowed,
--Do not allow further charges.
--
IF (n_account_balance > MAX_BALANCE) THEN
…………
…………
Incorrect way:
IF (n_account_balance > MAX_BALANCE) THEN
…………
…………
3. Indentation
Indentation is the most crucial factor affecting the readability of the code. Consistent indentation makes the code appear structured and makes the code more readable by showing the levels of logic with in the code.
These are the guidelines for indentating code:
· Be consistent i.e. use the same number of spaces for indentation throughout your code.
· Don’t use too much or too little indentations. Four spaces are the most effective level of indentation. When the code is long TAB may used as the standard. Less indentation is not very useful, and more wastes spaces. Remember that 80 character is line limit.
· Indent the variable and constant definition in the variable declaration portion of a PL/SQL block
· Indent WHEN statement following an exception handler to the first level of indentation. Indent the statement following the WHEN statement to the second level of indentation.
EXCEPTION
WHEN NO_DATA_FOUND_THEN
The following are the guidelines for indenting that relate directly to stored prodedure and functions
· When invoking a stored procedure or function, begin the indentation of the parameter at the end of the procedure or function name. Place only one parameters per line and align all the parameters in a columnar fashion.
For example,
Pr_cal_billdue_dt (prm_policy_no,
prm_policy_effdt);
· When defining the parameters for stored procedure or function, align the IN, OUT and IN OUT parameter type definitions vertically, leaving space for both keywords even if and only one keyword is used.
PROCEDURE sample_proc (prm_1 IN number,
prm_2 IN OUT number,
prm_3 OUT varchar2);
Line Length
Avoid lines loner than 80 characters, Since they are not handled well by many terminals and tools.
Wrapping Line
When an expression will not fit on a single line, break it according ro these general principals,
· Break after a comma.
· Break after an operator.
· Prefer higher level breaks to lower level break.
· Align the new line with the beginning of the expression at the same level on the previous line.
In the above rules lead to confusing code or to code that’s squished up against the right margin, just intead by a TAB instead.
4. Naming Conventions
Variables
Types
Prefix
Examples
Local variables
mmy_
mmy_policy_no , mmy_policy_renew_no etc
Package variables
cmn_
cmn_policy_no , cmn_policy_renew_no etc
Parameters
prm_
Prm_policy_no , prm_policy_renew_no etc
global variables
glb_
:GLOBAL.glb_policy_no , :GLOBAL.glb_policy_renew_no etc
Objects
Types
Prefix
Examples
Primary key constraint
Pk_
Unique key constraint
Uk_
Foreign key constraint
Fk_
Check constraint
Ck_
Foreign key index
Fki_
Indexes
I_
Views
V_
Sequence
Seq_
Snapshots
Sn_
Tablespace
Ts_
Clusters
Clu_
Using %TYPE and %ROWTYPE
The use of %TYPE is recommended when declaring variables and constants that refer to a column in a table. Declaration using %TYPE is also useful when dependencies are desired between database objects and variables or parameters.
Due to a decrease in readability when using %TYPE, it is essential that meaningful identifier names can be used.
The use of %ROWTYPE is encouraged, providing comments are used to improve readability of the code. Due to the likelihood of changes in database structure, declarations using %ROWTYPE will help streamline the maintenance of existing procedure during the development process.
5. Programming Practices
Codding Conventions
Coding conventions are suggestions to help standardize the structure and coding style of a script or set of script so that you and others can easily read abd ubderstand the code. Using proper coding sonventions result in precise, readable, and unambiguous source code that is consistent with other language conventions and is intuitive as possible.
By adopting proper coding conventions early in the development cycle, you greatly increase the effectiveness of your development staff throughout the product cycle.
Naming
A variable name should be complete as necessary to describe its purpose. For frequently used or long terms, standards abbreviations are recommended to help keep name length reasonable. In general, variable names greater than 32 characters can be difficult to read. When using abbreviations, make sure they are consistent throughout the entire script.
For example, randomly switching between the variable name cnt and count within a script may lead to confusion.
Declarations
One declaration per line is recommended since it encourages commenting.
For example,
mmy_policy_no policy_reg.policy_no%type; --Policy number
mmy_policy_effdt policy_reg.policy_effdt%type; --Eff. Date
Capitalization
An important aspect of how code is presented is capitalization. This section outlines the rules for both partially and fully capitalized identifiers within code,
· All reserve words, built in functions, pre-defined types, Sql keywords should always be presented in upper case.
· The names of all the standard and user-defined exceptions should be listed in full uppercase.
· Below is the list of all the standard exceptions provided by PL/SQL
CURSOR_ALREADY_OPEN
DUP_VAL_ON_INDEX
INVALID_CURSOR
INVALID_NUMBER
LOGON_DENIED
NO_DATA_FOUND
OTHERS
PROGRAM_ERROR
STORAGE_ERROR
TIMEOUT_ON_RESOURCE
TOO_MANY_ROWS
TRANSACTION_BACKED_OUT
VALUE_ERROR
ZERO_DIVIDE
· The names of all the database triggers should be listed in full upper case.
· All user-defined and other datatypes will be listed in lower case.
· The names of all the tables, views, snapshots, synonyms will be listed in lowercase
All text that is not covered by these rules will be presented in lower case.
6. Errors/ Exceptions Handling
Errors and exeptions should be handled at every level of the application.
This control should be incorporated in the following components,
· Scripts
· Functions
· Procedures
· Triggers
a. In any database application. DML operations such as SELECT, UPDATE, INSERT, DELETE return an error code if they fail due to violation of constraints.
b. The constraints can be Foreign key violation, Primary key violation, Column size mismatch, datatype mismatch, etc.
c. Typically, these error codes or the text populated by the system do not make sense to the end user. The error handling system should intelligently interpret the error code or the text associated with it and return a clear and simple error message to the end user.
In case of error, the error trapping module should preferably identify the error and populate the best suited context sensitive message such as “policy no does not exist” in absence of policy number while reference through a foreign key.
Make it a practice to have EXCEPTION, in all the blocks even if exceptions / errors have no significance.
In cases where exception need not be handled, use WHEN_OTHERS. Ensure that not even a single DML is left without provision for exception.
e.g.
SELECT policy_effdt
FROM policy_reg INTO mmy_polieffdt
WHERE policy_no = ‘VG01’;
EXCEPTION
WHEN NO_DATA_FOUND then
Following are the guidelines for EXCEPTION handling.
1. Every subroutine (Procedure / Function / Trigger) must have WHEN OTHERS handler at the end.This will take care of any error
2. Every procedure/ functions must have two parameters i.e. prm_success_failure and prm_error_message. If a Logical error occurs (Due to Missing information or business rue violation), then set the prm_success_failure to '99. The error message should be driven from the Error master table. Do not hardcode the error message in the subroutine rather pick the error description from the error master table using the error code
3. For any program error due to unhanded exceptions, which are trapped in the Final block of the routine using WHEN OTHERS, set the prm_success_failure to '99', Also track the proper type of error using sqlcode and sqlerrm.
4. Log each and every error in the Error log table. Use the Diag package to log the error. Use the diag.ins_error_log and diag.ins_edit_log to log the errors. Avoid using the diag.pr for error logging.
5. Always log the EL_SOURCE and EL_ADDITIONAL_REFERENCE while logging any error in the error log. The el_source should be the Name of the Package. And el_additional_reference should be the name of the procedure/function inside the package. In case, it's a standalone procedure/Function, keep the el_source and el_additional_reference same.
6. If the error is in the batch object, while logging the error in error log ensure that you populate the EL_BATCH_EXEC_NO column of the error log with the be_sequence.
No comments:
Post a Comment