External Tables : <name>_EXT
PL/SQL Packages : <name>_API
PL/SQL Procedures : <name>_PRC
PL/SQL Functions : <name>_FUN
Package Global Variables: g_variable_name
Local Variables : l_variable_name
Types : t_type_name
Cursors : c_cursor_name
Exceptions : e_exception_name
Input Parameters : i_parameter_name
Outut Parameters : o_parameter_name
In/Out Parameters : io_parameter_name
1) Make all the PL-SQL key words in uppercase and application-specific identifiers in Lower Case
2) Make proper Indents 3 spaces for normal conditions and 5 spaces for When condition of exception handler .
3) Never hard cord a literal even though the value is same all over the application . Declare a constant which holds the literal values .
4) Naming of the Modules should be meaningfull . Procedure - verb_subject . Function Name should be Description of the returned value .
5) Test every possible senarios
6) Use anchored declaration where ever possible - use %TYPE and %ROWTYPE --- ls_mycompany company.companyname%type advantange is if you declare varchar2(30) and when you table size increases your code will give bug
7) Hide your Business Logic
8) Try to use records in the code . Benefits Less code and changes in database will not reflect in the code .
9) Use all the Built in Functions Availabe like LENGTH,SUBSTR,INSTR etc.
10) Be familiar with all new function from Oracle PL-SQL
11) Take advantage of PL-SQL For loop
12) Be careful when write exits from loops . Write structured exits from loop . Always write exit statment in a simple loop and never write exit from a while loop .
13) Enhance scope control with nested blocks
14) Function overloading can be done for effective for readibilty of the code
15) Use Self identifying parmaters
