PL/SQL Coding Standards and Best Practices

The following are the guidelines for PL/SQL best practices:

1) While writing PL/SQL, use the correct data type so that implicit conversion will be avoided

2) Use bind variable to avoid unnecessary parsing

3) Use BULK COLLECT, % ATTRIBUTES wherever required

4) MODULARITY

Write the code that fit into your screen size.
Through successive refinement, you can reduce a complex problem to a
set of simple problems that have easy-to-implement solutions.

5) EXCEPTION WHEN OTHERS is almost always a BUG unless it is immediately
followed by a RAISE.  Use WHEN OTHERS exception as the last resort and handle exceptions.  For example:

EXCEPTION
WHEN OTHERS THEN
if (sqlcode=-54) then
.... deal with it.
else
RAISE;
end if;

6) Tom's Mantra

If (possible in SQL)
do it;
else if(possible in PL/SQL)
do it;
else if(possible in JAVA)
do it;
else
..
..
end if;

7) % ATTRIBUTES

Use %TYPE and %ROWTYPE attributes. No code change is required when schema structure changes. 

8) BEFORE VS AFTER TRIGGER

NEVER USE BEFORE TRIGGER FOR VALIDATIONS. Use BEFORE triggers ONLY to modify :NEW value.

Oracle Documentation States:"BEFORE row triggers are slightly more efficient than AFTER row triggers. With AFTER row triggers, affected data blocks must be read (logical read, not physical read) once for the trigger and then again for the triggering statement. Alternatively, with BEFORE row triggers, the data blocks must be read only once for both the triggering statement and the trigger."

9)  For an application to be considered successful, it must satisfy several criteria, listed here in order of importance. The application must be

1. Correct. The application must meet user requirements. If it doesn’t do what the user wants it to do, the project will be a complete or partial failure, depending on the extent to which it falls short.

2. Fast enough. The code must run fast enough to (at least) minimize user frustration. If the application meets user needs but runs so slowly that users want to throw their monitors out the window, then it cannot be deemed a success.

3. Maintainable. The code we write today is the code users will run tomorrow, next year, and most likely next decade. If we don’t write our code so that it can be maintained easily and quickly, an initial success will soon turn into a failure, because keeping it running will require inordinate resources.

“Correct” Requires Testing

There is just one way to guarantee that our application code meets user requirements: testing it. Without rigorous, comprehensive, and repeatable testing, we have no way of knowing that the application works. We simply hope for the best.

Manual testing is doomed to disappoint. The best practice is simple to state and challenging to implement: you must commit to automated regression testing of your back-end code and then find a tool that will help you achieve this goal. Options for automated PL/SQL testing include utPLSQL, PLUTO, PL/Unit, DbFit, and Quest Code Tester for Oracle. Each has its own strengths and weaknesses, but any of these will help you establish regression tests that can confirm whether your application is correct.

“Fast Enough” Requires Optimization

The most important thing you can do to ensure that your code’s performance meets user expectations is to rely on key optimization techniques in PL/SQL. Many of these techniques are covered in other PL/SQL Practices columns in Oracle Magazine , so I will simply summarize the important performance-related features of PL/SQL:

The optimizing compiler. Oracle Database 10g introduced a compiler that automatically optimizes your code to maximize performance. So the rule of thumb here is: use it!

Bulk processing with FORALL and BULK COLLECT. These two features will reduce by an order of magnitude or more the time it takes to execute multirow SQL operations (such as a cursor FOR loop containing an INSERT statement).

Pipelined table functions. Table functions can be called from within the FROM clause of a SELECT statement as if they were relational tables. Pipelined table functions are a specialized form that can be used to improve performance.

Function result cache. New to Oracle Database 11g, the function result cache can reduce execution time for functions that retrieve data from tables whose contents change less frequently than they are queried.

Native compilation. When this feature is used, the runtime PL/SQL engine calls a platform-specific dynamic-link library (DLL) rather than scanning the PL/SQL machine code that is produced with nonnative execution.

These performance features will almost always substantially reduce execution time. In addition, PL/SQL offers more-specialized techniques you should consider employing when you identify bottlenecks in your application and need to eke out every possible performance improvement. To identify bottlenecks, you can now (with Oracle Database 11g) use the traditional PL/SQL profiler (DBMS_PROFILER) or the new hierarchical profiler (DBMS_HPROF) to find the lines of code or entire subprograms that consume too much time.

“Maintainable” Requires Standards

If everyone on your team has the freedom to write code to their own specifications, you will end up with a truly horrible mess on your hands. High-quality applications are always based on a smart and practical set of standards. The five key elements for which standards must be set to avoid code chaos are the following:

Naming conventions and syntax standards. All developers should follow the same rules for the names given to identifiers (tables, program units, variables, and so on). Beyond this relatively superficial aspect of standards, you should also develop a set of rules that outlines how best to use PL/SQL. For example: use local subprograms and other modularization techniques to keep your executable sections small and self-documenting, do not use GOTO unless absolutely necessary, and avoid multiple RETURNs in the executable sections of your functions.

