VARRAY and NESTED TABLES

Nested Table
So, what is the difference between an Associative Table and Nested Table? A nested table looks a lot like an associative table. The main difference in the declaration is the missing INDEX BY keywords.
If we go back to our first example in part 1, we can modify it easily to use a nested table instead:
DECLARE
TYPE a_char_data IS TABLE OF VARCHAR2(10);
v_char_data a_char_data := a_char_data();
BEGIN
v_char_data.EXTEND;
v_char_data(1) := 'ABC';
v_char_data.EXTEND;
v_char_data(2) := 'DEF';
v_char_data.EXTEND;
v_char_data(3) := '1234567890';
v_char_data.EXTEND;
v_char_data(4) := 'X+Y=Z';
v_char_data.EXTEND;
v_char_data(5) := 'DE NADA';
FOR i IN v_char_data.FIRST..v_char_data.LAST LOOP
dbms_output.put_line(v_char_data(i));
END LOOP;
END;
/
There are three changes to note here.
· The TYPE declaration does not include the INDEX BY keyword.
· Notice the variable declaration for v_char_data now includes a call to a Nested Table CONSTRUCTOR.
· And finally, I've introduced the EXTEND method. EXTEND adds a new, empty element to a nested table. You must call EXTEND before referencing an element or you will get an error.
VARRAY
So, what is the difference between a Nested Table and a Varray? Well, here is that same example using a Varray.
DECLARE
TYPE a_char_data IS VARRAY(5) OF VARCHAR2(10);
v_char_data a_char_data := a_char_data();
BEGIN
v_char_data.EXTEND;
v_char_data(1) := 'ABC';
v_char_data.EXTEND;
v_char_data(2) := 'DEF';
v_char_data.EXTEND;
v_char_data(3) := '1234567890';
v_char_data.EXTEND;
v_char_data(4) := 'X+Y=Z';
v_char_data.EXTEND;
v_char_data(5) := 'DE NADA';
FOR i IN v_char_data.FIRST..v_char_data.LAST LOOP
dbms_output.put_line(v_char_data(i));
END LOOP;
END;
/
Not a lot of differences there. To declare a Varray, you just replace the TABLE OF keywords with VARRAY(size limit). In this case, I made it a maximum length of 5. What would happen if I made it 4 and then tried to add 5 elements? You get an error very much like you would if you didn't use Extend. Bascially, the element does not exist.
Varrays and Nested Tables in SQL
Associative tables cannot be used in SQL. Both Varrays and Nested tables can. There are two ways you can use collections in SQL.
Arrays in Tables
You can declare a column in a table as either like this example:
CREATE OR REPLACE TYPE addresses_v IS VARRAY(3) OF VARCHAR2(30);
/
CREATE OR REPLACE TYPE addresses_n IS TABLE OF VARCHAR2(30);
/
CREATE TABLE person (
name VARCHAR2(30),
dob DATE,
address_v addresses_v,
address_n addresses_n )
NESTED TABLE address_n STORE AS address_nested
/
SQL> INSERT INTO person (name, dob, address_v, address_n)
2 VALUES ('Lewis', to_date('01-jan-1960', 'dd-mon-yyyy'),
3 addresses_v('Line 1', 'Line 2', 'Line 3' ),
4 addresses_n('Line 1', 'Line 2', 'Line 3' )
5 )
6 /
1 row created.
SQL>
SQL> SELECT * FROM person
2 /
NAME DOB
------------------------------ ---------
ADDRESS_V
------------------------------------------------------------------
ADDRESS_N
------------------------------------------------------------------
Lewis 01-JAN-60
ADDRESSES_V('Line 1', 'Line 2', 'Line 3')
ADDRESSES_N('Line 1', 'Line 2', 'Line 3')
SQL>
SQL> UPDATE TABLE(select address_n from person)
2 SET column_value = 'Line #2'
3 WHERE column_value = 'Line 2'
4 /
1 row updated.
SQL>
SQL> SELECT * FROM person
2 /
NAME DOB
------------------------------ ---------
ADDRESS_V
------------------------------------------------------------------
ADDRESS_N
------------------------------------------------------------------
Lewis 01-JAN-60
ADDRESSES_V('Line 1', 'Line 2', 'Line 3')
ADDRESSES_N('Line 1', 'Line #2', 'Line 3')
SQL>
SQL> UPDATE person
2 SET address_v = addresses_v('Line 3', 'Line 2', 'Line 1' )
3 /
1 row updated.
SQL>
SQL> SELECT * FROM person
2 /
NAME DOB
------------------------------ ---------
ADDRESS_V
------------------------------------------------------------------
ADDRESS_N
------------------------------------------------------------------
Lewis 01-JAN-60
ADDRESSES_V('Line 3', 'Line 2', 'Line 1')
ADDRESSES_N('Line 1', 'Line #2', 'Line 3')
SQL>
Tables from Arrays
You can also use a collection in your SQL like this:
CREATE TABLE data_and_stuff (
name VARCHAR2(30),
dob DATE )
/
INSERT INTO data_and_stuff values ('LEWIS', sysdate - 1 );
INSERT INTO data_and_stuff values ('DAVID', sysdate - 2 );
INSERT INTO data_and_stuff values ('EILEEN', sysdate - 3 );
INSERT INTO data_and_stuff values ('ERICK', sysdate - 4 );

CREATE OR REPLACE TYPE a_data IS TABLE OF VARCHAR2(30);
/
SQL> set serveroutput on size 200000
SQL> DECLARE
2 v_data a_data := a_data( 'TIM', 'DAVID', 'ERICK', 'LEWIS' );
3 BEGIN
4 FOR c1 IN (
5 SELECT * FROM TABLE(CAST( v_data AS a_data)) ) LOOP
6 DBMS_OUTPUT.PUT_LINE('Looking for values: ' c1.column_value);
7 END LOOP;
8 DBMS_OUTPUT.PUT_LINE('Begin search: ' );
9 FOR c1 IN (
10 SELECT *
11 FROM data_and_stuff
12 WHERE name IN (
13 SELECT * FROM TABLE(CAST(v_data AS a_data)) ) ) LOOP
14 DBMS_OUTPUT.PUT_LINE(c1.name ' was born on '
15 to_char( c1.dob, 'DD-MON-YYYY' ) );
16 END LOOP;
17 END;
18 /
Looking for values: TIM
Looking for values: DAVID
Looking for values: ERICK
Looking for values: LEWIS
Begin search:
DAVID was born on 30-SEP-2005
ERICK was born on 28-SEP-2005
LEWIS was born on 01-OCT-2005

PL/SQL procedure successfully completed.
Using the above logic, you can allow a parameter to a function as a nested table and use it directly in your SQL. This is a very powerful concept.
When to use which
Now I guess we should talk about when to use a particular type of collection. In PL/SQL, for the most part, it's a matter of preference. If you know you have a maximum, use VARRAY. The choice between Nested Table and Associative Table is not as clear cut. In general, I use Nested Table instead of Associative Tables unless I am indexing the array with a VARCHAR2.
In SQL, it's a different subject. Of course, you cannot use an Associative table at all in SQL so your choices are down to Nested Table or Varray. I rarely, if ever, use nested tables or varrays in a relational table. I prefer to create a normal table and use foreign keys.