Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

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

 

 

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 
 
email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.