16.4 How do I…Create and use the VARRAY collection type?
Problem
I have a one-to-many relationship involving a static number of elements on the “many” side. Unlike traditional relational designs, the order of these elements is significant. How do I use VARRAYs to model this type of relationship?
Technique
VARRAYs are perfect for modeling one-to-many relationships where the maximum number of elements on the “many” side of the relationship is known and where the order of these elements is important. In object-oriented parlance, these characteristics describe an ordered collection. Suppose that you track the monthly average database buffer cache hit rate for the Oracle8 instances comprising your enterprise. It’s not difficult to model the relationship between the instance name and the monthly hit rate data using VARRAYs. There are also some limitations to consider when you use SQL DML statements to reference VARRAYs.
Steps
1. Start SQL*Plus, connect as the WAITE user, and use the START command to load and execute the script CHP16_11.SQL. The script and its output appears in Figure 16.11.
The first statement creates a VARRAY type called DBHIT_TYPE16, which consists of 12 elements of datatype NUMBER(6,2). Each element stores the database buffer cache hit rate for one month. The second SQL statement creates a table called DBHIT_RATE16, which contains an embedded object called MONTHLY_RATES (see line 3) that is a VARRAY of type DBHIT_TYPE16.
2. Insert two rows into the dbhit_rate16 table by using the START command to load and execute the script CHP16_12.SQL as shown in Figure 16.12.
Notice that each insert statement uses the system-generated constructor for the VARRAY called dbhit_type16. Also, the first insert statement only inserts ten months of data; the second inserts 12 months, but two of the VARRAY elements are null.
3. Execute the anonymous PL/SQL procedure in CHP16_13.SQL to query the DBHIT_RATE16 table. The procedure appears in Listing 16.2.
Listing 16.2 A script to create a table called DBHIT_RATE16
SQL> DECLARE
2 CURSOR dbhit_rate16_c1 IS
3 SELECT instance_name, monthly_rates
4 FROM dbhit_rate16;
5 BEGIN
6
6 FOR hit_rec IN dbhit_rate16_c1
7 LOOP
8 dbms_output.put_line(‘instance_name: ‘ ||
hit_rec.instance_name);
9 for i in 1 .. hit_rec.monthly_rates.count
10 LOOP
11 dbms_output.put_line(to_char(i) || ‘ ‘ ||
12
to_char(hit_rec.monthly_rates(i)));
13 END LOOP;
14 END LOOP;
15
15 END;
The procedure uses a FOR loop in lines 6 through 14 to cycle through the DBHIT_RATE16 table. Line 8 contains code to print the instance name and lines 9 through 13 print the value of the database buffer cache hit rate from the VARRAY of type DBHIT_TYPE16. The FOR loop construct takes care of the record declaration and the loop exit criteria. The script addresses the individual elements of the VARRAY using a subscript in line 12. The output from this procedure appears in Figure 16.13.
How It Works
Steps 1 and 2 feature scripts to create a VARRAY collection type, embed an object of this type in a table, and finally populate the table. Step 3 exhibits some PL/SQL code to query the VARRAY.
Comments
Currently no alternative exists for the PL/SQL code in Step 3. Within standard SQL, you cannot query tables containing VARRAYs. Attempts to do so under Oracle8 version 8.02 beta for Windows NT generate the following error messages:
SQL*Plus internal error state 2231, context 0:0:0
Unsafe to proceed
There are obvious normalization questions here, too. It looks like VARRAYs contain repeating groups and so they violate first normal form. Oracle considers VARRAYs as atomic units, and thereby addresses the normalization concerns as well as explaining the lack of SELECT support.
The extra PL/SQL code aside, VARRAYs can eliminate some of the overhead associated with joining tables involved in one-to-many relationships. Unless the size of a VARRAY exceeds four kilobytes, VARRAYs are stored inline, meaning within the same segment containing data from the same table, so the overhead associated with querying and manipulating them is low. If SELECT capability or index support is a necessity, or if there can be no upper limit on the number of elements, then nested tables are a better choice. Nested tables are the subject of the next How-To.