Oracle PL SQL Coding Standards

Oracle PL/SQL Coding Standards

Why You Need Coding Standard?

The best applications are coded properly. This sounds like an obvious statement, but by 'properly', it means that the code not only does its job well, but is also easy to add, to maintain and debug.

This "maintainable code" is a popular talking point among those involved in standardization of coding in all languages. There's nothing worse than inheriting an application or needing to make changes to code that requires a lot of energy to decipher – you end up trawling through lines and lines of code that doesn't make its purpose or intentions clear. Looking through unfamiliar code is much easier if it is laid out well and everything is neatly commented with details that explain any complicated constructs and the reasoning behind them.

The Problem

·                     Can you actually read the code? Is it spaced out clearly?

·                     If you come back to the code in a few weeks or months, will you be able to work out what’s happening without needing to look at every line?

·                     How are you commenting the work?

·                     Have you used complex language functions/constructs that are quicker to write but affect readability?

When we learn a new language, we usually begin to code in a specific style. In most cases, we'll write in a style that we want, not one that has been suggested to us. But once we start to code using a particular style, like a spoken language dialect, it will become second nature -- we'll use that style in everything we create. Such a style might include the conventions we use to name variables and functions (v_userName, v_username or v_user_name for example), and how we comment our work. Any style should ensure that we can read our code easily.

However, what happens when we start to code bigger projects and introduce additional people to help create a large application? Conflicts in the way you write your code will most definitely appear.

The Solution: a Coding Standards Document

A coding standards document tells developers how they must write their code. Instead of each developer coding in their own preferred style, they will write all code to the standards outlined in the document. This makes sure that a large project is coded in a consistent style -- parts are not written differently by different programmers. Not only does this solution make the code easier to understand, it also ensures that any developer who looks at the code will know what to expect throughout the entire application. Developers will likely adopt the style gradually, just as an accent develops over time.

Naming conventions

General Naming Standards

Use meaningful names!  Avoid using short or arbitrary variable names such as "x" or "var".  These names convey nothing about what is stored within the variable.  Object names in your code should cause your code to be self-documenting.  In other words, if you name your items meaningfully, it should usually be quite obvious what your code does.

If you are naming a variable or a parameter that references a value stored in a database column, use the column name for your variable name.  For example, a local variable used to store the value of the HOME_PHONE column in a table would be called l_home_phone.

Never use "a", "b", "c", etc. as table aliases in your SQL statements.  A query joining several tables quickly becomes difficult to read and debug when you have WHERE clause conditions like:

SELECT a.order_date

FROM   orders a,

       order_lines b,

       line_prices c

WHERE  a.order_id      = b.order_id

AND    b.order_line_id = c.order_line_id;

Use meaningful table aliases instead.  Consider the difference:

SELECT ord.order_date

FROM   orders ord,

       order_lines line,

       line_prices prc

WHERE  ord.order_id       = line.order_id

AND    line.order_line_id = prc.order_line_id;

A third option that requires less typing but still provides more meaning than (than single-letter aliases) is to use the initial letters of the words in the table name to provide the aliases:

SELECT o.order_date

FROM   orders o,

       order_lines ol,

       line_prices lp

WHERE  o.order_id       = ol.order_id

AND    ol.order_line_id = lp.order_line_id;

The second and third examples above are acceptable, but the first example is not.  The third option also provides one benefit that the others do not: a fairly consistent rule for naming table aliases.  You may have to bend the rule slightly when joining tables with the same initials, but it works fine in most cases.

Use underscores to separate words or identifiers in all names, e.g. “use_names_like_this”.  Do not used mixed case for this purpose, e.g. “DontUseNamesLikeThis” (see the section on Capitalization).

When declaring datatypes of variables or parameters, use anchoring if your variable or parameter refers to the value in a database column.  Anchoring allows the developer to “anchor” the variable datatype to the datatype of the underlying column in the table.  Anchoring is done using the syntax “table_name.column_name%TYPE” in place of an actual datatype.

Variables

Variable scope is determined by a prefix of “l” for local variables or “g” for global variables.  Constant variables include the letter “c” in the prefix.  Scalar variables are additionally identified by suffix.

Local Variables

Prefix local variable names with the letter “l” and an underscore, e.g. “l_variable_name”.  This includes all variables that are local in scope to the current procedure or function.

Global Variables

Prefix global variable names with “g_”, e.g. “g_variable_name”.  This includes package-level variables that are global in scope to all procedures and functions within the package.

