Donald K. Burleson Oracle Tips

Using VARRAY tables in Oracle

Before Oracle8, we would need to represent repeating groups in a table in a very clumsy and non-elegant fashion. Below is the syntax we use to create the table in Oracle7.

   create table employee (

       full_name               full_mailing_address_type,

       last_name                varchar(40),

       previous_employer_one    varchar(40),

       previous_employer_two    varchar(40),

       previous_employer_three  varchar(40)

);

We begin by creating a Oracle type to hold the repeating group of prior employers.

CREATE OR REPLACE TYPE
   employer_name
AS OBJECT
(e_name varchar(40))
;

CREATE OR REPLACE TYPE
   prior_employer_name_arr
AS
   VARRAY(10) OF employer_name;

Next, we create the employee type, embedding our varray of prior employers.

CREATE OR REPLACE TYPE employee AS OBJECT

(

   last_name               varchar(40),

   full_address            full_mailing_address_type,

   prior_employers         prior_employer_name_arr

);

Next, we create the emp table, using the employee type.

SQL> create table emp of employee;
Table Created.

Now we insert rows into the object table. Note the use of the full_mailing_address_type reference for the ADT and the specification of the repeating groups of previous employers.

insert into emp
values
(
   'Burleson',
   full_mailing_address_type('7474 Airplane Ave.','Rocky Ford','NC','27445'),
   prior_employer_name_arr(
      employer_name('IBM'),
      employer_name('ATT'),
      employer_name('CNN')
   )
);
insert into emp
values
(
   'Lavender',
   full_mailing_address_type('7474 Bearpond Ave.','Big Lick','NC','17545'),
   prior_employer_name_arr(
      employer_name('Oracle'),
      employer_name('Sybase'),
      employer_name('Computer Associates')
   )
);

Next, we perform the select SQL. Note that we can select all of the repeating groups with a single reference to the prior_employers column.

select
   p.prior_employers
from
   emp p
where
   p.last_name = 'Burleson';

PRIOR_EMPLOYERS(E_NAME)
--------------------------------------------------------------------------------
PRIOR_EMPLOYER_NAME_ARR(EMPLOYER_NAME('IBM'), EMPLOYER_NAME('ATT'), EMPLOYER_NAM
E('CNN'))

This output can be difficult to interpret because of the nature of the repeating groups. In the example below, we use a new BIF called table that will flatten-out the repeating groups, re-displaying the information.

column l_name     heading "Last Name"     format a20;

SELECT
   emp.last_name           l_name,
   prior_emps.*
FROM
   emp                      emp,
   table(p.prior_employers) prior_emps
WHERE
   p.last_name = 'Burleson';

Here we see a flattened output from the query, and the single information is replicated onto each table row.

Last Name            E_NAME
-------------------- ----------------------------------------
Burleson             IBM
Burleson             ATT
Burleson             CNN

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The DefinitiveReference” by Rampant TechPress. (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_1002_oracle_tuning_definitive_reference_2nd_ed.htm