Oracle PL/SQL Best Practices

Name

DAT-13: Centralize TYPE definitions in package specifications.

Synopsis

As you use more and more of the PL/SQL language features, you will define many TYPEs of things, including:

• SUBTYPEs that define application-specific datatypes

• Collection TYPEs, such as lists of numbers, dates, or records

• Referenced cursor TYPEs, from which cursor variables are declared

Some of these TYPEs can be used unchanged throughout your application (there is only one way, for example, to declare an index-by table of dates); other types are specific to some part of an application but are standard within that.

In either case, create a package to hold these standard TYPEs, so that they can be used in multiple programs.

Example

Here is a portion of a package specification that contains standard TYPE statements for nested and index-by tables:

CREATE OR REPLACE PACKAGE colltype
IS
   TYPE boolean_ntab IS TABLE OF BOOLEAN;
   
   TYPE boolean_ibtab IS TABLE OF BOOLEAN
    INDEX BY BINARY_INTEGER;

   TYPE date_ntab IS TABLE OF DATE;
   
   TYPE date_ibtab IS TABLE OF DATE
    INDEX BY BINARY_INTEGER;
   ...
END colltype;

Benefits

Developers write their code more rapidly and with fewer bugs by relying on predefined TYPEs.

As you need to maintain your TYPEs (those based on application-specific elements are, after all, very likely to change), you go to one package and make the change in one place.

Challenges

Developers must be disciplined enough to seek out predefined TYPEs or to add new TYPEs to existing packages.

Resources

colltype.pks : A package ...