GNM_RCS_PLSQL_Standards_TR

• 1. .Introduction

     1. Purpose

This document outlines the standards and guidelines for all PL/SQL development for the Rights & Contributor System (RCS).

These standards are to be followed in development wherever possible, unless there is a pressing need to deviate from them.

• 1. Scope

This document covers PL/SQL development wherever the language is used, including:

• • Database PL/SQL – triggers, packages, procedures & functions;

  • Oracle Forms PL/SQL – triggers and program units; and

  • Oracle Reports PL/SQL – triggers and program units.

• 1. .File naming conventions

The naming conventions used in software development should simply be an extension of those used in the database and application design. This starts at the file name.

• 1. File names

Files containing PL/SQL code to be created on the database should be given a file name matching the name of the object being created.

For example, the package specification CURR is defined in the file curr.pks, and relates to the CURRENCIES table, whose notional alias is “curr”.

• 1. File extensions

File extensions should conform to the following standard:

Note that the package specification is separated from the package body. This simplifies the mapping of operating system files to database objects.

Importantly it also helps that a package body can be released without any change to its specification, thereby minimizing the impact of other objects in the database that could be invalidated.

• 1. .File layout

     1. File header

Whether the program unit is a package, an standalone procedure or function, or a trigger, a header should always be written, which includes a prompt that an operator would see if the file is run in SQL*Plus. It should have this structure:

prompt Creating package AAAS...

set document off

set serveroutput on

set scan off

/*

* Package containing all server PL/SQL that

* relates to the AR_ACCOUNT_ACTIVE_STATUSES table.

*/

• 1. PL/SQL

Below this comment the PL/SQL program unit should be defined, for example:

create or replace package aaas as

...

end aaas;

.

/

• 1. File Footer

After the program unit definition errors that occurred should be displayed, to help with debugging:

show errors

set document on

set scan on

• 1. Other layout rules

Do not use the <Tab> character in PL/SQL code – use spaces instead. This ensures that the code will always have the same layout in whatever ASCII editor it is opened.

The standards below apply to the layout of all PL/SQL modules:

• • The create statement uses create or replace to facilitate future changes;

  • The end statement references the package/procedure/function name;

  • Spaces are used instead of <Tabs>;

  • Indentation is 2 characters;

  • Code should be left aligned;

  • Parameters should be vertically left aligned, and preceded by commas;

  • Parameter modes (IN/OUT/IN OUT) should be defined, and left aligned;

  • Parameter datatypes should be vertically left aligned; and

  • All code is written in lower case.

• 1. .Module naming conventions

     1. Package names

There are 4 main styles of package in RCS:

• 1. Table-based packages (object-oriented style)

Table packages are written to provide functionality that relates mainly to a single table, and these are named after the table. Typically, such packages include procedures and functions to check a primary key or a unique key.

These packages take their name from the table alias, which should be 4 characters long.

For example A package called comm contains general processing for table COMMISSIONS.

• 1. Packaged batch processes

These provide significant areas of related of system functionality, affecting multiple tables. They are often run in batch, and invoked by database jobs.

The name should reflect the functionality delivered.

For example invoice_interface

• 1. Packages to support individual client modules

Client module packages are used to partition complex PL/SQL code onto the database, thereby improving network performance. (They are likely to make calls to the table-based packages.)

They should be given the same name as the client module.

For example rcs_comm_060

• 1. Application packages

These include declarations of application-wide variables and constants, and any common processes not tied to a particular table or process.

The package name should reflect its purpose and contents.

For example algorithms

• 1. Procedure & function names

Procedures and functions should be given names that reflect their functionality, whether they are defined within a package, or as standalone modules.

For example

create or replace package atyp as

function for_pk (p_code in varchar2) return association_types%rowtype;

function for_uk (p_name in varchar2) return association_types%rowtype;

function exists_pk (p_code in varchar2) return boolean;

function exists_uk (p_name in varchar2) return boolean;

end atyp;.

/

These names must be used consistently across all packages where they are applicable.

• 1. Trigger names

Trigger names must be a composite of the table alias (for the table they fire on) and a number of identifiers that indicate when they fire.

