Oracle PL/SQL Best Practices

Name

DAT-02: Anchor variables to database datatypes using %TYPE and %ROWTYPE.

Synopsis

When you declare a variable using %TYPE or %ROWTYPE, you “anchor” the type of that data to another, previously defined element. If your program variable has the same datatype as (and, as is usually the case, is acting as a container for) a column in a table or view, use %TYPE to define it from that column. If your record has the same structure as a row in a table or view, use %ROWTYPE to define it from that table.

Example

Here is an example of a “hard-coded” declaration:

DECLARE
   l_title VARCHAR2(100);

I pretty clearly want to put a book title into this variable. And I checked the data dictionary and found that the title column is defined VARCHAR2(60). So that declaration seemed pretty safe. Unfortunately, two months later, the DBA expanded the column size to VARCHAR2(200)—and a month after that, my code started getting VALUE_ERROR exceptions. Bad news!

A much better approach is shown in the following declaration section:

DECLARE
   l_title book.title%TYPE;

Benefits

Your code automatically adapts to underlying changes in data structures. Whenever the data structure against which a declaration is anchored changes, the program containing the anchoring is marked INVALID. Upon recompilation, it automatically uses the new form of the data structure.

These declarations are “self-documenting”; a variable declaration tells anyone who reads it what kind of data this variable is supposed to hold.

Challenges

You need ...