Effective Codying Style
Oracle Naming Coding Standards
Oracle architectural design tips

Effective Coding Style

Revealing Logical Structure with Indentation
Indentation is one of the most common and effective techniques used to display a program's logic via format. As illustrated in the following examples, programs that are indented are easier to read than those that are not indented. Here is an unindented IF statement:

IF to_number(the_value) > 22 THEN
IF max_totals = 0 THEN
calc_totals;
ELSE WHILE more_data
LOOP
analyze_results;
END LOOP;
END IF;
END IF;

I have found that a three (or four)-space indentation not only adequately reveals the logical structure of the code but also keeps the statements close enough together to read comfortably. And, with deeply nested structures, you won't run off the right margin as quickly! Here is the three-space indented version of the previous nested IF statement:

IF to_number(the_value) > 22 THEN
   IF max_totals = 0  THEN
      calc_totals;
   ELSE
      WHILE more_data
      LOOP
         analyze_results;
      END LOOP;
   END IF;
END IF;


Using Case to Aid Readability
PL/SQL code is made up of many different components: variables, form items, report fields, procedures, functions, loops, declarations, control elements, etc. But they break down roughly into two types of text: reserved words and application-specific names or identifiers.Reserved words are those names of language elements that are reserved by PL/SQL and have a special meaning for the compiler. Some examples of reserved words in PL/SQL are:

WHILE
IF
BEGIN
TO_CHAR

Application-specific identifiers are the names that you give to data and program structures that are specific to your application and that vary from system to system. The compiler treats these two kinds of text very differently. You can improve the readability of your code greatly by reflecting this difference in the way the text is displayed. Many developers make no distinction between reserved words and application-specific identifiers. Consider the following lines of code:

if to_number(the_value)>22 and num1 between lval and hval then
   newval := 100;
elsif to_number(the_value) < 1 then
   calc_tots(to_date('12-jan-95'));
else
   clear_vals;
end if;

While the use of indentation makes it easier to follow the logical flow of the IF statement, all the words in the statements tend to blend together. It is difficult to separate the reserved words and the application identifiers in this code. Changing entirely to uppercase also will not improve matters. Indiscriminate, albeit consistent, use of upper- or lowercase for your code reduces its readability. The distinction between reserved words and application-specific identifiers is ignored in the formatting. This translates into a loss of information and comprehension for a developer.
 

·  The UPPER-lower Style
You can easily solve this problem by adopting a guideline for using a mix of upper- and lowercase to your code. I have recoded my previous example below, this time using the UPPER-lower style: all reserved words are written in UPPERCASE and all application names are kept in lowercase:

IF to_number(the_value) > 22 AND num1 BETWEEN lval AND hval THEN
   newval := 100;
ELSIF TO_NUMBER (the_value) < 1 THEN
   calc_tots (TO_DATE ('12-jan-95'));
ELSE
   clear_vals;
END IF;

Using a mixture of upper- and lowercase words increases the readability of the code by giving a sense of dimension to the code. The eye can more easily cruise over the text and pick the different syntactical elements of each statement. You can focus quickly on the lowercase words for the application-specific content. Consistent use of this method makes the program listings more attractive and accessible at a glance.
 

Formatting Single Statements
Most of your code consists of individual statements, such as assignments, calls to modules, and declarations. A consistent approach to formatting and grouping such statements will improve the readability of your program as a whole. This section suggests some guidelines. 


·  Use at most one statement per line
PL/SQL uses the semicolon (;) as the logical terminator for a statement, as a result you can have more than one statement on a line and you can continue a single executable statement over more than one line. You will sometimes be tempted to place several statements on a single line, particularly if they are very simple. Consider the following line:
    new_id := 15; calc_total (new_id); max_dollars := 105 * sales_adj;

It is very difficult to pick out the individual statements in this line, in addition to the fact that a procedure is called in the middle of the line. By placing each statement on its own line you mirror the complexity of a program--the simple lines look simple and the complex statements look complex--and reinforce the top-to-bottom logic of the program:
    new_id := 15;
    calc_total (new_id);
    max_dollars := 105 * sales_adj;