<alias>_tl_iud

Where

<alias> is the table’s four-letter notional alias;

t is the time the trigger fires – Before or After;

l is the level the trigger fires at – Row or Statement; and

iud signifies Insert, Update and/or Delete.

For example

Trigger comm_br_iu fires on the COMMISSIONS table, Before Row, on Insert and Update.

• 1. .PL/SQL naming conventions

Names of elements within the code must not conflict with names of database objects. The following naming conventions should be adopted within the code:

What comes after the prefix must be a meaningful name. Where appropriate use table aliases and column names to help construct the complete names.

Examples:

v_comm_fee is a suitable name for a variable to holds COMMISSION.FEE; and

get_comm is a suitable name for a cursor that fetches records from the COMMISSIONS table.

• 1. .Layout of declarations

The standards below apply to declarative statements:

• • Constant declarations should be grouped together, followed by the variables;

  • Types are declared next, ahead of Records and Tables;

  • NB Types can be declared at package level is useful elsewhere in the application.

  • Cursors are declared after all of the above;

  • Private procedures and functions should be grouped together, after the above;

  • Public procedures and functions should be grouped together, after the private;

  • A small comment should identify the start of any private declarations;

  • A small comment should identify the start of any public declarations;

  • The order of public procedure declarations must be the same in spec. and body; and

  • Forward declarations should be avoided unless absolutely necessary.

• 1. .Layout of IF statements

The following standards should be applied to if statements where possible.

• • if statements should evaluate conditions in order of “least expected cost”:

  • The “expected cost” of a condition is the product of its likelihood and its resource cost. This principle should be applied, albeit subjectively, to minimise the expected resource usage of a PL/SQL statement.

  • As a rule of thumb, it is better to evaluate client-side conditions first, followed by server-side conditions requiring no SQL; and conditions requiring SQL should be evaluated last.

  • Parentheses should be used to clarify complex conditions;

  • Avoid double negatives i.e. use if A=B then.. rather than if not(A!=B) then.. .

  • Alternate outcomes of if elsif else end if statements should be closely related to each other. For clarity, it is often better to nest if statements.

• 1. .Layout of LOOPS

PL/SQL has three standard forms of LOOP:

• • LOOP

  • WHILE-LOOP

  • FOR LOOP

These standards do not restrict the use of any of these types of loop.

• 1. LOOP

Use this loop construction when you wish to test the “exit condition” at the end of, or within, the loop’s processing. Exit the loop unconditionally using the exit statement, or conditionally using the exit when statement.

Example

loop

<statements>

exit when x<y;

end loop;

• 1. WHILE-LOOP

Use this loop construction if you wish to test the exit condition before performing the loop’s processing.

Example

while x<y loop

<statements>

end loop;

• 1. FOR LOOP

This is the most used loop construction in RCS. This loop should be used for all loops with a known number of iterations, and for the management of some cursors.

Example

for i in 1..20 loop

<statements>

end loop;

FOR LOOPs are frequently used for cursor management (cf. section on cursors) although explicit cursors are used more. The following standards should apply:

• • Never return from a function within a for loop; (refactor where you encounter this)

  • Always check for open cursors in exception handlers and close them; and

  • Declare cursors in the declarative section and use for <cursor name> loop

• 1. Loop counters

When using loops always define the loop counter as i – this is very common practice in the industry, and therefore quickly recognisable by others.

A loop nested within the main loop is given the incremental loop counter j. If a further loop is nested a level deeper the loop counter would be k, and so on.

Example

for i in get_dept loop

... statements ...

for j in get_empl_in_dept (cp_deptno => i.deptno) loop

<statements>

for k in 1..j.years_service loop

<statements>

end loop;

end loop;

end loop;

When two or more loops are nested at the same level the loop counter can be the same.

Example

for i in get_dept loop

... statements ...

for j in get_managers (p_deptno => i.deptno) loop

... statements ...

end loop;

for j in get_non_managers (p_deptno => i.depno) loop

... statements ...

end loop;

end loop;

• 1. Loop labels

