ICCM.MD.090b - Application Code PL/SQL
OMG = Oracle Method Guideline
OMS = Oracle Method Standard

General
OMS-61518: When a secondary access element or secondary access controlled element (SAC) is deleted, this should be recorded in the notes of the primary access element or Primary access controlled (PAC) element.
Rationale Analysts or developers might want to know what happened to the SAC. If this kind of information is not recorded, the person that can explain, might not be available on the project, if at all.
Example The deletion of an attribute is recorded in the notes of the entity.

OMS-61537: Document any deviations of the CDM Standards in the respective Notes, including the OMS Standard code and the reason.
Rationale Documenting these deviations helps your team members understand the decisions that have been made. This is also valuable information for anyone performing QA on the project.

Note: If you deviate from any OMS standards on a project level, do not include the deviation for every object. If there is only one Application System involved, then document these deviations in the Description property of the Application System. When there are more Application Systems involved, then it is recommended to document the deviations in a separate document.
PL/SQL Standards
OMS-52001: Do not interfere with the Oracle Forms transaction mechanisms from within a PL/SQL code segment using COMMIT, SAVEPOINT, or ROLLBACK. The only exception is POST, which you are allowed to use in combination with the Oracle Server with the transaction processing option.
Rationale If you interfere with Oracle Forms transaction mechanism, this may cause unexpected behavior.

OMS-52832: Indent your code. Each indent is defined as 3 blanks. Do not use tabs to create this indentation, use spaces instead. Indentation is started at the first line after a line that ends with:
- begin
- loop
- then
- exception

Un-indentation (return to previous level of indentation) should be applied at the first line after a line containing:
  • end
  • end loop
  • end if
Rationale The code is easier to read when the code is indented. You should not use tabs because the number of characters in a tab can differ across systems and word editors, causing the layout to be messed up when moving from one environment to another.

OMS-52836: When concatenating strings use the following layout:

l_string := 'abc_'
............||to_char(p_uni_id)
............||'_xyz';


Use the following layout in a call to a program unit:

convert_unit
( l_uni_id
, 'abc'
..||to_char(p_uni_id)
..||'_xyz'
);

Note: The dots indicate spaces
Rationale This makes the code easier to read, and it is less likely that any commas and brackets will be forgotten when writing the code.

OMS-52837: The general layout of a PL/SQL block is:

<<label>>
declare
---(...)
begin
---(...)
exception
---(...)
end;


Note: The three --- hyphens outside the brackets indicate the number of spaces required. The (...) indicate programming code.

The layout of a IF-THEN-ELSE is:

if (...)
then
---(...)
elsif (...)
then
---(...)
else
---(...)

end if; --if_label


Note: The section in italic is optional.

The layout of a LOOP is:

<<loop_label>>
loop
---(...)
end loop loop_label;


The layout of a FOR loop is:

<<for_loop_label>>
for i_counter in i_start..i_end loop
---(...)
end loop for_loop_label;


Layout of WHILE loops:

<<while_loop_label>>
while (...) loop
---(...)
end loop while_loop_label;
Rationale This makes the code easier to read.

OMS-52838: Do not use the mixed notation (positional and named parameters) in the parameter list to call functions and procedures.
Rationale Mixing notations is confusing. Therefore, keeping it consistent makes the code easier to read, and in addition less vulnerable to errors.

OMS-52839: Use the following layout for a CURSOR:

declare
...cursor...c_table_alias cursor
...(........b_var1...datatype1
...,........b_var2...datatype2
...)........is
...select...column1
...,........column2
...(...)
...from.....table
...where....condition1;


Note: The dots indicate the number of spaces required to layout the statement properly.
Rationale This makes the code easier to read.

OMS-52840: If you want to explicitly identify an if-then statement, use the following layout:

if (condition1)
then
...if condition2
...then
......(...)
...else
......(...)
...end if; --if_label cond2
else
...(...)
end if; --if_label cond1


The comment after the 'end if' identifies the statement, and should refer to the condition after the '''if'''. For example, a logical indication, the actual expression or the name of a variable used in the condition.
Note: The dots outside the brackets indicate the number of spaces required.
Rationale This makes it obvious to which if the end if belongs. This makes your code easier to read, especially when the if-then statement grows large.

OMS-52847: Use the following layout for an if statement, when more than one condition needs to be tested in the if clause:


if...condition1
and..(condition2
......or
......condition3
.....)
then