·  Use whitespace inside a statement
You can use all the indentation and blank lines you want to reveal the logic of a program and still end up with some very dense and unreadable code. It is also important to employ whitespace within a single line to make that one statement more comprehensible. Always include a space between every identifier and separator in a statement. Instead of this:
    WHILE(total_sales<maximum_sales AND company_type='NEW')LOOP
write this:
    WHILE (total_sales < maximum_sales AND company_type = 'NEW') LOOP
 
 

·  Use indentation to offset all continuation lines under the first line.
This is the most important guideline. The best way to identify continuation lines is to use indentation to logically subsume those lines under the main or first line of the statement. The following call to generate_company_statistics is obscured because the continuation line butts right up against the left margin with the module name:

generate_company_statistics (company_id, last_year_date,
rollup_type, total, average, variance, budgeted, next_year_plan);

If I indent the continuation line, the relationship of the second line to the first becomes clear:

generate_company_statistics (company_id, last_year_date,
   rollup_type, total, average, variance, budgeted, next_year_plan);
 

Formatting SQL Statements
Because PL/SQL is an extension to the SQL language, you can place SQL statements directly in your PL/SQL programs. You can also define cursors based on SELECT statements. This section summarizes my suggestions for formatting SQL statements and cursors for maximum readability.PL/SQL supports the use of four SQL DML (Data Manipulation Language) statements: INSERT, UPDATE, DELETE, and SELECT. Each of these statements is composed of a series of "clauses," as in the WHERE clause and the ORDER BY clause. SQL statements can be very complex, to say the least. Without a consistent approach to indentation and alignment inside these statements, you can end up with a real mess. I have found the following guidelines useful:

  • Right-align the reserved words for the clauses against the DML statement.

I recommend that you visually separate the SQL reserved words which identify the separate clauses from the application-specific column and
table names. The following table shows how I use right-alignment on the reserved words to create a vertical border between them and the rest of
the SQL statement:

 

SELECT

INSERT

UPDATE

DELETE

 
SELECT
  FROM
  WHERE
    AND
     OR
  GROUP BY
  HAVING
     AND
      OR
  ORDER BY
 
INSERT INTO
     VALUES
 
INSERT INTO
   SELECT
     FROM
     WHERE
 
UPDATE
   SET
   WHERE
 
DELETE
  FROM
  WHERE

Here are some examples of this format in use:
SELECT last_name, first_name
   FROM employee
   WHERE department_id = 15
     AND hire_date < SYSDATE;

SELECT department_id, SUM (salary) AS total_salary
   FROM employee
   GROUP BY department_id
   ORDER BY total_salary DESC;

INSERT INTO employee (employee_id, ... )
   VALUES (105 ... );

UPDATE employee
   SET hire_date = SYSDATE
   WHERE hire_date IS NULL
     AND termination_date IS NULL;

This right alignment makes it very easy to identify the different clauses of the SQL statement, particularly with extended SELECTs. You might also consider placing a blank line between clauses of longer SQL statements (this is possible in PL/SQL, but is not acceptable in "native" SQL executed in SQL*Plus).

  • Use meaningful abbreviations for table and column aliases

It's impossible to read a program when a query has a six-table join and the tables have been assigned aliases A, B, C, D, E, and F. How can you possibly decipher the WHERE clause in the following SELECT?
SELECT ... select list ...
     FROM employee A, company B, history C, bonus D, profile E, sales F
     WHERE A.company_id = B.company_id
         AND A.employee_id = C.employee_id
         AND B.company_id = F.company_id
         AND A.employee_id = D.employee_id
         AND B.company_id = E.company_id;

