SQL and PL/SQL Best Practices and Standards

SQL and PL/SQL Best Practices and Standards

1. Oracle Apps standard naming convention has been followed.

2. All PL/SQL functions and procedures will be implemented as part of a package. The package names used will be specified in the component specification

3. Package header source files will contain a package level comment which includes the following information: FileName, Component Name, Description, Package Name, Designer Name, Developer Name, Version, Date and Copyright statement

4. SQL and PL/SQL reserved words (SELECT, INSERT, PACKAGE, FUNCTION, etc.) will be capitalized.

5. Variable names will be all lower case, with individual words separated by an underscore

6. Use %TYPE or %ROWTYPE where ever possible

7. Functions should not contain ‘OUT’ parameters

8. The ‘ELSIF/ELSE’ construct will be used in preference to multiple or nested conditionals

9. It’s considered bad practice to exit from the middle of a loop. Each loop should include only a single exit point

10. Package is usually created for
          1.Logical containers for related elements
          2.Overloading
          3.Package-level data and caching
          4.Initialization section
          5.Local or nested modules
          6.To keep executable sections small/tiny

11. Remove any unused variables from code

12. Define your subprograms as close as possible to their usage(s).The shorter the distance from usage to definition, the easier it is to find, understand and maintain that code.

13. Whenever call API, use parameters name with assigned values, instead of passing only values.

14. Identifier naming convention adopted for project

15. Use standard comments for version tracking

16. Number versions in whole numbers

17. Only one statement per line

18. Commas should be leading in stacked lists

19. Document (in English) what each parm is

20. Appropriate grants have to be incorporated into the scripts

21. Indent and align nested control structures, continuation lines, and embedded units consistently.

22. Always indent the comments at the same level as the code which they describe

23. Inline comments/Header comments have been added

24. End label for all program units, loops and nested blocks

25. Application code must raise, handle, log and communicate errors in a consistent manner

26. PL/SQL Exception handled.

27. Exceptions will be used to indicate errors, not as a normal method for branching control .Exception handling code will catch specific exceptions and will only use the ‘WHEN OTHERS’ construct in the outermost code.

28. A cursor for loop will not be used to retrieve a single row. Cursor for loops will only be used when every row in the cursor will be processed.

29. Cursors will be fetched into cursor records, not into multiple variables

30. All Cursors properly closed

31. Do Performance Analysis and use proper comparison tool like TKPROF , TOAD utilities for Explain Plan

Posted in: Best Practices and Standards