Collection As Table Column Datatype

dbms_output

In PL/SQL,we can use both VARRAY & NESTED TABLE collection type as table column datatype.

The difference between these implementation is NESTED TABLE data is stored OUTLINE where VARRAY data is store INLINE with respect to relational table data.

VARRAY AS COLUMN DATATYPE :::

SQL> CREATE OR REPLACE TYPE Dependent_birthdate_t AS VARRAY (10) OF DATE;
  2  /

Type created.

SQL> CREATE TABLE employees1
  2  (
  3     id                NUMBER,
  4     name              VARCHAR2 (50),
  5     dependents_ages   Dependent_birthdate_t
  6  );

Table created.

--employees1 = OUTER TABLE
--Dependent_birthdate_t = INNER TABLE

SQL> INSERT INTO employees1 VALUES (42,'Zaphod Beeblebrox',Dependent_birthdate_t ('12-JAN-1765','4-JUL-1977','22-MAR-2021'));

1 row created.

SQL> INSERT INTO employees1 VALUES (43,'Pink Floyd',Dependent_birthdate_t ('12-JAN-1900','4-JUL-1978','22-MAR-3021'));

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM employees1;

        ID NAME               DEPENDENTS_AGES
---------- ------------------ -----------------------------------------------------------
        42 Zaphod Beeblebrox DEPENDENT_BIRTHDATE_T('12-JAN-65', '04-JUL-77', '22-MAR-21')
        43 Pink Floyduiuxzhd DEPENDENT_BIRTHDATE_T('12-JAN-00', '04-JUL-78', '22-MAR-21')

--This type of output is not very useful.You need to define a NESTED TABLE collection structure to actually access the VARRAY data meaningfully.

SQL> CREATE OR REPLACE TYPE Dependent_birthdate_t1 AS TABLE OF DATE;
  2  /

Type created.

SQL> SELECT CAST (dependents_ages AS Dependent_birthdate_t1) result FROM employees1;

RESULT
---------------------------------------------------------------
DEPENDENT_BIRTHDATE_T1('12-JAN-65', '04-JUL-77', '22-MAR-21')
DEPENDENT_BIRTHDATE_T1('30-MAR-99', '26-JUL-73', '01-JUL-15')

--COLUMN_VALUE is an access method for rows in a NESTED TABLE.

--THE enables a COLUMN_VALUE to be selected from a query of a NESTED TABLE.