Loop labels are optional, but should be used where they would improve readability, e.g.

• • When loops contain many lines of code; and, especially

  • When there are many levels of nested loops.

When loop labels are used, they should be referenced in the end loop statement.

Example

<<check_salary>>

for i in 1..20 loop

... statements ...

end loop check_salary;

• 1. .Layout of EXCEPTIONs

The following guidelines apply:

• • Exceptions handlers should be listed in order of likelihood;

  • Trap Oracle errors by using pragma exception_init declarations;

  • Application errors must fall within the range –20000 to –20999; and

  • Exception handlers should be indented below the when clause:

Example

declare

insufficient_privileges exception;

invalid_monetary_value exception;

pragma exception_init (insufficient_privileges,-1031);

pragma exception_init (invalid_monetary_value, -20001);

begin

validate_monetary_values; -- called proc may raise application error -20001

... statements ...

exception

when value_error then

raise;

when insufficient_privileges then

dbms_output.put_line(‘Insufficient privileges to Insert’);

rollback;

when others then

dbms_output.put_line(‘Insert failed - ’||SQLERRM);

rollback;

end;

.

• 1. .Techniques (by module type)

In general PL/SQL code should be modular, but not to the point of over-complication.

Modularity improves performance and eases the maintenance burden.

• 1. Packages

• • Use packages to group related procedures and functions.

  • Declare constants and variables at the level (scope) that they are needed. See the section on variables and constants for guidelines;

  • Private procedures & functions should define common code within a package;

  • Public procedures & functions should define code accessed from elsewhere in the application;

  • Table-based packages must use standard names for their procedures and functions, and the parameters within them, to make client coding much easier; and

  • Declare cursors at package-level if they are reusable, but make them private rather than public if the data they return can be output using a function.

• 1. Procedures

Procedures should be encapsulated within packages. It is very rare that they need to be created as standalone database objects.

• 1. Functions

Functions should also be encapsulated within packages, with very few exceptions. However, exceptions may include non-data related functions, such as:

Example (1)

function null_high (p_date in date) return date is

begin

return (nvl(p_date,to_date(‘31129999’,’DDMMYYYY’)));

end null_high;

Example (2)

function timestamp return date is

begin

return sysdate;

end timestamp;

Example (3)

function datestamp return date is

begin

return trunc(sysdate);

end datestamp;

The above examples do not access data stored on the database. To minimise network traffic functions such as these are often coded in the client library software as well as being stored on the database.

• 1. Triggers

Triggers are used to attach functionality to DML statements, where that functionality is independent of the calling client.

A good example is the population of audit columns and audit tables.

• 1. Granularity

Triggers must only fire when appropriate, so restrict them as far as possible.

A WHEN clause should be defined wherever possible to restrict how often the trigger body is executed.

Example

create or replace trigger curr_br_iu

before insert or update of end_date on currencies

for each row

when (new.end_date is null)

declare

... declarations ...

begin

... statements ...

end;

NB It is easier to maintain a single trigger containing conditional processing than to maintain multiple triggers each having a different when clause. In this situation it is better NOT to apply the above standard.

• 1. Mutating tables & constraining tables

Mutating table errors and constraining table errors must be coded around in all scenarios. (cf Appendix C.) Dropping foreign key constraints to avoid them is not an acceptable solution, and neither is it acceptable to separate the process into 2 distinct commit units.

• 1. Code placement

Restrict the amount of code written into triggers. If the code is extensive consider moving it to the appropriate table-based package. This means that it can then be re-used by other objects.

Example

Trigger curr_br_iu

From its name we know the trigger is executed on table CURRENCIES (CURR) Before Row on Insert or Update. (cf. 4 Module naming conventions)

Code executed by this trigger is found in the package CURR.

• 1. .Variables & constants

Variables, constants and parameters are all used for temporary storage of data.

• 1. Format

The format (datatype, precision and scale) of variables should be determined from the database where possible using the %type or %rowtype constructs.

Example

declare

v_empl_name empl.name%type;

%type or %rowtype can also be used to enable a variable or constant to assume the characteristics of another variable or constant that is visible in the current scope.