Writing SQL in PL/SQL. The SQL statements in our applications are the root of most performance issues and must constantly be changed to reflect changes in the business model. I rarely encounter a development team that has even one single rule for how, when, and where to write SQL statements in their PL/SQL program units. And a single rule will do: never repeat the same logical statement in your application code. Repetition complicates efforts to optimize and maintain your code. Instead, build a separate data access layer, also known as a “table API” or “transaction API,” that hides all of your SQL. Your application code then simply calls procedures and functions in this API to perform SQL operations.

Error management. We’re not paid to write execution sections; we’re paid to implement user requirements. For that reason, among others, we seldom consider how errors are trapped, handled, logged, and communicated to users. Instead, everyone writes their own error management code, with terrible consequences: information is logged inconsistently, and key features of PL/SQL (such as the backtrace function and data manipulation language error logging) are ignored. The solution is straightforward: everyone on the team should use the same shared utility to log errors and communicate them to your users.

Application tracing. Most applications are complex, containing thousands of program units and tens (or hundreds) of thousands of lines of code. It is not unusual, therefore, to have trouble understanding what all that code is doing, especially when a user reports a problem you cannot reproduce. For such situations, tracing is a powerful analytical tool. Application tracing, a form of code instrumentation, should be standardized for an application team and used both proactively as you build code and retroactively to get more information out of particularly hard-to-understand parts of your code. Options for tracing include DBMS_APPLICATION_INFO and DBMS_MONITOR from Oracle; Quest Error Manager tracing; and Log4PLSQL, an open source framework built on the popular Log4J.

Version control and backups. You must control access to your software by using a tool that enables you to check changes in and out and ensures that you never experience a “lost update” on your code. In addition, you must have regular, automated backups in place to minimize vulnerability and avoid any kind of catastrophic loss.

Passive Standards Not Enough

Setting standards is just the first step to building code that is based on those standards. You also need to find ways to help developers follow the standards and to verify that they were followed. The best way to ensure that standards are followed is to introduce both peer and automated code review to your development lifecycle.

Have regular team meetings to look over each other’s code, offer ideas for improvement, and learn from each other. You should also complement peer review with automated review.

Automatic code analysis is difficult, but consider using these options:

·      Queries against data dictionary views, including ALL_SOURCE, ALL_ARGUMENTS, ALL_DEPENDENCIES, and ALL_OBJECTS. You can search for text that should and should not be present in programs, clean up dependencies between program units, and much more. 

·      IDE-based, automated code analysis. Some PL/SQL editors offer built-in tools for parsing and analyzing code according to a predefined set of best practices. 

·      PL/SQL compile time warnings. In Oracle Database 10g and higher, with warnings enabled, the compiler will not only report errors but also will give you feedback on the quality of your code. 

·      PL/Scope. New to Oracle Database 11g, PL/Scope is a compiler feature that, when enabled, gathers information about how your identifiers (named elements such as variables, constants, subprograms, and exceptions) are used in your code. You can then execute queries on the contents of the ALL_IDENTIFIERS data dictionary view to perform operations that previously were not possible, such as “Find all the lines of code in my subprogram where the l_name variable is assigned a value.” You can also write queries to validate conformance to naming conventions.

Prepare Carefully

Finally, if you want to be successful with your next application, you need to take some time up front to set standards and put processes in place that reinforce them. Before you start writing new code, make sure the answer to each of the following questions is a clear and emphatic “Yes!”:

·      Are you going to build automated regression tests for your back-end code?

·      Have you picked a tool for your testing?

·      Has everyone agreed to naming standards and coding conventions for PL/SQL programs?

·      Do you have rules in place for writing SQL that include putting the SQL statements inside a data access layer?

·      Do you have a shared utility for handling, logging, and raising errors?

·      Have you selected an application tracing utility?

·      Do you know how you will verify conformance to your naming standards and coding conventions?

If you take the time to establish these procedures, you can be proud of the resulting application and know that it will be easy to maintain in the coming years.

10)  Make full use of the appropriate, provided features of the programming language.

11)  Use UPPER-lower case method to make code more readable.

12)  Use consistent indentation

13)  Avoid hard-coded literals of any kind in your programs.

14)  Make sure your name describes the module accurately.

15)  Test all of your assumptions as you debug your code.

16)  Use anchored declarations whenever possible (%TYPE and %ROWTYPE).

17)  Streamline your code with records.

18)  Optimize foreign key lookups with PL/SQL tables.

19)  Use Boolean variables and functions to greatly improve the readability of your programs.

20)  Leverage fully the built-in functions, PL/SQL offers dozens of built-in functions to help you get your job done with the minimum amount of code and fuss possible.

21)  Get familiar with the new built-in packages.

22)  Take advantage of the cursor FOR loop.

23)  Don't declare your FOR loop index.

24)  Avoid unstructured exits from loops.

25)  Construct abstract data types (ADT) with PL/SQL packages.

26)  Enhance scope control with nested blocks

27)  Overload modules to make your software smarter.

28)  Use local modules to reduce code volume and improve readability.

29)  Code a single RETURN for successful function execution.

30)  Use Self-identifying Parameters (Avoid Boolean Values).

I hope you all have enjoyed reading this article. Comments are welcome....