Note: The dots indicate the number of spaces required.
Rationale This makes the code easier to read.

OMS-60062: Prevent messages from being hard-coded into the application.
Rationale This offers you the flexibility to easily change message text online and implement multi-lingual messaging.

Inline Documentation
OMS-52100: Use the double hyphen-style comment markers: --. Only use the '/*' and '*/' comment markers in package specification headings.
Rationale When you have used double hyphens comment markers, you can easily use the '/*' and '*/' comment markers to temporarily comment out large pieces of code (for example, for debugging purposes).
Nested '/*' and '*/' comment markers do not work.
For package specification headings, this is less important as the comment is placed before the code starts.

OMS-52101: Use comments if a group of statements need clarification. Place them before the code segment commences.
The comment should give a short clarification on what is performed within the group of PL/SQL statements.
Rationale This makes it easier to understand and thereby maintain the code.

OMS-52102: If you want to give a short comment for a single line of PL/SQL code, then start the comment at the (end of the) same line as the line for which it is a comment.
If the comment lines become too long to put at the end of the same line, then instead use a new line above the statement with the same indent as the statement itself.
Rationale This makes it immediately obvious to which section the comment applies. The code is then easier to read.
Example if l_name = c_emp.ename -- this is a short comment
then
...-- this is a long comment that will not fit at the end
...l_deptno := c_emp.deptno
...(...)
end if;

Note: The dots indicate spaces.

OMS-52103: Remove all comment markers implemented for debugging purposes before you deliver a PL/SQL module.

Naming Conventions
OMS-52200: A PL/SQL variable, except for fields in record structures, should not have a name identical to a column name. Try to assign to each variable in a PL/SQL code segment a unique name. However, if necessary, use block names as a prefix to make names of variables local to the block unique. If the block is anonymous, then give it a block label for this purpose.
Rationale If you use the same name, it is possible to accidentally mix the column and variables within the same program unit, resulting in undesired behavior.

OMS-52201: Do not use reserved words or the word STANDARD as a block or loop label.
Rationale PL/SQL declares built-in functions globally in package STANDARD. Redefining it locally is error prone because your local declaration overrides the global declaration.

Attention: Oracle 8 reserved words are:
ACCESS ADD ALL ALTER
AND ANY AS ASC
AUDIT BETWEEN BY CHAR
CHECK CLUSTER COLUMN COMMENT
COMPRESS CONNECT CREATE CURRENT
DATE DECIMAL DEFAULT DELETE
DESC DISTINCT DROP ELSE
EXCLUSIVE EXISTS FILE FLOAT
FOR FROM GRANT GROUP
HAVING IDENTIFIED IMMEDIATE IN
INCREMENT INDEX INITIAL INSERT
INTEGER INTERSECT INTO IS
LEVEL LIKE LOCK LONG
MAXEXTENTS MINUS MODE MODIFY
NETWORK NOAUDIT NOCOMPRESS NOT
NOWAIT NULL NUMBER OF
OFFLINE ON ONLINE OPTION
OR ORDER PCTFREE PRIOR
PRIVILEGES PUBLIC RAW RENAME
RESOURCE TRIGGER REVOKE ROW
ROWID ROWNUM ROWS SELECT
SESSION SET SHARE SIZE
SMALLINT START SUCCESSFUL SYNONYM
SYSDATE TABLE THEN TO
UID UNION UNIQUE UPDATE
USER VALIDATE VALUES VARCHAR
VARCHAR2 VIEW WHENEVER WHERE

OMS-52210: Begin each cursor name with "c_", followed by the short name of the most important table in the SELECT statement. If a table short name is needed to use in more than one cursor-name in a PL/SQL code segment, add a logical name as a suffix to all cursors to make the cursor names unique.
Rationale This makes it easy to read the code. It is obvious that it concerns a cursor, and what table is involved in the select statement of the cursor. The logical suffix gives an indication on what kind of select the cursor is performing.

OMS-52211: The name of a cursor bind parameter should start with "b_".
Rationale This makes it easy to understand that it concerns a cursor bind parameter.

OMS-52212: Name the record structure after the cursor, replacing the "c_" prefix with an "r_" prefix.
Rationale This makes it easier to read the code. It is easy to see that the record contains the result of the belonging cursor.

OMS-52231: Name each temporary or local variable after the database column, prefixed with "l_". Assign temporary or local variables that do not have a direct relationship with a column a meaningful name, prefixed with "l_".
Rationale This makes it easy to identify which variables are local, and what kind of information they represent.

