General PL/SQL Coding Standards

Always Use Packages

PL/SQL procedures should always be defined within packages. Create a package for
 each block of a form, or other logical grouping of code.

Package Sizes

A client-side (Oracle Forms) PL/SQL program unit's source code and compiled code
together must be less than 64K. (A program unit is a package specification or body or
stand-alone procedure.) This implies that the source code for a program unit cannot
exceed 10K.
If a package exceeds the 10K limit, you can reduce the size of the package by putting
private variables and procedures in one or more "private packages." By standard, only
the original package should access variables and procedures in a private package. If an
individual procedure exceeds the size limit, you should separate the code into two or
more procedures.

When an Oracle Forms PL/SQL procedure exceeds the 64K limit, Oracle Forms raises an
error at generate time.
Server-side packages and procedures do not have a size limit, but when Oracle Forms
refers to a server-side package or procedure, it creates a local stub, which does have a
size limit. The size of a package stub depends on the number of procedures in the
package and the number and types of arguments each procedure has. Keep the number
of procedures in a package less than 25 to avoid exceeding the 10K limit.

PL/SQL Packages, Procedures and Source Files

Note that PL/SQL packages and procedures are documented slightly differently:
Packages do not have Arguments sections and procedures do not need to have History
sections.

Begin all SQL and PL/SQL files with the following lines (after the copyright header):
SET VERIFY OFF
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;

End all SQL and PL/SQL files with the following lines:
COMMIT;
EXIT;

This section contains recommendations for formatting PL/SQL code.

• Within a package, define private variables first, then private procedures, and finally
  public procedures.
• Always end procedures and packages by following the "end" statement with the
  procedure or package name to help delineate procedures.
• Indent code logically. Using increments of two spaces provides an easy way to track
  your nested cases.
• Indent SQL statements as follows:

   DECLARE 	     
     CURSOR employees IS          
       SELECT empno           
         FROM emp           
         WHERE deptno = 10        	     
           AND ename IN ('WASHINGTON', 'MONROE')                 
           AND mgr = 2701;

• Use "- -" to start comments so that you can easily comment out large portions of 
  code during debugging with "/* ... */".
• Indent comments to align with the code being commented.
• When commenting out code, start the comment delimiter in the leftmost column
• Use uppercase and lowercase to improve the readability of your code (PL/SQL is
     case-insensitive).  As a guideline, use uppercase for reserved words and lowercase
     for everything else.
• Avoid deeply nested IF-THEN-ELSE condition control. Use IF-THEN-ELSIF

IF ... THEN ... ELSE    	         
  IF ... THEN ... ELSE    	           
    IF ... THEN ... ELSE    	           
    END IF    	       
  END IF    	 
END IF; 

IF ... THEN ...
ELSIF ... THEN ...
ELSIF ... THEN ...
ELSIF ... THEN ...
ELSE ...
END IF;


   • Only create nested PL/SQL blocks (BEGIN/END pairs) within a procedure when
     there is specific exception handling you need to trap.
     When the code is clearly no longer needed, remove it entirely.

IF (error_condition) THEN
fnd_message.set_name(appl_short_name,
message_name);
APP_EXCEPTION.RAISE_EXCEPTION;
END IF;

  Follow these guidelines for all SQL that you code:

   
	• Use "select from DUAL" instead of "select from SYS.DUAL". Do not use
   SYSTEM.DUAL.
   Using PL/SQL in Oracle Applications 4-9


   
	• All SELECT statements should use an explicit cursor. Implicit SELECT statements
   actually cause 2 fetches to execute: one to get the data, and one to check for the
   TOO_MANY_ROWS exception. You can avoid this by FETCHing just a single
   record from an explicit cursor.
   
	• If you want to SELECT into a procedure parameter, declare the parameter as IN
   OUT, whether or not you reference the parameter value, unless the parameter is a
   field.
   
	• A single-row SELECT that returns no rows raises the exception
   NO_DATA_FOUND. An INSERT, UPDATE, or DELETE that affects no rows does
   not raise an exception. You need to explicitly check the value of SQL%NOTFOUND
   if no rows is an error.
   
	• To handle NO_DATA_FOUND exceptions, write an exception handler. Do not code
   COUNT statements to detect the existence of rows unless that is your only concern.
   
	• When checking the value of a field or PL/SQL variable against a literal, do the check
   in PL/SQL code, not in a WHERE clause. You may be able to avoid doing the SQL
   altogether.
   
	• Do not check for errors due to database integrity problems. For example, if a correct
   database would have a table SYS.DUAL with exactly one row in it, you do not need
   to check if SYS.DUAL has zero or more than one row or if SYS.DUAL exists. 