Cursors

Suffix cursor names with “_cur”, e.g. “l_cursor_name_cur”.  Note that cursors must be identified as local or global just like any other variable name.

Records

Suffix variables that store scalar record data with “_rec”, e.g. “l_record_name_rec”.  This applies to programmer-defined records as well as cursor or table-based record variables.

Record Types

Record type names should include the suffix “_rec_type” after the record name, e.g. “l_record_name_rec_type”.

Constants

Constant variables should include the letter “c” immediately after the prefix letter indicating scope, e.g. “lc_variable_name” or “gc_variable_name”.

PL/SQL Tables

Table names should include the suffix “_tab” after the table name, e.g. “l_table_name_tab”.

PL/SQL Table Types

Table type names should include the suffix “_tab_type” after the table name, e.g. “l_table_type_name_tab_type”.

Parameters

Procedure and Function Parameters

IN parameters should be prefixed with “i_”, e.g. i_parameter_name.

OUT parameters should be prefixed with “o_”, e.g. “o_parameter_name”.

IN OUT parameters should be prefixed by “io_”, e.g. “io_parameter_name”.

Cursor Parameter

Cursor parameters should be prefixed with “p_”, e.g. “p_cursor_parameter”.

Procedures & Functions

When naming procedures, use a verb-noun combination that describes what the procedure does: for example, validate_customer_phone.  When naming functions, think about how the function will be called from code and name it something that makes sense in that context.  For example, customer_no_is_valid is a function that returns a BOOLEAN value.  In code you would call this function like this:

IF customer_no_is_valid(l_customer_no) THEN
  do_something;
END IF;

Using customer_no_is_valid for the function name makes the code more readable than something like this does:

IF validate_customer_no(l_customer_no) THEN
  do_something;
END IF;

Packages

Package names should be suffixed by “_pack”, e.g. “package_name_pack”.  Name packages based on the function of the package.  For example, if a package contains procedures and functions written to read and write data to and from the order_statuses table, name the package “order_statuses_pack”.

Code Formatting Standards

Capitalization

All SQL keywords should be in all caps.  Any Oracle keywords or built-ins should also be in all caps.  Everything else should be in all lower case.  Do not use mixed case for anything.

This method makes it easy to differentiate between a built-in procedure call and a call to a custom procedure.

Indentation

Three spaces should be used (instead of the TAB key) whenever an indent is required.  DO NOT use embedded TAB characters. Use indentation:

Using three spaces instead of embedded tabs may seem harsh for those of us who prefer to use tabs to indent code, but there are several good reasons for this:

·         Tabs are not portable across editors

Different programmers use different editors to work on code, and some editors use a different spacing than others.  Many editors, including Notepad, do not allow tab spacing to be modified.  If another programmer needs to look at or modify your code, and she uses a different editor than you did, she will likely have a mess on her hands.

·         Tabs do not store code properly in the database

Database source code can be viewed using the data dictionary, either through the USER_SOURCE, ALL_SOURCE, or DBA_SOURCE views.  These views do not properly display code that has been formatted with embedded tabs.  This makes it difficult to look at stored code when it may be unnecessary to check out the source code file.

·         Tabs are usually too long

Notepad and many other editors use 8 spaces to display a tab character.  Nested structures quickly run off the side of the page when displaying this code.  Three spaces is deep enough to offset the structure of your code, but not so deep that nested code runs off the right margin.  If your code is nested too deeply to get it on the page using three space indents, then you may want to consider ways to modularize your code more effectively.

If you are accustomed to using tabs instead of spaces to indent code, consider using a third-party editor to manage your code.  There are many freeware and public domain editors designed for programmers that will automatically insert a user-defined number of spaces when you hit the tab key.  One such editor is PL/SQL Developer.  Information about this particular freeware editor is available at the following URL: http://www.allroundautomations.com/plsqldev.html

Alignment

Align code consistently:

·         Left-align DECLARE, BEGIN, EXCEPTION, and END statements.

·         Left-align SQL statement keywords with each other.

·         Left-align application-specific components (don’t worry about making this work with GROUP BY or ORDER BY clauses, or with other clauses that are longer than the SELECT keyword, such as DELETE FROM or INSERT INTO).