OMS-52240: A cursor loop is named after the cursor it controls, with the "c_" prefix replaced with a "r_" prefix.
Rationale This makes it easier to read the code because it is easy to recognize to which cursor the block is related.

OMS-52260: Do not declare any exception handler with the same name as a standard Oracle exception handler.
Rationale If you declare exceptions with the same name as standard Oracle exceptions, you are not able to trap the standard Oracle exception of the same name in your exception handler. It only responds to the user-defined exception.

OMS-52270: The name of an exception should start with "e_" and must be a meaningful name (never the same as a standard Oracle exception).
Rationale This makes it easy to identify that it is an exception and what the exception is about.

OMS-52280: The name of a record variable should be suffixed with "_rec".
Rationale This makes it easy to identify that the variable is a record variable.

OMS-52285: The name of a table variable should be suffixed with "_tab". The corresponding index should be prefixed with "i_". This index is the number of an element in the table.
Rationale This makes it easy to identify that the variable is a table variable.

OMS-52290: Functions must be given a meaningful, preferably pronounceable, name. If functions are defined to support other functions (for example, conversion), a short name for the supported function can be part of that function's name. If supporting functions are general, that is common functions which support several other functions, then prefix the function's name with 'gen_'.
Rationale This makes it easier to read, understand and pronounce the code.

OMS-52291: Procedures must be given meaningful, preferably pronounceable, names. If procedures are defined to support other procedures (for example, value checking), a short name for the supported procedure can be part of that procedure's name. If supporting procedures are general procedures, that is common functions which support several other procedures, then prefix the procedure's name with 'gen_'.
Rationale This makes it easier to read, understand and pronounce the code.

OMS-52292: The name of a package should reflect the function(s) that are stored within that package.
Rationale This makes it easier to read and understand the code.

OMS-52820: Prefix variables local to the program unit with 'l_'.
Rationale This makes it easy to identify which variables are local.
Example l_lastfield

OMS-52821: Prefix variables global to all program units in the package body with 'g_'.
Rationale This makes it easy to identify which variables are global.
Example g_client

OMS-52822: Prefix parameters for procedures and functions with 'p_'.
Rationale This makes it easy to identify that they are parameters.
Example p_contract

OMS-52823: Use index variable 'i' or 'j', or prefix index variables (loop counters) with 'i_logical_name'.
Rationale This makes it easy to identify that they are index variables.
Example i_counter

OMS-52824: Suffix PL/SQL Cursor return type declarations with '_curtype'.
Rationale This makes it easy to identify that they are PL/SQL Cursor return types.

OMS-52825: Suffix PL/SQL 8 Object type declarations with '_objtype'.
Rationale This makes it easy to identify that they are PL/SQL 8 Object types.

OMS-52826: Suffix PL/SQL record type declarations with '_rectype'.
Rationale This makes it easy to identify that they are PL/SQL record types.

OMS-52827: Suffix PL/SQL subtype declarations with '_subtype'.
Rationale This makes it easy to identify that they are PL/SQL subtypes.

OMS-52828: Suffix PL/SQL table type declarations with '_tabtype'.
Rationale This makes it easy to identify that they are PL/SQL table types.

Declarative Part
OMS-52300: Only use the following datatypes in PL/SQL declarations:
VARCHAR2
NUMBER
PLS_INTEGER
DATE
BOOLEAN
BLOB
CLOB
Do not use the datatypes RAW, LONG RAW, or the IBM compatible datatypes in PL/SQL variable declarations.
Rationale The datatype CHAR may lead to coding errors when comparing values in PL/SQL code. A
CHAR(1) column does not use less space than a VARCHAR2(1) column.
The IBM compatible types are NUMBER subtypes. NUMBER variables with a maximum length precision are preferred, unless you need it for compatible reasons.
PLS_INTEGER is preferred above BINARY_INTEGER for performance reasons.
PL/SQL cannot interpret RAW data.

OMS-52301: Do not declare index variables for numeric loops; rely on implicit declaration of index variables.
Rationale This makes the code less prone to errors.
Example Use:
begin
...<<hours>>
...for i_hours in 1..24 loop
......(...)
...end loop; --hours
end;

instead of:

declare
...i_hours number(2,0);
begin
...<<hours>>
...for i_hours in 1..24 loop
......(...)
...end loop; --hours
end;

