PL/SQL Tip of the Month

From the PL/SQL Pipeline: http://www.revealnet.com/plsql-pipeline/index.htm

Did you know that you can create your own datatypes in Oracle7? Well, you can, sort of, by using the SUBTYPE statement.

Suppose you set as a rule that any primary key column in your application is defined as NUMBER(6). Now you could write code like this in your PL/SQL programs:

DECLARE
   my_id NUMBER(6);
BEGIN
...

but that would be such a bad idea. What if one day you needed to expand your primary key columns to NUMBER(7) or even NUMBER(10)? Why, you'd have to go back to all those programs and change the declarations. Yuch! Instead, take this approach: first, suppose you have a table named ORDER in which ORDER_ID is a "typical" primary key. Now, create a package containing the following SUBTYPE:

CREATE OR REPLACE PACKAGE app_types
IS

[Quoted]    SUBTYPE primary_key IS order.order_id%TYPE; END;
/

[Quoted] Then whenever you need to declare a variable of type "primary key", you do it like this:

DECLARE
   my_id app_types.primary_key;
BEGIN
...

Notice that you do not need to use the %TYPE attribute on the declaration. It already is a type. Withthis approach, if your primary key in ORDER or any other table needs to expand, you just change the table, recompile the package specification and nothing else needs to change in your PL/SQL code.

Best wishes,

Cam White
RevealNet, Inc. Received on Mon Dec 29 1997 - 00:00:00 CET