Example

declare

v_empl_sal empl.sal%type;

v_empl_sal_avg v_empl_sal%type;

Declaring NOT NULL variables

%type does not impose not null conditions on variables. This can be done explicitly if you wish to prevent a variable from being given a null value. Clearly though, you must initialise the variable to a value if it is declared as not null. NB constants can be initialised with a null.

declare

v_empl_sal empl.sal%type;

v_empl_sal_avg v_empl_salary%type not null := 0;

Using Boolean variables instead of functions

The boolean datatype can be used very effectively instead of declaring separate functions.

declare

c_dates_valid constant boolean := p_start_date <= p_end_date;

• 1. Defaults

Constants and not null variables must always be initialised with a default value.

Defaults can be assigned using either := or the default key word.

Never initialise a variable to null – that’s the value it gets anyway.

• 1. Instantiation

A package is said to be “instantiated” the first time it is called in a database session. At this time (and only this time) any package-level code is executed. It is not executed on subsequent calls to the package in that session unless pragma serially_reusable is also declared.

One use for package-level code is to assign values to package variables where such initialisation is more complex than usual, or to cache commonly used data as package data.

• 1. Scope

Declare variables and constants at the lowest level possible.

Variables and constants defined at package-level (whether private or public) are known as “package data” and the package is said to have “package state” and is “stateful” rather than “stateless”. The variables and constants have the database session as their scope i.e. once set they persist for that session, unless changed programmatically.

NB Scope is different in Oracle Forms: in Oracle Forms package variables are initialised each time the Form is opened: they do not persist within either the Oracle Forms session or the database session.

This applies to PL/SQL packages in Oracle Forms whether they are of type Form or Library. However it is important to note here that libraries can be implemented so that they persist within the Oracle Forms session. This has very significant ramifications for the programming style that is adopted, and is not done at GNM.

Declarations that are local to a procedure or function are the most common scope. They are often all that is necessary. They are very intuitive as these declarations are situated very close to the code that uses them. These declarations do not persist once the processing of the function or procedure has completed.

• 1. Visibility

Variables and constants declared at package-level can be declared in the specification or the body depending on how “visible” they need to be.

Package data accessed by code internal to the package should be “private”, i.e. declared in the package body.

Package data accessed by external code should be “public”, i.e. declared in the package specification.

• 1. Controlling access to “public data”

Encapsulation is the best technique to control access to “public” data. It involves:

• • Declaring the data privately; and

  • Using public get and set procedures & functions giving read and write access to the data.

The decision to implement this technique should be determined on a case-by-case basis.

• 1. .Parameters

Parameters should be used in preference to direct referencing of bind variables.

• 1. Definition

When defining parameters for procedures or functions (or cursors):

• • Always specify the mode (IN/OUT/IN OUT) even though IN is the default;

  • Explicitly state the datatype or use a %type construct;

  • Only specify default parameter values if the program may be called with only some of the IN parameters.

Example (1)

procedure pay_employee (p_empno in number) is

begin

... statements ...

end pay_employee;

Example (2)

procedure pay_employee (p_empno in emps.empno%type) is

begin

... statements ...

end pay_employee;

• 1. Parameter Passing

Use the named notation wherever possible.

Example

pay_employee (p_empno => 12345

,p_sal => 20000);

instead of

pay_employee (12345

,20000);

NB Some older code uses positional notation and should be refactored when encountered.

• 1. Overloading

When a program unit has been overloaded it is generally desirable that one version be the “master” version. This master version is called by the other versions (rather than similar code being repeated in the various versions). Whilst quite robust this is not always possible. An alternative technique, which has the benefit of reducing dependencies between program units, is simply to share any cursors that are used by the different versions. Or named notation can be used on a single procedure/function, with some parameters not being passed but having defaults values.

Care should be taken over the decision to overload versus the decision to use optional parameters. Overloading tends to be favoured where functions (or procedures) use significantly different processing, or the modules calling them require different OUT parameters.

• 1. .Tables