Note: The dots indicate spaces

OMS-52302: If a variable must always have a value, declare the variable as NOT NULL and assign it a (starting) value at declaration.
Rationale This makes it easier to read the code, and thereby to prevent coding errors during initial coding and maintenance.

OMS-52303: If a variable contains a known constant value throughout the program, declare it as a constant.
Rationale This makes it easy to identify that the value is truly a constant and may/will never change.

OMS-52304: Never redeclare globally declared identifiers from the package STANDARD
Rationale PL/SQL declares built-in functions globally in package STANDARD. Redeclaring them locally is error prone because your local declaration overrides the global declaration.

OMS-52310: Declare explicit cursors for all SQL statements that return more than one row or no rows at all.
Rationale This prevents the program ending up in unnecessary exceptions.

OMS-52311: If a SELECT statement requires bind variables, declare these bind variables as cursor parameters.
Rationale This makes it easier to read and understand the code as it is easy to identify which values are used. Also values can be assigned as part of the open cursor statement.

OMS-52320: Use the %TYPE attribute referencing a column in the table(s) used in the SELECT statement to declare the datatype for a cursor parameter.
Rationale This makes your code less vulnerable for changes. If the datatype of the database column changes, then you would not need to change the cursor parameter declaration.

OMS-52321: Specify a full list of all columns in each SELECT statement you declare, instead of simply using the select *. As an exception, you are only allowed to use an asterisk when more than 75% of all columns of a table have been selected.
Rationale The declaration of the necessary record variable for the cursor, reserves space for all columns in the cursor select-statement. This would demand too much unused resources if through the select * space is reserved for a large number of columns that are not used anywhere in the code.

OMS-52330: Use the %ROWTYPE attribute to declare a record structure for each explicitly defined cursor. Do not use the implicit record declaration mechanism. The only exception to this rule is when you use a cursor FOR loop.
Rationale This makes your code less vulnerable to changes. Whenever a column is added or removed from the cursor definition, you do not need to change your %ROWTYPE attribute.

OMS-52340: Use the %TYPE attribute to declare a temporary variable that is used to store a value related to a database column.
Rationale This makes your code less vulnerable to changes. If the datatype of the database column changes, then you would not need to change the temporary variable declaration.

OMS-52360: Place all user-defined exceptions together in the declarative part of the PL/SQL block. First specify all user-defined Oracle exceptions. Then specify all non-Oracle-related exceptions.
Rationale This makes it easier to read and therefore understand the code.

OMS-52361: Explicitly declare exceptions for each of the Oracle error conditions you can expect to occur in normal program execution. In the comment you specify the text of the Oracle error message.
Rationale Declaring and trapping expected errors makes it possible to provide exact error handling and messaging. Providing the text of the error message as a comment makes it easier for the programmer to understand what kind of errors will raise the various exceptions.
Example declare
...(...)
...e_snapshot_too_old...exception;
...-- ORA-01555: Snapshot too old (Rollback segment too small)
...pragma exception_init(e_snapshot_too_old
........................, -1555
........................);
...(...)
begin
...(...)
exception
...when e_snapshot_too_old then
........(...)
end;

Note: The dots indicate spaces

OMS-52362: Define exceptions and exception handlers for functional errors or warning conditions. In the comment, you must specify the condition(s) that raise the exception.
Rationale Grouping all the exceptions into a single handler makes the code easier to read and maintain. Also using exceptions makes it possible to raise the same exception from different parts of the whenever a similar error occurs, that is handled in the same way in the exception handler.
Example declare
...-- No more available seats for re-assignment
...e_out_of_seats...exception;
...(...)
begin
...(...)
...raise e_out_of_seats;
...(...)
exception
...when..e_out_of_seats then
.........(...)
end;

Note: The dots indicate spaces.

OMS-52363: Customize exception handlers that use raise_application_error so that only error number -20000 is used. Customize the message text explicitly to pass module-relevant messages.
Rationale The user-supplied error numbers must be between -20000 and -20999, because Oracle reserves all other numbers for standard errors. Using a single number prevents running out of numbers and lightens the task of having to administer unique message numbers.

Reference: See also the Oracle Headstart documentation, section End User Assistance, Message Handling how you can use qms$errors.show_message to raise application errors and use application specific error numbers.
Executable Part
OMS-52500: Avoid implicit datatype conversion. If you expect implicit datatype conversion to occur, you must place an explicit datatype conversion function around the expression.
Rationale Avoiding implicit datatype conversion makes it easier to follow what is happening in your program. Especially when tracking errors, implicit datatype conversion is easily overseen and thereby the actual error may be difficult to detect.

