Oracle PL/SQL Best Practices

Name

DAT-03: Use SUBTYPE to standardize application-specific datatypes.

Synopsis

The SUBTYPE statement allows you to create “aliases” for existing types of information, in effect creating your own specially named datatypes. Use SUBTYPE when you want to standardize on a set of named datatypes that aren’t anchorable back to the database.

Example

Suppose that my book table has a page_count column, defined as INTEGER(4). I then write a program that calculates the total number of pages I have written across all books. If I declare a variable to hold this value as:

DECLARE
   l_total book.page_count%TYPE;

I could run into problems. My total count might exceed four digits. In fact, I may well not have any database column I can use for anchoring in this case. Yet, I still should not hard-code a declaration like this:

DECLARE
   l_total INTEGER(10);

Instead, I will create a package and define a variable there that is big enough to hold the total count:

CREATE OR REPLACE PACKAGE book_data
IS
   SUBTYPE total_count_t IS INTEGER (10);

and then use that in my declaration section:

DECLARE
   l_total book_data.total_count_t;

Tip

If you use Oracle7 or Oracle8, the SUBTYPE statement just shown will fail; Oracle doesn’t recognize constrained SUBTYPEs until Oracle8i. In this case, you can do the following:

CREATE OR REPLACE PACKAGE book_data
IS
 total_count INTEGER(10);
 SUBTYPE total_count_t IS total_count;

Benefits

You standardize or “normalize” all datatype definitions. In other words, any definition appears only once ...