PL/SQL tables provide a means of storing data in rows and columns, without using database tables. The PL/SQL table’s primary key allows array-like access to the rows. Data held in PL/SQL tables is only accessible to the current session. It is unaffected by commit processing and cannot be accessed using SQL statements (without significant complication and inefficiency)

PL/SQL tables differ from arrays because:

• • They are unbounded; and

  • “Sparsity” provides that non-consecutive index numbers can be used in the key.

• 1. Declarations

Declare a new TYPE and then declare a PL/SQL table of that TYPE.

Example

declare

type typ_curr is table of currencies%rowtype index by binary_integer;

tab_curr typ_curr;

• 1. Usage

PL/SQL tables can be used for a variety of purposes including:

• • Passing arrays of data between program units;

  • Bulk collect of data in queries and bulk DML using forall statements; and

  • Solving mutating table errors (see Appendix D: Mutating tables);

• 1. .Records

Use a PL/SQL record to hold a series of related variables in temporary storage.

• 1. Declarations

A simple RECORD declaration is based on a database table or cursor and uses %rowtype.

Example

declare

rec_curr currencies%rowtype;

A composite RECORD declaration pulls together more elements. The first step is to define a RECORD TYPE. The second step is to declare a record of that TYPE.

Example

declare

type typ_period is record (start_date periods.start_date%type

,end_date periods.end_date%type);

rec_period typ_period;

• 1. Usage

Use records to store multiple related variables rather than declaring many variables. This technique often reduces the overall number of declarations and assignment statements that are required. It also helps guard against changes in table definitions.

A good example is the temporary storage of values returned by a cursor.

• 1. .Cursors

Cursors, preferably, are always explicitly declared within a package, procedure or function.

• 1. Declaration and usage

Explicit use of the open, fetch and close keywords is the normal method used, also implicit cursors are useful when a for loop structure is more appropriate.

Cursors must be parameterised, rather than referencing variables that are external to the cursor.

Example

cursor get_dept (cp_deptno in depts.deptno%type) is

select dept.dname

from depts dept

where dept.deptno = cp_deptno; [cursor parameter]

is better than:

cursor get_dept is

select dept.dname

from depts dept

where dept.deptno = p_deptno; [program unit parameter]

• 1. Manipulating queried records

If records retrieved by a cursor need to be updated or deleted, a current of clause should be used in the update or delete statement and the query must have a for update of clause.

• 1. .Exception handling

Runtime errors in PL/SQL code are called exceptions. When an error occurs, an exception is raised. Write exception handlers to handle expected exceptions.

Exception handlers are not coded as a matter of course: well-written code actually requires very little exception handling.

• 1. Names

Use meaningful names for all user-defined exceptions.

• 1. Propagation

When a PL/SQL block raises an exception processing is stopped and control transfers to the block’s exception handling section.

However, PL/SQL blocks can be nested within one another, or may explicitly call one another. When an inner block raises an exception for which there is no exception handler that block terminates and the exception propagates, or "falls out" to the enclosing block. If a handler is not found in the enclosing block, the exception propagates again, finally reaching the outermost block, or calling program. If the outer block does not handle the exception, the code fails and returns a runtime error.

To force the immediate raising of the error the following command can be used in a PL/SQL block’s exception handler:

when value_error then

raise;

All exception processing can be bypassed as follows:

when others then

null;

But as a rule this is not advisable as it means errors are occurring and no action is being taken. It is far better to identify the kind of exceptions being raised and then modify the code accordingly.

• 1. Guidelines

Care must be taken when writing exception handlers for the following reasons:

• • Most exception handling is “required” because of poor code. If unexpected errors need to be handled in a PL/SQL block, it is often far better to rewrite the code with a view to avoiding the error;

  • One benefit of modular system architectures is the reuse of code. This benefit may be restricted if exception handlers have been written into called modules when the calling program wants to handle the error; and

  • The manner in which an exception is handled may differ depending on whether the code is running online or in batch (e.g. You may wish to output an error to screen or write it to a log file/table).

Where batch processes are very complex it may be prudent to implement additional user-defined exceptions to help trap errors and more easily debug the code.

• 1. .Comments

Comments must only be added if they are useful. Do not write them if they are not.