·         When you have more than one conditional or assignment statement, align the elements of each condition with similar elements from other conditions.  For example, if you have a long WHERE clause with several conditions, align all the operators (i.e. “=”, “>”, etc.) with each other and align all the right-hand side of the conditions with each other.  The example statements below use this alignment style.  Note that this standard may sometimes be ignored if one conditional statement is substantially longer than the others.  In this case, only align the conditions where it makes sense.

·         In procedure and function declarations, left-align parameter names with each other, left-align parameter directions with each other, and left-align parameter data types with each other (see example below).

Example SQL statements:

SELECT t.tag_no,

       t.weight,

       t.piece_count

FROM   cs_tags t,

       location_identifiers li,

       cs_location_statuses ls

WHERE  t.location_token   =  li.location_token

AND    li.site_no         =  i_site_no

AND    li.location_status =  ls.location_status

AND    ls.sell_flag       =  'Y'

AND    li.location_type   =  'F'

AND    t.afs_flag         =  'Y'

AND    t.part_no          =  i_part_no

AND    t.contract         =  i_contract

AND    t.piece_count      <> i_piece_count

AND    t.tag_no NOT IN (SELECT tag_no

                        FROM   cs_tag_orders

                        WHERE  release_status = 'ACTIVE');

UPDATE cs_tag_orders

SET    release_status = 'ACTIVE',

       shippable_flag = 'Y'

WHERE  tag_no     = l_tag_no(l_loop_index)

AND    order_no   = i_order_no

AND    way_longer_name_than_the_others = i_way_longer_name_than_the_others

AND    order_code = i_order_code

AND    line_no    = i_line_no;

INSERT INTO cs_tag_orders

  (order_no

  ,order_code

  ,line_no

  ,rel_no)

VALUES

  ('123456'

  ,'O'

  ,1

  ,0);

Note - either of the following two syntaxes is legal - developers should use whichever method they prefer.

DELETE FROM cs_tag_orders

WHERE  order_no   = i_order_no

AND    order_code = i_order_code

AND    line_no    = i_line_no;

DELETE cs_tag_orders

WHERE  order_no   = i_order_no

AND    order_code = i_order_code

AND    line_no    = i_line_no;

Example program alignment:

CREATE OR REPLACE PROCEDURE create_release (

   i_mill_order_no   IN     oeorder_detail.cs_mill_order_no%TYPE,

   io_dummy_param    IN OUT NUMBER)

IS

   CURSOR l_order_cur

   IS

      SELECT order_no, order_code, line_no

        FROM oeorder_detail

       WHERE cs_mill_order_no = i_mill_order_no;

   l_order_rec   l_order_cur%ROWTYPE;

BEGIN

   OPEN l_order_cur;

   FETCH l_order_cur INTO l_order_rec;

   CLOSE l_order_cur;

   FOR l_counter IN 1 .. 10

   LOOP

      INSERT INTO oeorder_release (order_no,

                                   order_code,

                                   line_no,

                                   rel_no)

           VALUES (order_rec.order_no,

                   order_rec.order_code,

                   order_rec.line_no,

                   l_counter);

   END LOOP;

EXCEPTION

   WHEN OTHERS

   THEN

      raise_an_error;

END create_release;

Example of bad statement alignment:

l_long_string := 'Tag ' || i_tag || ' has not passed the ' || i_inspection_test || ‘ that was performed on ‘ ||TO_CHAR(l_date_created, 'MM/DD/YYYY');

Better statement alignment:  