OMS-52510: Do not use NULL statements (null;) in your PL/SQL code in your completed code. Before you hand over your PL/SQL code, remove all NULL statements that were never originally envisaged.
Rationale NULL statements do not perform any actions. It is only confusing to read the code when threads end up in NULL statements.
Normally, you should only use the NULL statement as a temporary placeholder for PL/SQL code that still has to be developed.
However, in some situations, NULL statements can be useful. Although not recommended (see OMS-52310), an example would be catching a NO_DATA_FOUND exception. In those cases, use inline comments to clarify its use.

Loop Constructs
OMS-52530: Make sure that in each of the loop constructs the condition to end the execution of the loop will eventually occur. Be careful with the occurrence of NULL values in the loop condition that may cause early loop termination or no loop termination at all.
Rationale Otherwise, you may end up in an indefinite loop, and eventually run out of memory.

OMS-52531: Use a cursor FOR loop as the preferred method of handling SELECT statements that return more than one row.
Rationale This is effective coding. You simplify coding by using this as you do not need to perform explicit OPEN, FETCH, and CLOSE statements. A cursor FOR loop implicitly declares its loop index as a %ROWTYPE record, opens a cursor, repeatedly fetches rows of values from the result set into fields in the record, and closes the cursor when all rows have been processed.

Cursor Handling
OMS-52534: Avoid the use of explicit cursors to perform DML operations.
Rationale This may impede performance.

OMS-52540: You must CLOSE explicitly declared cursors not handled by a cursor FOR loop in exception handlers. Test whether these explicit cursors are still open using the %ISOPEN cursor attribute.
Rationale You can have a limited number of cursors open at the same time. The cursor is released in the close cursor statement.

OMS-52560: Use the CURRENT OF CURSOR mechanism in combination with a SELECT FOR UPDATE statement, instead of making your own reference to a ROWID.
Make explicit use of ROWID when the FOR UPDATE OF will lock too many records too soon and/or you need to perform COMMIT; while the cursor is open.
Rationale This simplifies your code and makes it easier to read and maintain.

OMS-52570: Only specify the BEGIN and END keywords around a subblock when you need to declare local identifiers or perform exception handling in the subblock.
Rationale Unnecessary use of keywords makes the code more difficult to understand and maintain.

OMS-52575: Do not use the GOTO statement.
Rationale Use of GOTO statements are not necessary in PL/SQL. Use of GOTO statements can result in complex, unstructured code (sometimes called spaghetti code) that is hard to understand and maintain.

Recursion
OMS-52532: Make sure that a recursive subprogram has at least two paths -- one that leads to the recursive call and one that leads to a terminating condition.
Rationale This prevents the recursive program from ending in an indefinite loop resulting in the program running out of memory.

Debugging
OMS-52610: Use the standard packages supplied by Oracle to extract debugging information at critical places from stored procedures. Locate the calls to the I/O procedures in your stored procedures, along with a mechanism to activate them through, for example, a global variable.
Rationale Using mechanisms to turn debug information on and off, makes it easier to test and debug your code.

Exception-Handling Part
OMS-52700: Do not place excessive coding in the exception-handling part of a PL/SQL block; only specify the code that is strictly necessary to handle the exception effectively.
Rationale The exception part is only meant to handle the specified exceptions quickly and effectively. Using excessive coding within the exception increases the risk that an exception may occur within the exception. This exception is passed to the enclosing block (if any). It may not be clear what actually should occur in the enclosing exception. In general the code is more difficult to read, understand and thereby maintain. You should not attempt to bypass this rule by calling a procedure that performs the excessive coding. The exception is if this procedure is an error messaging routine that provides standard error messaging.

OMS-52701: Do not use SQLCODE to branch within the exception-handling part.
You should explicitly define exceptions for each of the Oracle error conditions you can expect to occur in normal program execution.
Rationale This makes it easier to see what kind of exceptions may be expected from the program. In addition, it prevents you from having to include excessive coding within the exception handler to distinguish between the various exceptions.

OMS-52702: Do not prefix an exception with the keyword STANDARD.
Rationale Using STANDARD as a prefix may cause problems as this is a public synonym for an Oracle-provided package of stored procedures.

