Working with VARRAYs in Oracle Part I

This article is the first in a three part series that will take
you through the process of creating VARRAYs and dealing with them in Oracle
tables.

If you were a strict normalization geek, you would definitely
venture down the track we are going to take. However, if you adhere to object
technology you will more than likely enjoy this article. This article is going
to introduce you to the creation of an abstract data type, or object that can
be used within the typical table structure within Oracle. The caveat of this is
that we are also going to introduce an array in our table that will make you
cringe because we are introducing a repeating field, or object, within the
table that goes against all normalization practices. Before you throw this
article out or stop reading it, there are a few good reasons why you may want
to consider putting this technique in your bag of tricks.

  1. By in-lining the repeated fields (object) in the table, you remove the
    reliance on creating another table with its own structure and indexes to worry
    about.

  2. You do not have to join to another table just to get a set of related
    fields since they are stored in the table already.

  3. The abstracted data type (object) can be reused by other tables or objects
    that forces designers to adhere to a standard for the columns in the abstracted
    object.

An Abstract Data Type

As stated briefly, an abstracted data type is an object type
that groups common columns together. In our example, we have an object that is
designed to hold a gas log for a fleet system. The gas log is made up of the
number of gallons of gas we have pumped, the date we filled up, and the
particular gas station used. Obviously, we could add things but this is a
simple case and I hope that you get the picture presented here. We will use
this object when defining and relating a gas log to a particular vehicle later
on. The interesting point to note here is that this object could be used by
another table. For example, what if our fleet of vehicles were also used to
fill the tanks at our local gas stations. We could then also use this object
and add it to a table called STATION_FILL_SCHEDULE to distinguish the number of
gallons deposited, when the tanks were filled, and the gas station. This is of
great use since we gain reusability and commonality of field definitions. To
create this new object you would issue the following DDL:


CREATE TYPE GAS_LOG_TY AS OBJECT (
GALLONS NUMBER,
FILLUP_DATE DATE,
GAS_STATION VARCHAR2(255));

The Array

The GAS_LOG_TY object previously created will easily stand
on its own and can be added to a table. However, we want to track the last 100
times a vehicle was filled with gas in our fleet system. We do this be
providing an array structure that will hold 100 of the gas log object. Here is
the DDL to accomplish that.

CREATE TYPE GAS_LOG_VA AS VARRAY(100) OF GAS_LOG_TY;

The Table

Now that we have the gas log object created and the array of
gas log, all we need to do is issue the DDL that you and I are already familiar
with. To note, we are tracking by the vehicle ID number. Also note that the
column GAS_LOG has a column type of the array we just defined GAS_LOG_VA.


CREATE TABLE GAS_LOG
(VIN NUMBER NOT NULL,
GAS_LOG GAS_LOG_VA);

Describing the structure

If you are familiar with the SQL*Plus DESCRIBE command, you
can get the full description of this newly created table with little more
effort than before. Following is the sequence as depicted in Listing 1.

  1. First
    describe the table GAS_LOG and you will note the column type GAS_LOG_VA

  2. You
    can then describe the column type GAS_LOG_VA to show that it is in fact a
    VARRAY of 100 entries of the GAS_LOG_TY object.

  3. Describing
    the GAS_LOG_TY object does not provide any further information in this example
    but is practice just to get the full picture.

Listing 1.
Describing the abstracted objects


SQL> DESCRIBE gas_log
Name Null? Type
—————————————– ——– ——————
VIN NOT NULL NUMBER
GAS_LOG GAS_LOG_VA

SQL> DESCRIBE GAS_LOG_VA
GAS_LOG_VA VARRAY(100) OF GAS_LOG_TY
Name Null? Type
—————————————– ——– ——————
GALLONS NUMBER
FILLUP_DATE DATE
GAS_STATION VARCHAR2(255)

SQL> DESCRIBE GAS_LOG_TY
Name Null? Type
—————————————– ——– ——————
GALLONS NUMBER
FILLUP_DATE DATE
GAS_STATION VARCHAR2(255)

How to Drop the Structure

In order to drop the newly created structure, you should
begin dropping in the reverse order from which you created them. If you try and
drop the underlying object types you will get an ORA-02303 which tells you that
you cannot drop or replace a type with type or table dependents. Here is the
proper sequence to drop the above structures.


DROP TABLE GAS_LOG;
DROP TYPE GAS_LOG_VA;
DROP TYPE GAS_LOG_TY;

James Koopmann
James Koopmann
James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Latest Articles