SQL> SELECT COLUMN_VALUE
  2     FROM THE (SELECT CAST(dependents_ages AS
  3  Dependent_birthdate_t1)
  4  FROM employees1);
   FROM THE (SELECT CAST(dependents_ages AS
             *
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row

SQL> SELECT COLUMN_VALUE
  2     FROM THE (SELECT CAST(dependents_ages AS
  3  Dependent_birthdate_t1)
  4  FROM employees1
  5  WHERE id = 42);

COLUMN_VA
---------
12-JAN-65
04-JUL-77
22-MAR-21

SQL> SELECT COLUMN_VALUE
  2     FROM THE (SELECT CAST(dependents_ages AS
  3  Dependent_birthdate_t1)
  4  FROM employees1
  5  WHERE id = 43);

COLUMN_VA
---------
12-JAN-00
04-JUL-78
22-MAR-21

--A NESTED TABLE collection is built to mirror the element definition for the VARRAY. 

--NESTED TABLES are not upwardly bound as are VARRAY but can be used to temporarily hold the contents of VARRAY. Using a NESTED TABLE is the only way to meaningfully display the contents of a VARRAY using a select statement.

--You must ensure that your VARRAY is a mirror of your NESTED TABLE structure. If they are not data type mirrors, you will encounter an ORA-00932 error (inconsistent datatypes).The error complains that the source for the CAST is the wrong type to convert to a NESTED TABLE.

SQL> UPDATE employees1 SET dependents_ages = Dependent_birthdate_t('30-MAR-1964','26-JUL-1973','01-JUL-2015')
  2  WHERE id = 43;

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> SELECT COLUMN_VALUE result
  2     FROM THE (SELECT CAST(dependents_ages AS
  3  Dependent_birthdate_t1)
  4  FROM employees1
  5  WHERE id = 43);

RESULT
---------
30-MAR-64
26-JUL-73
01-JUL-15

SQL> UPDATE employees1 SET dependents_ages = Dependent_birthdate_t('30-MAR-1999','26-JUL-1973','01-JUL-2015')
  2  WHERE id = 43;

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> SELECT COLUMN_VALUE result
  2     FROM THE (SELECT CAST(dependents_ages AS
  3  Dependent_birthdate_t1)
  4  FROM employees1
  5  WHERE id = 43);

RESULT
---------
30-MAR-99
26-JUL-73
01-JUL-15

SQL> DELETE FROM employees1 WHERE id = 42;

1 row deleted.
SQL> CREATE TYPE first_names_t IS VARRAY (2) OF VARCHAR2 (100);
  2  /

Type created.

SQL> CREATE TYPE child_names_t IS VARRAY (1) OF VARCHAR2 (100);
  2  /

Type created.

SQL> CREATE TABLE family1
  2  (
  3     surname          VARCHAR2 (1000),
  4     parent_names     first_names_t,
  5     children_names   child_names_t
  6  );

Table created.

SQL> DECLARE
  2     parents    first_names_t := first_names_t ();
  3     children   child_names_t := child_names_t ();
  4  BEGIN
  5     parents.EXTEND (2);
  6     parents (1) := 'Samuel';
  7     parents (2) := 'Charina';
  8
  9     children.EXTEND;
 10     children (1) := 'Feather';
 11
 12     INSERT INTO family1 (surname, parent_names, children_names) VALUES ('Assurty', parents, children);
 13
 14     COMMIT;
 15  END;
 16  /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM family1;

SURNAME PARENT_NAMES                        CHILDREN_NAMES
------- ----------------------------------  -------------------------
Assurty FIRST_NAMES_T('Samuel', 'Charina')  CHILD_NAMES_T('Feather')

NESTED TABLE AS COLUMN DATATYPE :::

SQL> CREATE OR REPLACE TYPE color_tab_t9 IS TABLE OF VARCHAR2 (100);
  2  /

Type created.

SQL> CREATE TABLE Personality_inventory1
  2  (
  3     person_id         NUMBER,
  4     favorite_colors   color_tab_t9,
  5     date_tested       DATE,
  6     test_results      BLOB
  7  )
  8  NESTED TABLE favorite_colors
  9     STORE AS favorite_colors_st9;

Table created.

--Personality_inventory1 = OUTER TABLE
--color_tab_t9 = INNER TABLE
--favorite_colors_st9 = STORE TABLE

/*
The NESTED TABLE…STORE AS clause tells the database that I want the store table for the favorite_colors column to be called favorite_colors_st9. There is no preset limit on how large this store table, which is located "out of line" (or separate from the rest of that row’s data to accommodate growth) can grow.

You cannot directly manipulate data in the store table, and any attempt to retrieve or store data directly into favorite_colors_st9 will generate an error. The only path by which you can read or write the store table’s attributes is via the outer table.You cannot even specify storage parameters for the store table; it inherits the physical attributes of its outermost table.

One chief difference between nested tables and VARRAYs surfaces when you use them as column datatypes. Although using a VARRAY as a column’s datatype can achieve much the same result as a nested table, VARRAY data must be predeclared to be of a maximum size, and is actually stored "inline" with the rest of the table’s data. For this reason, Oracle Corporation says that VARRAY columns are intended for "small" arrays,and that nested tables are appropriate for "large" arrays.
*/

SQL> INSERT INTO Personality_inventory1 VALUES(1,color_tab_t9('AAA','BBB','CCC'),SYSDATE,NULL);

1 row created.

SQL> INSERT INTO Personality_inventory1 VALUES(2,color_tab_t9('DDD','EEE','FFF'),SYSDATE,NULL);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM Personality_inventory1;

 PERSON_ID FAVORITE_COLORS                    DATE_TEST TEST_RESULTS
--------- ---------------------------------------------- ------------
         1 COLOR_TAB_T9('AAA', 'BBB', 'CCC')  15-SEP-20         
         2 COLOR_TAB_T9('DDD', 'EEE', 'FFF')  15-SEP-20

--This type of output is not very useful.But unlike the VARRAY you implemented by casting to a NESTED TABLE, you can directly access NESTED TABLE in DQL.

SQL> SELECT COLUMN_VALUE
  2  FROM THE (SELECT favorite_colors
  3  FROM Personality_inventory1);
FROM THE (SELECT favorite_colors
          *
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row

SQL> SELECT COLUMN_VALUE
  2  FROM THE (SELECT favorite_colors
  3  FROM Personality_inventory1
  4  WHERE person_id = 1);

COLUMN_VALUE
-------------------------------------------
AAA
BBB
CCC

SQL> SELECT COLUMN_VALUE
  2  FROM THE (SELECT favorite_colors
  3  FROM Personality_inventory1
  4  WHERE person_id = 2);

COLUMN_VALUE
-------------------------------------------
DDD
EEE
FFF

SQL> SELECT * FROM favorite_colors_st9;
SELECT * FROM favorite_colors_st9
              *
ERROR at line 1:
ORA-22812: cannot reference nested table column's storage table

SQL> UPDATE Personality_inventory1 SET favorite_colors = color_tab_t9('AA','BB','CC') WHERE person_id = 1;

1 row updated.

SQL> UPDATE Personality_inventory1 SET favorite_colors = color_tab_t9('DD','EE','FF') WHERE person_id = 2;

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM Personality_inventory1;

 PERSON_ID FAVORITE_COLORS                 DATE_TEST     TEST_RESULTS
--------- ---------------------------------------------- ------------
         1 COLOR_TAB_T9('AA', 'BB', 'CC')  15-SEP-20         
         2 COLOR_TAB_T9('DD', 'EE', 'FF')  15-SEP-20
		 
SQL> UPDATE Personality_inventory1 SET favorite_colors = color_tab_t9('AA','BB','ZZ') WHERE person_id = 1;

1 row updated.

SQL> UPDATE Personality_inventory1 SET favorite_colors = color_tab_t9('DD','EE','YY') WHERE person_id = 2;

1 row updated.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM Personality_inventory1;

 PERSON_ID FAVORITE_COLORS                 DATE_TEST     TEST_RESULTS
--------- ---------------------------------------------- ------------
         1 COLOR_TAB_T9('AA', 'BB', 'ZZ')  15-SEP-20         
         2 COLOR_TAB_T9('DD', 'EE', 'YY')  15-SEP-20

SQL> DECLARE
  2     color_tab   color_tab_t9;
  3  BEGIN
  4     SELECT favorite_colors
  5       INTO color_tab
  6       FROM Personality_inventory1
  7      WHERE person_id = 1;
  8
  9     FOR element IN 1 .. color_tab.COUNT
 10     LOOP
 11        IF color_tab (element) = 'ZZ'
 12        THEN
 13           color_tab (element) := 'PP';
 14        END IF;
 15     END LOOP;
 16
 17     INSERT INTO Personality_inventory1
 18          VALUES (3, color_tab,SYSDATE,NULL);
 19
 20  COMMIT;
 21  END;
 22  /

PL/SQL procedure successfully completed. 
 
SQL> SELECT * FROM Personality_inventory1;

 PERSON_ID FAVORITE_COLORS                DATE_TEST   TEST_RESULTS
 --------- ------------------------------ ----------- -------------
 1         COLOR_TAB_T9('AA', 'BB', 'ZZ') 15-SEP-20
 2         COLOR_TAB_T9('DD', 'EE', 'YY') 15-SEP-20
 3         COLOR_TAB_T9('AA', 'BB', 'PP') 15-SEP-20

SQL> DELETE FROM Personality_inventory1;

3 rows deleted.
SQL> CREATE OR REPLACE TYPE color_tab_t10 IS TABLE OF VARCHAR2 (100);
  2  /

Type created.

SQL> CREATE TYPE auto_spec_t10 AS OBJECT
  2  (
  3     make VARCHAR2 (30),
  4     model VARCHAR2 (30),
  5     available_colors color_tab_t10
  6  );
  7  /

Type created.

SQL> CREATE TABLE auto_specs10 OF auto_spec_t10
  2  NESTED TABLE available_colors
  3  STORE AS available_colors_st10;

Table created.

--When you create a "table of objects," the database looks at the object type definition to determine what columns you want. When it discovers that one of the object type’s attributes, available_colors, is in fact a nested table, it wants to know what to name the store table. So the phrase: ...NESTED TABLE available_colors STORE AS available_colors_st10 says that you want the available_colors column to have a store table named available_colors_st10.

SQL> INSERT INTO auto_specs10 VALUES('ORACLE','11G',color_tab_t10('AAA','BBB','CCC'));

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM auto_specs10;

MAKE                           MODEL     AVAILABLE_COLORS
-------------------------- ------------ --------------------------------------
ORACLE                         11G       COLOR_TAB_T10('AAA', 'BBB', 'CCC')

Nested tables are not constrainable by definition whereas varrays are.

Nested tables as column values do not let you use a NOT NULL constraint. When you attempt to use a table type in a table definition and set the column constraint to NOT NULL, it will raise an ORA-02331 error.

You are storing a table that is only referenced through the parent table. Placing a NOT NULL column constraint is inconsistent with a nested table type.

SQL> CREATE TABLE Personality_inventory2
  2  (
  3     person_id         NUMBER,
  4     favorite_colors   color_tab_t9 NOT NULL,
  5     date_tested       DATE,
  6     test_results      BLOB
  7  )
  8  NESTED TABLE favorite_colors
  9     STORE AS favorite_colors_st2;
   favorite_colors   color_tab_t9 NOT NULL,
                                  *
ERROR at line 4:
ORA-02331: cannot create constraint on column of datatype Named Table Type

SQL> CREATE TABLE family2
  2  (
  3     surname          VARCHAR2 (1000),
  4     parent_names     first_names_t NOT NULL,
  5     children_names   child_names_t
  6  );

Table created.

Shoumadip Das

Hi Folks, I am Shoumadip Das hailing from Kolkata, India welcoming you to explore my blog www.oraclemasterpiece.com. I am a self motivated and successful professional working in a leading IT Giant for more than 10 years.My area of expertise includes –

  • Oracle Database Programming (SQL & PL/SQL)
  • Perl Programming
  • Unix Programming

Read More

One thought on “PL/SQL Collection Methods”

  1. When you pass an associative array as a parameter to a remote database using a database link, the two databases can have different globalization settings. When the remote database uses a collection method such as An array has a declared number of elements, but a nested table does not. The size of a nested table can increase dynamically (however, a maximum limit is imposed see Referencing Collection Elements ).

Leave a Reply

Your email address will not be published. Required fields are marked *