With more sensible table aliases (including no tables aliases at all where the table name was short enough already), the relationships are much clearer:
     SELECT ... select list ...
        FROM employee EMP, company CO, history HIST, bonus, profile PROF, sales
        WHERE EMP.company_id = CO.company_id
          AND EMP.employee_id = HIST.employee_id
          AND CO.company_id = SALES.company_id
          AND EMP.employee_id = BONUS.employee_id
          AND CO.company_id = PROF.company_id;

 
Formatting Exception Handlers
PL/SQL provides a very powerful facility for dealing with errors. An entirely separate exception section contains one or more "handlers" to trap exceptions and execute code when that exception occurs. Logically, the exception section is structured like a conditional CASE statement (which, by the way, is not supported by PL/SQL). As you might expect, the format for the exception section should resemble that of an IF statement. Here is a general example of the exception section:

EXCEPTION
   WHEN NO_DATA_FOUND THEN
      executable_statements1;

   WHEN DUP_VAL_ON_INDEX THEN
      executable_statements1;
...
   WHEN OTHERS THEN
      otherwise_code;
END;
 

Formatting PL/SQL Blocks
The PL/SQL block structure forms the backbone of your code. A consistent formatting style for the block, therefore, is critical. This formatting should make clear these different sections. Consider the following function:

FUNCTION
company_name (company_id_in IN company.company_id%TYPE)    RETURN
VARCHAR2 IS cname company.company_id%TYPE; BEGIN
   SELECT name INTO cname FROM company
    WHERE company_id = company_id_in;
   RETURN cname;
EXCEPTION WHEN NO_DATA_FOUND THEN  
   RETURN NULL;
END;

You know that this program is a function because the first word in the program is FUNCTION. Other than that, however, it is very difficult to follow the structure of this program. Where is the declaration section? Where does the executable section begin and end? Here is that same function after we apply some straightforward formatting rules to it:

FUNCTION company_name (company_id_in IN company.company_id%TYPE)
RETURN VARCHAR2
IS
   cname company.company_id%TYPE;
BEGIN
   SELECT name INTO cname FROM company
      WHERE company_id = company_id_in;
   RETURN cname;

EXCEPTION
   WHEN NO_DATA_FOUND THEN
      RETURN NULL;
END;

The declaration section, which comes after the IS and before the BEGIN, clearly consists of a single declaration of the cname variable. The executable section consists of all the statements after the BEGIN and before the EXCEPTION statement; these are indented in from the BEGIN. Finally, the exception section shows a single specific exception handler and a WHEN OTHERS exception. Generally, indent the statements for a given section from the reserved words which initiate the section. You can also include a blank line before each section, as I do above, for the executable section (before BEGIN) and the exception section (before EXCEPTION). I usually place the IS keyword on its own line to clearly differentiate between the header of a module and its declaration section.
 

Make Comments Easy to Enter and Maintain
You shouldn't spend a lot of time formatting your comments. You need to develop a style that is clean and easy to read, but also easy to maintain. When you have to change a comment, you shouldn't have to reformat every line in the comment. Lots of fancy formatting is a good indication that you have a high-maintenance documentation style. The following block comment is a maintenance nightmare:

/*
===========================================================
| Parameter          Description                          |
|                                                         |
| company_id         The primary key to company           |
| start_date         Start date used for date range       |
| end_date           End date for date range              |
===========================================================
*/

The right-justified vertical lines and column formatting for the parameters require way too much effort to enter and maintain. What happens if you add a
parameter with a very long name? What if you need to write a longer description? A simpler and more maintainable version of this comment might be:

/*
===========================================================
| Parameter - Description
|
| company_id - The primary key to company
| start_date - Start date used for date range
| end_date - End date for date range
===========================================================
*/
 
 

Maintain Indentation
Inline commentary should reinforce the indentation and therefore the logical structure of the program. For example, it is very easy to find the comments in the make_array procedures shown below.

PROCEDURE make_array (num_rows_in IN INTEGER)
/* Create an array of specified numbers of rows */
IS
   /* Handles to Oracle Forms structures */
   col_id GROUPCOLUMN;
   rg_id RECORDGROUP;
