Oracle PL/SQL Best Practices

Name

SQL-21: Soft-code the maximum length of columns in DBMS_SQL.DEFINE_COLUMN calls.

Synopsis

When you call DBMS_SQL.DEFINE_COLUMN to define a VARCHAR2 column, you must provide the maximum length of the string that will be passed back to your program. Ideally, we’d use an attribute like %COLLEN to automatically draw that value from the data dictionary. There is, unfortunately, no such attribute. As a consequence, we usually sigh and hard-code a maximum length.

Rather than do that, create a package specification and place all column lengths you need to reference there. This way, if those lengths change, you can update just the one package. You can also generate this package specification directly from the data dictionary (see Section ).

Example

I create a “column length” package:

CREATE OR REPLACE PACKAGE collen
IS
   city CONSTANT INTEGER := 15;
   state CONSTANT INTEGER := 2;
END collen;

And I now reference those constants whenever I call DBMS_SQL.DEFINE COLUMN:

DBMS_SQL.DEFINE_COLUMN (
   cursor_handle, 1, city, collen.city);

DBMS_SQL.DEFINE_COLUMN (
   cursor_handle, 2, state, collen.state);

Benefits

You avoid hard-coding column lengths. If a column length changes, you update the value only in the package of named constants.

Challenges

You have to build and maintain the column length package(s). Code generation will make the difference here.

Resources

genlenpkg.pro : A program that generates the column length package for the specified table (VARCHAR2 columns only). Here’s an example of the output ...