Working with VARRAYs in Oracle Part I
March 4, 2004
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.
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 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;
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.
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;