Well-written code should be self-documenting thus requiring fewer comments.

This is achieved by using intuitive names for all declarations – especially function and procedure names – and using a clear layout style.

Oracle provides two facilities for comments in code, either of which may be used:

1) Line / part-line comments such as:

<statement> -- Check the department of the employee

2) Multi-line comments such as:

/*

* This section validates ... etc

*/

• 1. Header comment

For all packages, procedures, functions, and triggers a header comment should be written in the file following this structure:

prompt Creating package NTYP...

set document off

set serveroutput on

set scan off

/*

* Package containing all server PL/SQL that

* relates to the NOTICE_TYPES table.

*/

• 1. Comments in packages

The following comments should be written in PL/SQL packages:

• • A comment identifying the start of private procedures & functions and also the public ones;

  • Comments where complex code has been changed as a result of a bug fix – replaced code may also be retained, but commented out, for future reference; and

  • Immediately above a section of complex, counter-intuitive, or non-standard code.

• 1. Commented-out code

Over time remove all commented-out code: all it does is detract from readability.

• 1. .Do’s and Don’ts

Below are summary lists of do’s and don’ts. Adhere to these wherever possible.

• 1. Do’s

• • Use %type and %rowtype where suitable;

  • Declare all cursors;

  • Avoid package state whenever possible;

  • Indent nested code by 2 characters, not tabs;

  • Space code out with blank lines between statements;

  • Declare package-level variables if the value stored is re-usable in the session;

  • Declare package-level cursors if they can be shared across procedures/functions;

  • Use recursion in preference to iteration;

  • “Overload” instead of forcing all procedures/functions to have different names;

  • “Overload” instead of using mutually exclusive parameters;

  • Vertically align as much code as possible;

  • Vertically align lists of items with commas on the left;

  • Use the date format DD /MM/YYYY;

  • Avoid hard-coding literals into the application. Use package constants instead;

  • Use Booleans instead of y/n or binary flags; and

  • Use Boolean constructs instead of if statements to keep code simple:

Example

return p_start_date < p_end_date;

is much more succinct than

if p_start_date < p_end_date then

return true;

else

return false;

end if;

• 1. Don’ts

• • Do not use <tabs> in PL/SQL code – use spaces instead;

  • Do not use goto statements;

  • Do not declare exceptions as an alternative to using the goto statement;

  • Do not use the commit or rollback statements in PL/SQL, unless the program unit only executes in batch, or has pragma autonomous_transaction declared; and

  • Do not write the schema name or db link name in code when referencing schema objects.

• 1. .APPENDIX A: Performance tuning

Often the easiest way to improve the performance of PL/SQL is to tune the SQL and DML statements embedded in it. Such tuning is not in the scope of this document.

The following PL/SQL tuning techniques may be considered:

• 1. Nested FOR loops

It is usually more efficient to combine cursors used in nested for loops.

If nested loops are required for other reasons the order in which they are nested is important.

• 1. Algorithms

Use the pls_integer datatype instead of integer or binary_integer. The former uses machine arithmetic, and less memory. The latter two use library arithmetic.

• 1. Locking

This section mainly relates to batch processing. Although a unit of code may perform well individually, it may slow down other processing due to the locks it takes out.

Always minimise the locks required. Take out locks as late as possible, on as few records as possible, and release them as soon as possible.

If not all records in a table need to be locked, only lock those that must be locked. Select a subset of records for update, or even lock them one at a time within an inner loop.

When using the for update clause in a cursor declaration, always identify the primary key column(s) of the table(s) that the share update lock is required on. Otherwise all queried records from all the tables are locked. Specify nowait if the code should not wait for lock to be released, but be sure to handle the exception that would be raised.

Example

cursor get_empl_using_dept (cp_deptno in number) is

select empl.empno

empl.ename

dept.dname

from empls empl

depts dept

where empl.deptno = dept.deptno

and dept.deptno = cp_deptno

for update of empl.empno;

This cursor locks the record in the EMPLS table only. If, alternatively, the final clause was:

for update;

…then records in both the EMPLS and DEPTS tables would be locked.

