This code snippet demonstrates how to use Oracle’s VARRAY through JDBC. The code:

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 = "";    // Connect to the database    Connection conn =      DriverManager.getConnection (url, "" , "");    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
See also  Why ChatGPT Is So Important Today