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.