• 1. Transactional control – commit & rollback

PL/SQL that is called from on-line applications such as Oracle Forms or Oracle Reports should not use the commit or rollback statements. In these cases the calling program should control the commit or rollback.

PL/SQL batch and autonomous processes are the only processes stored on the database that have commit or rollback statements.

• 1. Application partitioning

PL/SQL blocks that access the data stored on the database should be stored on the database themselves, and not in client-side tools, such as Oracle Forms and Oracle Reports. All SQL and DML in client-side tools should be replaced with calls to packaged server-side procedures and functions.

This is an efficient implementation of PL/SQL and SQL in terms of network performance. It also encourages the re-use of code, which improves the performance of the SGA.

• 1. Pinning into memory

Packages, procedures and functions that are frequently called by many sessions should be considered as candidates for pinning into memory.

These program units should be identified to the DBA.

• 1. Deterministic functions and result_cache

Functions that should always return the same value given the same input parameter values may be better defined as deterministic functions or result_cache.

• 1. dbms_profiler

dbms_profiler is a very valuable tool for identifying which part or parts of a long-running or frequently executed program unit is/are performing poorly.

• 1. Automatic Workload Repository (AWR) reports

The DBA can supply AWR reports that identify the worst performing queries that should be looked at for opportunities to improve performance. These reports should be requested every month of two, or shortly after any major software release, on a proactive basis.

• 1. .APPENDIX B: Debugging

This section deals with some basic stuff. Nevertheless it is included for completeness.

• 1. Debugging uncompiled code

When a package/procedure/function is created/replaced and compiles with errors in SQL*Plus, the errors should be examined.

SQL> show errors [perhaps spool to a file?]

The listed errors should be investigated and corrected.

If the error specifies line 17 then look at the code lines in that area straightaway - before editing the program file.

SQL> list 15 – 20 [or SQL> list 15 – last]

Edit the file and correct the error!!!

• 1. Debugging running code

Examining variable values in running code

SQL> set serveroutput on

In the program unit write code to output the values

dbms_output.put_line('v_count = '||to_char(v_count));

The string is output to the SQL session once the package procedure has completed processing. Debug code must be removed from the production code or it should be disabled.

dbms_pipe is also useful for examining variables while a package is running and for establishing how far the processing has reached. Variable values can be passed between sessions.

• 1. .APPENDIX C: Mutating tables and Constraining tables

     1. Cause

“Mutating Table” and “Constraining table” errors occur when a trigger, or program unit called by a trigger, attempts to read or modify a record that is being manipulated by the current DML statement.

Example

An Oracle Form has a block based on the YEARS table, allowing insert of records.

A database trigger (After Row Insert) populates the MONTHS table with the 12 months for the YEAR during the insert process.

When fired, this trigger causes a mutating table error.

This is because as each record is inserted into MONTHS Oracle reads back across the foreign key to check the YEARS record exists. The error occurs because the row-level trigger processing on the YEARS record is not yet completed.

• 1. Solution

1) Create a Before Statement Insert trigger on table YEARS. (YEAR_BS_I)

Initialise a (package variable) record counter = 1.

Initialise a (package level) PL/SQL table with columns matching those in the key of YEARS.

2) Create an After Row Insert trigger on table YEARS. (YEAR_AR_I)

Write column values to the PL/SQL table in a row identified by the record counter.

Increment record counter by 1.

3) Create an After Statement Insert trigger on table YEARS. (YEAR_AS_I)

Read the PL/SQL table, in reverse order, starting at the row the counter identifies.

Write the MONTHS records matching the YEARS primary key.

Delete the row in the PL/SQL table.

Decrement record counter by 1.

Repeat until record counter = 1 again.

This solution relies upon the following Oracle features:

• • PL/SQL tables and variables defined at package-level can be read by multiple triggers because their scope is the session; and

  • After Statement triggers can write to detail tables and will not be susceptible to mutating table errors.

NB From Oracle 11 onwards these 3 triggers can be combined into a single compound trigger. This has not been done in RCS yet, but over time such triggers should be refactored into the new style.

End of Document