Stored Package, Procedures and Functions
OMS-52805: Store procedures and functions in packages whenever possible.
The criteria for grouping program units together in a package is:
  • They (potentially) share subprogram units, data structures, etc. These objects have to be declared in the package body only (information hiding).
  • They implement general functionality, related to a specific object.
  • Together, they implement a specific functionality or feature.
Rationale Grouping procedures and functions into packages makes it easier to group program units into logical units, and thereby makes it easier to locate the code. Also it is possible to modify a package's definition without causing Oracle to compile calling procedures or functions.
Finally the number of independent objects to manage/administer will be lower.

OMS-52810: Always manually recompile stored procedures or functions when remote referenced objects have been changed. As there is no automatic mechanism for this, you must keep this as a part of your manual programming procedures.
Rationale This is required to ensure proper working of the stored procedures and functions. When a remote referenced object is changed, this does not make the dependent objects invalid.

OMS-52816: Upon calling a package construct, qualify its name with the name of the package.
Rationale This is always required outside the package. When using the package name also within the package (where it is not strictly required) you make the code easier to amend. You can move a program unit without having to change the calls from one package to another whenever required.

OMS-52830: Never declare a package variable in the package specification. Instead, declare the variable in the package body.
Provide a GET function and SET procedure for packaged variables when the variable needs to be accessible from outside the package.
Rationale The primary reason for this standard is information hiding. The underlying data structure (a packaged variable, global, record group or Oracle table) can now be modified without affecting all the code that relies on the value returned.
It also allows control (in the SET procedure) over who is allowed to change the value of a variable and what values and value transitions are allowed.

Package Body
OMS-52831: Always include the name of the program unit in the end statement.
Rationale This helps finding your way around in the package body.

OMS-61632: Declare the private sections in a package body in the following order:
private types, private constants, private variables, private cursors, private program units. Place the forward declarations prior to the private program units.
Use the following layout:

create or replace
package body my_package
is
/*********************************************************************************
Created by......Allan Turing, Thinking Machines Inc.

(...)

*********************************************************************************/

--
-- private types
--

--
-- private constants
--

--
-- private variables
--

--
-- private cursors
--

--
-- forward declarations
--

--
-- private program units
--

procedure do_something
(...)
end do_something;

end my_package;
Rationale This gives a common and orderly layout which ultimately makes the code easier to read.

OMS-61633: Comment each packaged private program unit in the package body. Each program unit should be documented with its purpose, usage and possible remarks. Use the following layout with double hyphens:


create or replace
package body my_package
is
/*********************************************************************************
Created by......Allan Turing, Thinking Machines Inc.

(...)

*********************************************************************************/

--
-- private types
--

--
-- private constants
--

--
-- private variables
--

--
-- private cursors
--

--
-- forward declarations
--

--
-- private program units
--

procedure do_something
--
-- Purpose..A description of the purpose of this private program unit, just
--..........like the description of the purpose of public program units in
--..........the package specification
--
-- Usage....-
--
-- Remarks..Any remarks, including known limitations and enhancements, for this
--..........program unit.
--
is
begin
...null;
end do_something;

end my_package;
/

Note: The dots indicate the number of spaces required.
Rationale This makes it easier to understand what the procedure is about. Documenting it in the package body is natural as the procedure is private to the body.

Package Specification
OMS-61634: Declare the public sections in a package header in the following order:
public types, public constants, public variables, public cursors, public program units.
Use the following layout:


create or replace
package my_package
is
/*******************************************************************************
Purpose..Package for file IO.
.........A package level description of the purpose of the package.

Usage....Explaining the usage of the package

Remarks..Package level remarks.
********************************************************************************/

--
-- public types
--

--
-- public constants
--


--
-- public cursors
--

--
-- public program units
--

procedure open_file
(.........p_file_name in varchar2...-- Name of file (including path, e.g.
....................................-- 'c:\work\test.txt').
);
(...)

end my_package;


Note: The dots indicate the number of spaces required.
Rationale This gives a common and orderly layout which ultimately makes the code easier to read.

OMS-61635: Comment each packaged public program unit in the package specification. Each program unit should be documented with its purpose, usage and possible remarks. Use the following layout with double hyphens. Each parameter must be listed and described; any requirements/constraints on the values that may be passed in for the parameter must be indicated. Any specific security or performance issues that apply to the program unit should also be given (for example, the procedure may take extremely long to perform or it contains some additional privileges). Post-conditions (changes brought about by the procedure) may be outlined if they are not clear from the purpose of the program unit.