BEGIN
   /* Create new record group and column */
   rg_id := CREATE_GROUP ('array');
   col_id := ADD_GROUP_COLUMN ('col');
   /*
   || Use a loop to create the specified number of rows and
   || set the value in each cell.
   */
   FOR row_index IN 1 .. num_rows_in
   LOOP
      /* Create a row at the end of the group to accept data */
      ADD_GROUP_ROW (return_value, END_OF_GROUP);
      FOR col_index IN 1 .. num_columns_in
      LOOP
         /* Set the initial value in the cell */
         SET_GROUP_NUMBER_CELL (col_id, row_index, 0);
     END LOOP;
   END LOOP;
END;

Documenting the Entire Package
A package is often a complicated and long construct. It is composed of many different types of objects, any of which may be public (visible to programs and users outside of the package) or private (available only to other objects in the package). You can use some very simple documentation guidelines to clarify the structure of the package. As usual when discussing packages, one must consider the specification separately from the body. As a meta-module or grouping of modules, the specification should have a standard header. This header needn't be as complicated as that of a specific module, because you do not want to repeat in the package header any information which also belongs in specific modules. I suggest using the template header shown in the following example. In the "Major Modifications" section of the header, do not include every change made to every object in the package. Instead note significant changes to the package as a whole, such as an expansion of scope, a change in the way the package and global variables are managed, etc. Place this header after the package name and before the IS statement:

PACKAGE package_name
/*
|| Author:
||
|| Overview:
||
|| Major Modifications (when, who, what)
||
*/
IS
   ...
END package_name;

Document the Package Specification
The package specification is, in essence, a series of declaration statements. Some of those statements declare variables, while others declare modules. Follow the same recommendation in commenting a package as you do in commenting a module's declaration section: provide a comment for each declaration. In addition to the comments for a specific declaration, you may also find it useful to provide a banner before a group of related declarations to make that connection obvious to the reader. Surround the banner with whitespace (blank lines for the start/end of a multiline comment block). While you can use many different formats for this banner, use the simplest possible design that gets the point across. Everything else is clutter. The package specification below illustrates the header and declaration-level comment styles, as well as group banners:

PACKAGE rg_select
/*
|| Author: Diego Pafumi
||
|| Overview: Manage a list of selected items correlated with a
||    block on the screen.
||
|| Major Modifications (when, who, what)
||    12/94 - DP - Create package
||    3/95  - IS - Enhance to support coordinated blocks
||
*/
IS
   /*----------------- Modules to Define the List -------------------*/

   /* Initialize the list/record group. */
   PROCEDURE init_list (item_name_in IN VARCHAR2);

   /* Delete the list */
   PROCEDURE delete_list;

   /*------------------ Modules to Manage Item Selections -----------*/

   /* Mark item as selected */
   PROCEDURE select_item (row_in IN INTEGER);

   /* De-select the item from the list */
   PROCEDURE deselect_item (row_in IN INTEGER);

END rg_select;
 

Oracle Naming Coding Standards

Tables
Table names should reflect the data in the table.  The names are a mix of upper and lowercase letter with no underscores and in singular. The names should be fully spelled out and in singular or plural, but please keep only one rule: plural or singular.
Example: UserProfile.

Columns
Table columns should be named in the same was tables are named.
Examples: FormID, ScanDate, ZoneName.

Stored Procedures
Atlantis related stored procedures start with "ap_".
Project specific stored procedure should be prefixed by "p_".
Following the prefix should be an abbreviation for a verb, or action word.  Some typical actions on data are select (sel), insert (ins), update (upd) and delete (del).  If a combination of actions is possible in a stored procedure, then use the first letters of the action.  For example, if a stored procedure can do an insert or update, then use iu.  An underscore character should then follow the verb abbreviation.
Next, the name of the table affected or some other noun should be used to describe what is affected.  Each word in this part of the spec is a mix of upper and lowercase, where the first character is uppercase and the rest lowercase.  There should be no underscores in this part of the name.
Examples are listed below:
ap_ins_Form - This procedure name is an Atlantis stored procedure that inserts a record into the Form table.
ap_iu_Document - This procedure name is Atlantis stored procedure that inserts or updates a record into the Document table.
ap_sel_Zone - This procedure name is an Atlantis stored procedure that selects records from the Zonetable.
p_del_Account - This procedure is project specific procedure that deletes records from the Zone table.

