Oracle collections are database types that allow you to store sets of elements, similar to arrays and collections in Java. Storing data in collections is closer to the object oriented paradigm. The data stored in an embedded collection may be accessed faster by the database than if stored in two separate tables. Oracle database has 3 types of collections, which are varrays, nested tables and associative arrays. A varray is an ordered set of elements with index, similar to an array in Java. Even if you only want to modify one element, you must supply all the elements for the varray. A maximum size is set for the varray when creating it, and adding more elements will result in exception; but you can change the size any time. A nested table is a table that is embedded within another table, and you can insert, update, and delete individual elements in a nested table. A nested table array is a special case of nested table with only one column. An associative array is a set of key and value pairs, similar to a HashMap in Java. You can get value from the arrays using the key or index (position). Here, we will discuss varray and nested table array which are treated almost similarly, even by JDBC programs.

Creating, altering and deleting a VARRAY type

You can create a varray type using CREATE TYPE statement and then use it to define a column or variable.

You can create a varray type as:

CREATE TYPE varray_type AS VARRAY(3) OF VARCHAR2(50);

You can alter a varray type’s size using ALTER TYPE as:

ALTER TYPE varray_type MODIFY LIMIT 5 CASCADE;

NOTE: The CASCADE option propagates the change to any dependent objects in the database.

You can drop a varray type as:

DROP TYPE ARRAY_TABLE;

Note: You cannot drop or replace a type with type or table dependents present.

Using VARRAY type in a table

You can use the varray type to define a column in a table as:

CREATE TABLE empvarray (

  emp_id INTEGER,

  emp_skills varray_type

);

Inserting Data into a table with a VARRAY type

You can insert data to a table (e.g. empvarray) that contains a varray type (e.g. varray_type) as:

insert into empvarray values (1, varray_type ('java', 'sql'));

Getting Information about a VARRAY

A table definition doesn’t tell you about the type of a varray. In SQL Plus, you can find the details about a varray using DESCRIBE keyword as:

DESCRIBE varray_type;

You can also do the same for a table in SQL Plus as:

DESCRIBE empvarray;

The system table user_varrays contains all user defined varrays and you can query it as:

SELECT * FROM user_varrays

Or

SELECT parent_table_name, parent_table_column, type_name FROM user_varrays

Creating a Nested Table Array using ‘AS TABLE OF <type>’ syntax

Nested table array is a table with only one column. It is similar to a varray except that it doesn’t have an upper bound like varray. We can create a nested table array of one column type using the ‘AS TABLE OF’ syntax as:

CREATE TYPE array_table AS TABLE OF VARCHAR2 (50);

CREATE TYPE array_int AS TABLE OF NUMBER;       

Note that here we don’t specify the size and hence there is no upper bound for a nested table array, unlike VARRAY.

You can alter or drop using ALTER and DROP commands respectively similar to VARRAY.

Note: You cannot drop or replace a type with type or table dependents

Using Nested Table Array (AS TABLE OF) type within a table

Unlike VARRAY type, we need to have an additional ‘NESTED TABLE ... STORE AS ...’ clause while creating tables with nested table arrays.

CREATE TABLE empnested (

  emp_id INTEGER,

  emp_skills array_table

)

NESTED TABLE

emp_skills

STORE AS

array_table1;

array_table1 can be any name and there should not be any object/table with that name.

Without the nested clause, you will get error as:

[Error Code: 22913, SQL State: 99999]  ORA-22913: must specify table name for nested table column or attribute

Inserting Data into a table with a VARRAY type

INSERT INTO empnested values (1, array_table ('java', 'sql'));

Note that array_table is the array type and not the column name.

Retrieving data from VARRAY and Nested table  array (AS TABLE OF)

Simply trying to do a select * (e.g. Select * from empvarray) from a JDBC client like DBVisualizer will give null value for the VARRAY type/Nested table type. However you can see all entered values when you do a select * from statement in SqlPlus:

    EMP_ID

----------

EMP_SKILLS

--------------------------

 

         1

VARRAY_TYPE('java', 'sql')

JDBC program to retrieve data from an Oracle table with VARRAY and Nested table array (AS TABLE OF) types

import java.sql.Array;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.Statement;

 

public class JDBCArrayClient {

  public static void passArray() {

    try {

 

      Class.forName("oracle.jdbc.OracleDriver");

      // Need to modify conn string, username and password as per your oracle installation.

      Connection con = DriverManager.getConnection(

          "jdbc:oracle:thin:@<hostname>:<port>:<sid>", "username", "password");

      Statement stmt = con.createStatement();

      ResultSet rs = stmt

          .executeQuery("SELECT emp_id, emp_skills from empnested");

      while (rs.next()) {

        Array e = rs.getArray("emp_skills");

        String[] skills = (String[]) e.getArray();

        System.out.println("Skills");

        for (int i = 0; i < skills.length; i++) {

          System.out.println(skills[i]);

        }

      }

    } catch (Exception e) {

      System.out.println(e);

    }

  }

 

            public static void main(String args[]) {

                        passArray();

            }

}