create or replace
package my_package
is
/*******************************************************************************
Purpose..Package for file IO.
.........A package level description of the purpose of the package.

Usage....Explaining the usage of the package

Remarks..Package level remarks.
********************************************************************************/

--
-- public types
--

--
-- public constants
--

--
-- public variables
--

--
-- public cursors
--

--
-- public program units
--

procedure open_file
(.........p_file_name in varchar2...-- Name of file (including path, e.g.
....................................-- 'c:\work\test.txt').
);
-- Purpose..Opens file p_file_name. When the file does not exist the file is
--..........created. When the file exists, the file is opened and the position
--..........is set to end of the file. A program unit level description of the
--..........purpose of the program unit.
--
-- Usage....p_file_name must be a valid path and file name. The usage of the
--..........program unit.
--
-- Remarks..Any remarks, including known limitations and enhancements, for this
--..........program unit.

end my_package;
Rationale This makes it easier to understand what the procedure is about and how it should be invoked.

Application Systems
Notes
OMS-61517: Record the deletion of primary objects such as entities, modules, etc. as revision history in the Notes of the Application System. Add new entries on top.
Rationale Analysts or developers might want to know what happened to the object. If this kind of information is not recorded, the person that can explain, might not be available on the project, if at all.
Putting new entries on top gives quick insight into the latest change.
Example REVISION HISTORY
Label Date Name Description
1.1 12-11-1999 S. Muller Removed function 31
and module ASP0050F
1.0 06-07-1998 L. Jellema Removed entity STATUS

1.1
----
Removed function 31 and module ASP0050F because their functionality
was dropped as result of tighter planning.

1.0
----
Removed Entity STATUS, functionality now in domain STATUS

PL/SQL Definitions
OMS-45033: For packages, include in the package specification, at the top of the PL/SQL text, a header with the following items:
  • Purpose of the package
  • Usage of the package
  • Remarks
  • For each public routine, an explanation of parameters used, a purpose, usage and remarks

    Include in the package body, at the top, a header with the following items:
  • Revision history including the date, who performed the change, what has changed and the construct. The newest entries should be at the top. The first (oldest) line should state 'Creation' in the appropriate language.
  • For each private routine, an explanation of parameters used, a purpose, usage and remarks

    For independent PL/SQL routines (that is those not included in a package), add at the top of the PL/SQL text a header with the following items:
  • Purpose of the routine
  • Purpose and Use of each argument
  • Remarks
  • Revision history including the date, who performed the change and what has changed. The newest entries should be at the top. The first (oldest) line should state 'Creation' in the appropriate language.
Rationale In this way, it is properly documented what is the purpose of the routines. This makes it easier during development and maintenance.

Keeping track of what is changed can explain a lot of what happened during the Development. Questions such as, "Why was this changed and who did it?" are easily answered.
Putting new entries on top gives quick insight into the latest change.
Example Package Specification
========================
package my_package
is
/*************************************************************************************
Purpose:..Package for file IO. A package level description of the purpose of the package.

Usage:....Explaining the usage of the package

Remarks:..Package level remarks.
*************************************************************************************/
--
-- public program units
--

procedure open_file
( p_file_name in varchar2 -- Name of file (including path)
);
-- Purpose: Opens file p_file_name. When the file does not exist the file is
-- created. When the file exists, the file is opened and the position
-- is set to end of the file. A program unit level description of the
-- purpose of the program unit.
--
-- Usage: p_file_name must be a valid path and file name. The usage of the
-- program unit.
--
-- Remarks: Any remarks, including known limitations and enhancements, for this
-- program unit.

end my_package;

Package Body Specification
=============================
package body my_package
is
/*****************************************************************************************
Revision History

When.........Who............................Construct
..Revision...What
31-12-1980...Richard Voss...................wrap_line_when_too_long
..1.1........Added rendering algorithm, using a variation of classic mid-point
.............displacement.
01-01-1978...Allan Turing
..1.0........Creation
*****************************************************************************************/
--
-- private program units
--

procedure do_something
--
-- Purpose A description of the purpose of this private program unit.
--
-- Usage -
--
-- Remarks Any remarks, including known limitations and enhancements.

is
begin
null;
end do_something;

end my_package;

Server Generator: The text entered here is used by the Generate Database from Server Model utility to generate create statements for stored PL/SQL objects.