Naming Standards 32-1

In addition to specific naming conventions for particular objects, all database objects should be named without using any special characters. Database object names should use only letters, numbers, and underscores, and they should always begin with a letter.
Note that database object names are case-insensitive, so "Name" would be the same as "NAME".

Include header information when you create your objects.

The header should include the following documentation:
• Name
• Purpose
• Arguments
Arg1 Describe arg1
Arg2 Describe arg2
• Notes
1. Special usage notes
2. Special usage notes
• History
DD-MON-YY J. Doe Created

32-4 Oracle Applications Developer's Guide

Packaged Procedures

Standard verb_noun

verb_noun is a brief explanation of the purpose. Do not reuse the product short name or any part of the package name in the procedure name. Remember that you will invoke the procedure as package procedure. For example, if the package name is APP_ORDER_BY, then the procedures should simply be named APPEND and REVERT. Be careful you don't name your package procedure a SQL, PL/SQL, Oracle Forms, or other reserved word, or redefine an Oracle Forms built-in.
Example
CALCULATE_PRICE_VARIANCE, TERMINATE_EMPLOYEE

   Package table_PKG
   table is the name of the table on which the package acts (to insert, update, delete, or lock
   a record, or to check if a record in another table references a record in this table). The
   package name should be 24 characters or less.




   Example
   PO_LINES_PKG





   Private Packages
   Standard package_PRIVATE
   package is the name of the owning package.
   Example
   APP_ITEM_PROPERTY_PRIVATE

Primary Key table_PK
Unique table_Ui
Foreign Key table_Fi
Check Use Message Dictionary message naming standards.
See: Overview of Message Dictionary, page 12-1

table is the name of the table on which the constraint is created, while i is a unique id starting at 1. You should name all of your constraints so that you can enable or disable them easily.

Packages

   Standard prod_module or prod_description
   prod is the product short name, module is a functional module, and description is a one or
   two word explanation of the purpose. Stored package names should be 24 characters or
   less. For library packages, your package should be unique within 27 characters.
   Wrapper packages use a three character prefix. Select a description that helps simplify
   the names of procedures in the package.
   Example
   OE_SCHEDULE, AOL_FLEXFIELD

Standard variable or X_variable
variable should be a logical, meaningful, and concise name. Precede variable name with
X when the variable is used in a SQL statement, so that there is no possibility of
conflicts with database object names or confusion as to which are PL/SQL variables and
which are database objects.
Example
X_header_id

PL/SQL Global Variables
Standard G_variable
variable should be a logical, meaningful, and concise name. Precede variable name with
G to distinguish global variables from local variables.
Example
G_set_of_books_id

   All file names must be no greater than 8 chars in length plus a three character extension
   (FILENAME.ext). The files names must be all caps except for the extension. This will
   provide a consistent naming scheme across all platforms.

  
   Standard pppgxxxs.pls or pppggxxb.pls

   ppp is the two or three character product short name, g is a one-character abbreviation
   for the functional group, and xxx is a three-character abbreviation for the explanation of
   the purpose. If you do not need three characters for that purpose, you may use two
   characters for the functional group. s indicate a specification file, and b indicates a body
   file. Each file defines only one package specification or body. The files reside in
   $prod/install/sql (or the platform equivalent).
   Table Handler Package Source File Names
   Standard pppgixxs.pls and pppgixxb.pls

   In addition to all words reserved by the Oracle database, PL/SQL, and Oracle Forms, do
   not use any words that begin with the following letters (including the words
   themselves):
   • FOLDER
   • CALENDAR
   • APPCORE