Triggers
Atlantis triggers start with "at_" and project specific triggers start with "t_".
Following this prefix is an indicator describing the action that triggers the trigger.  Use del for delete, ins for insert and upd for update.  If a trigger is used for more than one of these operations, then just include the first letter.  For example, use iu for an insert/update trigger.  Next comes an underscore followed by the suffix.  The suffix contains an upper/lowercase name that includes the table name and/or any other descriptive text.
Examples: at_del_Document, t_iu_UserProfile.

Views
Atlantis views will have a prefix of "av_".  A project specific view should have a prefix of "v_" or "view_".
Following the prefix the view name should contain some sort of descriptive reference.  If the view contains a simple join of two tables, then include the table names.  For example: v_Table1Table2.
The suffix should be upper/lowercase.

Indexes
Index names should have an "in_" prefix.  The rest of the name is upper and lowercase.  This suffix contains some meaningful text about the nature of the index.
Example: in_EmployeeID.

Constraints
Primary keys are to be prefixed with "pk_", unique keys with "uk_" or "unique_" and foreign keys start with "fk_".
The remainder of the name is upper and lowercase and usually contains the name of the field(s) included in the key.
Example: pk_FormID, fk_ImageType.

Sequence
Sequence names begin with an "s_", followed by an underscore and then the field name (i.e. s_Field).  If field name is ambiguous, then precede the field name with table name s_TableField.


Oracle architectural design tips
Use RAM data caching
You must be aware that Oracle9i allows very large memory regions in order to cache frequently referenced row information. The caching of frequently referenced information should be a major design goal primarily because RAM access is two orders of magnitude (more than 10,000 times) faster than row access from disk. The larger the Oracle data block buffer cache, the faster the SQL queries will execute. The size of the RAM data buffers will have a direct impact on Oracle performance, and all systems run fastest when fully cached in the data buffers.

Buy fast processors
The CPU speed of the Oracle database server has a direct impact on performance. High-performance 64-bit CPUs will often perform 10 times faster than 32-bit processors. The 64-bit processors are available on all major platforms and include:

  • Windows—Intel Itanium processor
  • HP—PA-8000 processor
  • Solaris—500-MHz Ultrasparc-iie processor
  • IBM AIX—RS/6000 PowerPC processor


Use a 64-bit version of Oracle
It is highly recommended that Oracle systems exist on a dedicated database server with a 64-bit CPU architecture and a 64-bit version of Oracle. The 64-bit version of Oracle lets you create large SGA regions, and large projects commonly require more than 20 gigabytes of RAM data buffers. A serious shortcoming of 32-bit Oracle is the 1.7-gigabyte size limitation for the SGA.

Use large data blocks on indexes to minimise disk I/O
Oracle index access performs best with 16K and 32K Oracle block sizes. You should consult your application-specific OS documentation and create index tablespaces of the largest value for your environment.

Use Oracle Parallel Query
All table access should be tuned to avoid large-table full-table scans, but often, such scans are required. Make certain all full-table scans fully utilize Oracle Parallel Query to improve performance.

Choose proper SQL optimisation
The choice of optimiser mode is critical to Oracle SQL performance. In Oracle9i, approximately half of all queries run faster with rule-based optimisation; the other half run fastest with cost-based optimisation.

Always design for package pinning
All frequently referenced PL/SQL packages should be pinned into the shared pool by using the dbms_shared_pool.keep procedure. Doing so will greatly speed Oracle PL/SQL execution.