Oracle 9i PL/SQL Collections Page - 2

Creating Nested Tables

The following example explain how to create a table type and then nest it in a table

Following syntax is used to display columns of the above table we write.

SQL > SELECT COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLUMNS;
SQL > SELECT TYPECODE, ATTRIBUTES FROM USER_TYPES;

TYPECODE ATTRIBUTES
------------------------------ ----------
OBJECT 2
COLLECTION 0

To insert values into the above table we use the below given syntax

To query a nested table Oracle provides a new function THE. To select data from a field in the nested table we must have to flatten the table first. Function THE is used for this purpose.

{mosgoogle)

Varrays:

Is short form for variable sized arrays. They are used to store repeating attributes in a table. Varying arrays can be created based on standard datatypes of Oracle or by using abstract datatypes.
A varying array take fixed number of elements when it is created, which can be changed at runtime. The subscript used by Varrays is sequential. We have to decide the maximum number of elements for the varying array.

Creating VARRAYS

Below given example explains creation and usage of VARRYS. Let us consider an organization developing multiple projects simultaneously. Each project has few team members and these members report to a team leader. We will have a varying array for storing members’ names. Another table is created for storing the project details.

To describe table we use

SQL > DESC PROJECT
Name Null? Type
----------------------------------------- -------- ----------------
PROJID NUMBER (5)
PROJNAME VARCHAR2 (25)
TEAMLEAD VARCHAR2 (20)
TEAMMEMBERS MEMBERS_VA

We use the following query to see the datatype of MEMBERS_VA

Inserting Records into Varying Arrays.

Associative arrays (or) Index-by tables:

They are sets of key-value pairs. The keys are maintained unique. The key can be either an integer or a string. The unique key is created using primary key from a table. The types raw , longraw , rowid etc are not allowed as keys.

Creating Associative Arrays