l_long_string := 'Tag number '            || i_tag             || ' ' ||

                 'has not passed '        || i_inspection_test || ' ' ||

                 'that was performed on ' || TO_CHAR(l_date_created, MM/DD/YYYY');

                 
Each will store ‘Tag 00001 has not passed the chemical test that was performed on 05/08/2007’.  If you need to change the message to read, ‘Tag 00001 has not passed the chemical test that was performed by QA tester Fuqan Ahmed on 05/08/2007’, the second one is much easier to maintain.  Here’s another example from an Oracle Forms program unit:

Poor alignment makes it hard to see the individual settings for each item:

SET_ITEM_PROPERTY(‘block1.item1’, ENABLE, PROPERTY_TRUE);

SET_ITEM_PROPERTY(‘block100.item100’, ENABLE, PROPERTY_TRUE);

SET_ITEM_PROPERTY(‘block10.item2’, ENABLE, PROPERTY_FALSE);

SET_ITEM_PROPERTY(‘block10000.item3’, ENABLE, PROPERTY_TRUE);

SET_ITEM_PROPERTY(‘block1.item2’, ENABLE, PROPERTY_TRUE);

SET_ITEM_PROPERTY(‘block100.item101’, ENABLE, PROPERTY_TRUE);

SET_ITEM_PROPERTY(‘block10.item3’, ENABLE, PROPERTY_TRUE);

SET_ITEM_PROPERTY(‘block10000.item4’, ENABLE, PROPERTY_TRUE);

Same code with parameters aligned.
SET_ITEM_PROPERTY(‘block1.item1’,     ENABLE, PROPERTY_TRUE);

SET_ITEM_PROPERTY(‘block100.item100’, ENABLE, PROPERTY_TRUE);

SET_ITEM_PROPERTY(‘block10.item2’,    ENABLE, PROPERTY_FALSE); 

SET_ITEM_PROPERTY(‘block10000.item3’, ENABLE, PROPERTY_TRUE);

SET_ITEM_PROPERTY(‘block1.item2’,     ENABLE, PROPERTY_TRUE);

SET_ITEM_PROPERTY(‘block100.item101’, ENABLE, PROPERTY_TRUE);

SET_ITEM_PROPERTY(‘block10.item3’,    ENABLE, PROPERTY_TRUE);

SET_ITEM_PROPERTY(‘block10000.item4’, ENABLE, PROPERTY_TRUE);

With the code aligned, it is much easier to see which item is set differently.

Using Commas to Align and Separate Elements

As you can see from some of the examples above, it is usually easier to read code that places each logical element on a separate line (e.g. the column names and values in the INSERT statement). 

When defining or calling procedures with multiple parameters, put only one parameter on each line.

PROCEDURE get_order

  (order_no   IN oeorder_detail.order_no%TYPE

  ,order_code IN oeorder_detail.order_code%TYPE

  ,line_no    IN oeorder_detail.line_no%TYPE)

get_order (l_order_no

          ,l_order_code

          ,l_line_no);

Commas should be placed at the beginning of the line when used to separate arguments, parameters, or other parenthetical lists.  This results in parameters that are lined up evenly, since the comma on the second and subsequent lines aligns with the opening parenthesis on the first line. 

Commas should be placed at the end of the line when used to separate elements not contained within parenthesis, as in the list of columns in a SELECT statement.  See the example SELECT and INSERT statements above for examples.

Comments

Include a standard header comment at the top of every procedure and function.  The header should be updated any time modifications are made to the code.  Note that the header is not intended to take the place of in-line comments – you should still comment your code internally.  The header should be considered a summary of the program unit.

/*

----------------------------------------------------------

Author   :  XXXXXXXXXX

Purpose  :  XXXXXXXXXXXXXXXXXXX

Logic Structure:

   <use this area to document the basic

   flow of the program at a high level.  You

   should also make notes about anything else

   that might help someone reading your code.>

Modification History:

Date        Name       Revision Summary

----------  ---------- -----------------------------------

----------------------------------------------------------

*/

Keep your audience in mind when commenting code.  If you are commenting a private package procedure, realize that the person who will need your comments is the person who is considering making changes to the procedure.  On the other hand, if you are commenting a package specification, the person reading your comments is the developer who will be using the procedures or functions within the package.  Tailor your comments accordingly: In package specifications, include comments that instruct the reader how to use the programs within the package.  In package bodies, include comments describing how the code works.

Use comments to explain complex logic in your code.  In general, if you follow good naming conventions and write modular code, your code will be largely self-documenting.  Don't include comments that repeat what the code tells the reader.  Example:

--if the status code is null, then raise an error
IF status_code IS NULL THEN

  RAISE FORM_TRIGGER_FAILURE;

END IF;

This is a useless comment.  Anyone reading your code would realize what the code does.  The comment simply re-states the code.

Here is an example of some code that is enhanced by a comment:

/*
this calculation added 26/05/2006 to correct rounding errors caused by the fact that we store lengths rounded to 3 decimal places.  This code basically increases the number of significant digits we can use to figure out a weight.  For example, suppose the length was 26.417, or 26 feet, 5 inches. The actual length is 26.416666666..repeating.  If we take .417 and multiply by 96, we get 40.032.  Round this and you get 40.  Now divide by 96 and you get .41666666..repeating.  We can add this back to our even length(26 feet) to get a real length_no to use for calculations.  96 was used because the smallest length we use is 1/8 inch, and there are 96 1/8 inch units in a foot.
*/

SELECT (((ROUND(96 * (length_no - FLOOR(length_no)))/96) + FLOOR(length_no))

        * cs.lbs_ft

        * i_qty) exact_length

FROM   cs_sizes cs,

       part_description pd

WHERE  pd.part_no   = i_part_no

AND    pd.contract  = i_contract

AND    pd.size_code = cs.size_code;

Obviously, the complicated calculation involved in this select statement needs some explanation, since it is not obvious what the calculation is doing just from looking at the code.  If you are unsure if a comment is necessary in a particular situation, go ahead and put it in.  As anyone who has done code maintenance will tell you, it is better to over-comment than under-comment.

To comment the body of your code, you may choose to use single-line (preceded by two dashes) or multi-line (begins with /* and ends with */) depending on the comment.  Align comments with the code they describe.  Avoid using several two dash single-line comments when your comment is multiple lines long.

PL/SQL Elements

Parameters

Do not use OUT or IN OUT parameters in a function.  A function should only be used when a single return value is needed (the function return).  If more return values are needed, use a procedure instead.

Likewise, if your procedure has only a single OUT parameter, use a function instead.

 Always identify parameter direction (IN, OUT, or IN OUT).  IN is the default direction if not specified, but code is more readable if every parameter direction is explicitly stated.  Cursor parameters must be IN parameters, so it is not necessary to state parameter direction for cursor parameters.

Use default values for parameters where appropriate, remembering not to hard code any literal values.  A good example of when to use default values is with Boolean parameters. Boolean parameters can be used to give the calling programs more control over how your program works.  For example, a program that creates a new customer might have a Boolean parameter called i_preferred_customer that will normally be FALSE.  If it is TRUE, the program performs some additional tasks specific to preferred customers.  It is a good idea to place Boolean parameters at the end of your parameter list and give them default values of TRUE or FALSE.  This way, other developers can call your procedure and leave some or all of the Boolean parameters blank, thereby accepting the default behavior of these “flags”.

Any parameters that have default values should be placed at the end of the parameter list, where practical.  This allows calling programs to simply omit all arguments that have default values, rather that having to use named notation.

Loops

When using loops, try to keep the beginning and end of the loop close together.  If you can’t get them on the same page when browsing your program, then you need to perform additional modularization.  This is especially true if you are using nested loops.

Loops should have only one way in and one way out.  Try to avoid using multiple exit points.  This can lead to some very confusing code.

Use loop labels when dealing with nested loops.  Also use loop labels when you cannot avoid placing more than about one page of code between the beginning and the end of the loop.  Place the beginning loop label on a line directly above and left-aligned with the LOOP statement.  Place the ending loop label on the same line as the END LOOP statement.  See the examples below:

Nested loops without loop labels:

LOOP

  Some statements

  EXIT WHEN termination_condition1;

  LOOP

    Some more statements that cause page breaks

    EXIT WHEN termination_condition2;

  END LOOP;

END LOOP;

Same code using loop labels:

<<customers_loop>>

LOOP

  Some statements that cause page breaks

  EXIT customers_loop WHEN termination_condition1;

  <<orders_loop>>

  LOOP

    Some more statements that cause page breaks

    EXIT orders_loop WHEN termination_condition2;

  END LOOP orders_loop;

END LOOP customers_loop;

For Loops

For loops have this syntax:

FOR l_iterations IN 1..l_max_iterations LOOP
…
END LOOP;

A for loop should only be used when the loop needs to execute a specific number of times no matter what.  Don’t exit the loop until it has been executed the specified number of times.  Don’t change the value of the loop index to get out of the loop early.

While Loops

While loops have this syntax:

WHILE l_boolean_condition LOOP
…
END LOOP;

While loops should only be used when the loop needs to execute until a specific condition (specified in the WHILE clause) is met.  Don’t exit the loop until this condition is met.

While loops are a better choice than For loops if you need to loop until something happens. You can simply set the Boolean condition to TRUE when you are ready to exit the loop.  Don’t use While loops instead of For loops when you will always want to execute the loop a certain number of times:

l_value := 1;
WHILE l_value <= 10 LOOP
…
END LOOP;

If your While loop looks like this, you should use a For loop instead.

Simple Loops

Despite the name, Simple loops are best used for more complicated conditional exits.  The EXIT and EXIT WHEN statements should only be used within simple loops.  Use a simple loop if you need to exit the loop somewhere other than right at the beginning.  If you only need to exit the loop at the beginning, consider a While loop.

Cursors

Do not use implicit cursors.  Always use explicit cursors instead.

An explicit cursor is one that is defined in the declaration section of a block.  An implicit cursor is what Oracle uses to perform a fetch when your code performs a SELECT … INTO … operation.  Always use explicit cursors instead of implicit.  Explicit cursors are more efficient because the developer manually controls the number of fetches.  An implicit fetch will attempt one more fetch than is actually needed to make sure no more rows are found that match the criteria.

Implicit fetch example:

PROCEDURE get_customer_name (

   i_customer_no      IN     oecustomer.customer_no%TYPE,

   o_customer_fname      OUT oecustomer.first_name%TYPE,

   o_customer_lname      OUT oecustomer.last_name%TYPE)

IS

BEGIN

   SELECT first_name, last_name

     INTO o_customer_fname, o_customer_lname

     FROM oecustomer

    WHERE customer_no = i_customer_no;

END;

Same code converted TO explicit FETCH:

PROCEDURE get_customer_name (

   i_customer_no      IN     oecustomer.customer_no%TYPE,

   o_customer_fname      OUT oecustomer.first_name%TYPE,

   o_customer_lname      OUT oecustomer.last_name%TYPE)

IS

   CURSOR l_name_cur

   IS

      SELECT first_name, last_name

        FROM oecustomer

       WHERE customer_no = i_customer_no;

   l_name_rec   l_name_cur%ROWTYPE;

BEGIN

   OPEN l_name_cur;

   FETCH l_name_cur INTO l_name_rec;

   CLOSE l_name_cur;

   o_customer_fname := l_name_rec.first_name;

   o_customer_lname := l_name_rec.last_name;

END;

The explicit fetch requires slightly more code and is not quite as easy to read at first glance, but it is more efficient and affords the developer more control.  Exception handling is easier: a TOO_MANY_ROWS exception will not be raised when manually fetching from an explicit cursor.  This allows you to define what will happen in these cases in your code, instead of relying on an exception handler to trap the exceptions.  This gives the developer more flexibility in code structure.

When fetching from cursors, you should normally fetch values into a Record variable.  This makes it easier to change the columns in the SELECT list of the cursor later.  It is not necessary to fetch into a Record variable if you are performing a “SELECT 1” operation to avoid a COUNT query or a similar operation.

IF Statements

Align IF statements with the corresponding ELSIF, ELSE, and END IF statements.  Indent code inside this structure.

Using IF…ELSIF…ELSE…END IF is PL/SQL’s version of a CASE statement.  If you use an ELSIF clause in your IF statement, you should always include an ELSE clause.  This will trap any other case that your logic might have missed.  Even if you think it isn’t possible for another condition to exist, it is good coding practice to always catch every condition using a ELSE clause.

An example of this is an IF statement that examines a Boolean variable:

IF l_boolean THEN

  do_something;

ELSIF NOT l_boolean THEN

  do_something_else;

END IF;

This code works for both TRUE and FALSE values, but what about a NULL value?  This is better (assuming that NULL values should not be treated the same as either TRUE of FALSE):

IF l_boolean THEN

  do_something;

ELSIF NOT l_boolean THEN

  do_something_else;

ELSE

  raise_an_error;

END IF;

Exception Handlers

Exception handlers should be used only for capturing errors.  Keep in mind that once control branches to an exception handler, control will not return to the block that generated the exception.

Left-align the EXCEPTION keyword with the BEGIN and END of the block containing the exception handler.  Indent all other lines within the handler.  Example exception block:

BEGIN

(various DML statements)

EXCEPTION

  WHEN e_user_defined_problem THEN

    ROLLBACK;

    write_to_error_log;

  WHEN OTHERS THEN

    ROLLBACK;

    RAISE;

END;

Be very careful of transaction management when using exception handlers.  Consider what DML might need to be rolled back in case of a problem.  You should always include an exception handler with transaction management when your program involves DML statements.

Every program needs a top-level exception handler to make sure that unexpected errors are trapped and handled in some way.  Consider how your program will be used to determine how to structure your exception handler.

Do not use a WHEN OTHERS exception handler by itself unless that is what is really needed.  Normally, your program will need to take specific actions depending on what type of problem was encountered.  The WHEN OTHERS trap is designed to handle exceptions that were not trapped by other traps.

Exception handlers in functions should always return a value if program execution should continue after the exception.

In general, do not use exception handlers to trap events that are expected to occur.  Use conditional logic instead.  (there are certain cases where it makes more sense to use an exception handler for performance reasons, etc.)

Performance and Reusability Standards

Client-Side SQL

Avoid the use of SQL statements stored in local program units or triggers on the client side.  Client-side SQL reduces performance and code reusability. 

Exceptions to this rule include:

·         Record group queries.

·         Implicit SQL performed by Oracle Forms in conjunction with base table blocks.

·         Situations requiring the form to process records fetched from a cursor individually at the form level (for example, manual block or record group population).

·         Fetching the next value from a database sequence (or other fetches from the DUAL table);

From Developer/2000 Release 2 and onwards developers can take advantage of new features in the tool to avoid using client-side SQL even in many of the cases mentioned in the exceptions above.

Always place server-side stored functions and procedures in a package.  This allows maximum code reuse and allows the DBA the flexibility to "pin" your package in a shared memory area on the database server to allow maximum performance.

The program administrator should be responsible for managing shared packages.  If you need a procedure or function, make sure it does not already exist in a package somewhere before you take the time to write it.

Modularity

The best way to achieve a minimum of code duplication is to promote modularity by limiting procedures and functions to a single task.  If your function or procedure does more than one thing, consider breaking it up into multiple program units so that other code may be able to use it.  A general rule of thumb is that if a procedure or function is more than 3 or 4 pages in length, it should be reviewed for ways to modularize the code.

If you have more that a page of code between an IF…END IF block, your code will be difficult to interpret.  Nobody wants to manage code with giant IF statement blocks.  Avoid IF and LOOP structures with more that a page of code in between.  If you need to do more than this, simply place all of your logic in a separate procedure or function and call it from within the IF or LOOP structure.

If you keep your program units small and name your procedures and functions using meaningful names, it should be fairly simple for someone unfamiliar with your code to read your procedure and describe (at a high level) what you are doing.

Of course, it is possible to get too much of a good thing.  Over-modular programs are simply harder to figure out.  Avoid modularizing program units that will only be called from one place, unless they make a significant difference in the readability of your program.  In general, if you are not sure if it makes sense to make a procedure modular, go ahead and do it.  It’s better to err on the side of caution.  You may decide at some point in the future to call this procedure from another place, in which case you will be glad you made the choice to go modular.

Hard-coding

Never reference a literal value in your code.  There are many alternatives to literal values in code, including constants, public or private package variables, procedure or function parameters, and table-based “constants”.  If you think you have no choice but to reference a literal value, think again.

Note that it is perfectly acceptable to reference a literal value as long as you do it indirectly.  For example, say you want to set a column value equal to the word “CLOSED”.  Set up a constants table that has two columns: constant code and constant value.  Set the code equal to the value “CLOSED_STATUS” and set the value equal to “CLOSED”.  Then instead of referencing the value directly, look it up in the constants table by looking up the corresponding value to the constant code “CLOSED_STATUS”.  In this way, if the name of the closed status changes from “CLOSED” to “COMPLETE”, you will more likely be able to adjust to this change without any changes to your code.

Constants tables are useful in many situations like this.  A typical constants table includes a const_code column and one column each for number, string, and date values, in addition to a comments column.  You can write a generic package that contains overloaded functions to retrieve constant values of all three types given the constant code.  The package should use PL/SQL tables to buffer previously looked up values to improve performance, and should of course include a “reset” feature that allows the buffers to be flushed for those cases where you absolutely, positively have to have the most current value.  By preparing this package and table in advance, you can encourage everyone on the development team to avoid hard-coding by making the right choice more convenient for them.

Hard coded items are sometimes more subtle than literal values.  For example, if you have a formula that appears in your code in more than one place, this is a subtle form of hard-coding.  For example, if you determine the weight of a widget by multiplying the weight per foot times the length, this logic should be encapsulated in a function in a package.  Every place in the application that requires this calculation should then reference this function.  In this way, if the logical formula should ever change, the change only needs to be made in one place.

Unnecessary Group Functions

Group functions are costly when a large number of rows are involved.  Performance problems can be created when group functions are used in situations that don't really require the data to be grouped.

The most obvious example of this problem is the COUNT function.  There is a simple rule that can be remembered to avoid performance problems related to this: Don't use COUNT unless you need to know the exact number of rows that match your criteria.  Never use COUNT if you only need to know if a single row exists, or even if you need to know if multiple rows exist.  Use an explicit cursor and fetch the number of records from it that satisfies your requirement.

Here is an example of an unnecessary COUNT query.  Assume that we only need to determine if zero, one, or many rows exist so that we know whether to raise an error (zero), use the data found (one), or display a list to allow the user to select the correct record (many):

DECLARE

   l_name    oecustomer.name%TYPE;

   l_count   NUMBER := 0;

BEGIN

   l_name := 'FRANK';

   SELECT NVL (COUNT (1), 0)

     INTO l_count

     FROM oecustomer

    WHERE name = l_name;

   IF l_count = 0

   THEN

      show_error;

   ELSIF l_count = 1

   THEN

      use_data;

   ELSE

      display_list;

   END IF;

END;                     

--This PROCEDURE could be better written AS FOLLOWS:

DECLARE

   CURSOR l_name_cur

   IS

      SELECT 1

        FROM oecustomer

       WHERE name = l_name;

   l_name    oecustomer.name%TYPE;

   l_found   NUMBER := 0;

BEGIN

   l_name := ‘FRANK’;

   OPEN l_name_cur;

   LOOP

      FETCH l_name_cur INTO l_found;

      EXIT WHEN l_name_cur%NOTFOUND OR l_name_cur%ROWCOUNT > 1;

   END LOOP;

   IF l_name_cur%ROWCOUNT = 0

   THEN

      CLOSE l_name_cur;

      show_error;

   ELSIF l_name_cur%ROWCOUNT = 1

   THEN

      CLOSE l_name_cur;

      use_data;

   ELSE

      CLOSE l_name_cur;

      display_list;

   END IF;

END;

Procedure and Function Design

Transaction Handling

Be sure to consider the impact of an incomplete transaction in your code.  For example, if your procedure does an insert into the customer table and then an insert into the customer addresses table, what should happen if the second insert fails?  Should the first insert stand, or should it be rolled back?  When considering this, think about how the user will recover in case of failure.  Will they simply try it again?  Will they assume it worked?  How will they know?

Group your DML statements into logical transactions, using savepoints, rollbacks, and commits to control when DML statements get done and when they don’t.  Keep in mind that exceptions can be raised due to events far outside the control of the developer.  For example, a data file could go offline or a rollback segment can be full or locked.  Your exception handlers can perform many clean-up duties in case of failure.

Local Private Procedures

You can create a procedure or function inside another procedure or function.  The inner(private) procedure can only be used by the outer procedure.  This technique can be useful to make your outer procedure more readable.  Take care not to overdo it, however.  Too many (or too complicated) inner procedures can make your code even harder to figure out.

Variable Declarations

Use comments to describe the purpose of variables when it is not obvious from the variable name.  Anchor the datatypes of variables whenever possible.  Avoid the use of the CHAR datatype; use VARCHAR2 instead.  CHAR datatypes are blank-padded to the specified length; this is rarely beneficial to the efficiency of your code.

Use variables with a Boolean datatype when you need a flag.  This results in code that is more efficient and easier to read than code that uses a number or character field for this purpose.

Parameter Validation

If your code only works if the parameters passed are the length or format your code expects, you need to perform some parameter validation.  Common parameter validations such as verifying the length of character parameters and making sure a number is a positive integer will go a long way toward making your code bulletproof.

Test your procedures by passing in bizarre and unexpected parameters.  Your code should either work as expected or error out gracefully no matter what you pass in as parameters.

Oracle REF Cursor Example

CREATE OR REPLACE PROCEDURE get_emp_rs (p_deptno      IN     emp.deptno%TYPE,

                                        p_recordset      OUT SYS_REFCURSOR)

AS

BEGIN

   OPEN p_ recordset FOR

        SELECT ename, empno, deptno

          FROM emp

         WHERE deptno = p_deptno

      ORDER BY ename;

END get_emp_rs;

-- The resulting cursor can be referenced from PL/SQL as follows.

DECLARE

   l_cursor   SYS_REFCURSOR;

   l_ename    emp.ename%TYPE;

   l_empno    emp.empno%TYPE;

   l_deptno   emp.deptno%TYPE;

BEGIN

   GET_EMP_RS (p_deptno => 30, p_recordset => l_cursor);

   LOOP

      FETCH l_cursor INTO l_ename, l_empno, l_deptno;

      EXIT WHEN l_cursor%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE (l_ename || ' | ' || l_empno || ' | ' || l_deptno);

   END LOOP;

   CLOSE l_cursor;

END;