Use Oracle's VARRAY Through JDBC

1. Creates VARRAYs of different lengths.
2. Contains a table to store data of above array type.
3. Stores the objects in an array.
Inserts them into database table in two different ways (with SQL and ArrayDescriptor).

import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.oracore.Util;
import oracle.jdbc.*;


public class VArrayManipulation
{
  public static void main (String args[])
    throws Exception
  {
    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());

    // The sample retrieves an varray of type "STRING_VARRAY",
    // materializes the object as an object of type ARRAY.
    // A new ARRAY is then inserted into the database.

    String url = "<connection url>";

    // Connect to the database
    Connection conn =
      DriverManager.getConnection (url, "<user>" , "<password>");

    conn.setAutoCommit (false);

    // Create a Statement
    Statement stmt = conn.createStatement ();

    try
    {
      stmt.execute ("DROP TABLE sample_varray_table");
      stmt.execute ("DROP TYPE string_varray");
    }
    catch (SQLException e)
    {
        //Exceptions will be thrown if Table and types doesnt exist . Ignore this
    }

    stmt.execute ("CREATE TYPE string_varray AS VARRAY(10) OF VARCHAR2(100)");
    stmt.execute ("CREATE TABLE sample_varray_table (acol string_varray)");

    //Insert using SQL

    stmt.execute ("INSERT INTO sample_varray_table VALUES (string_varray('Test1', 'Test2'))");

    ResultSet rs = stmt.executeQuery("SELECT acol FROM sample_varray_table");
    printResultSet (rs);

    //Insert using ArrayDescriptor

    // create a new ARRAY object
    String arrayElements[] = { "Test3", "Test4" };
    ArrayDescriptor desc = ArrayDescriptor.createDescriptor
                                           ("STRING_VARRAY", conn);
    ARRAY newArray = new ARRAY(desc, conn, arrayElements);

    PreparedStatement ps =
      conn.prepareStatement ("insert into sample_varray_table values (?)");
    ((OraclePreparedStatement)ps).setARRAY (1, newArray);

    ps.execute ();

    rs = stmt.executeQuery("SELECT acol FROM sample_varray_table");
    printResultSet (rs);

    // Close all the resources
    rs.close();
    ps.close();
    stmt.close();
    conn.close();

  }

  public static void printResultSet (ResultSet rs)
    throws SQLException
  {
    System.out.println("********Fetch Starts....********");
    int line = 0;
    while (rs.next())
    {
      line++;
      System.out.println("********Row "+line+" : ");
      ARRAY array = ((OracleResultSet)rs).getARRAY (1);

      System.out.println ("Array is of type "+array.getSQLTypeName());
      System.out.println ("Array is of length "+array.length());

      // get Array elements
      String[] values = (String[]) array.getArray();

      for (int i=0; i<values.length; i++)
      {
        System.out.println("index "+i+" = "+values[i] );
      }
    }
    System.out.println("********Fetch Ends....********");
  }
}