Inserting Or Retrieving A Varray of Strings With Jdbc Driver Produces Empty Strings (Doc ID 551167.1)

Last updated on SEPTEMBER 01, 2014

Applies to:

JDBC - Version 10.2.0.3 to 11.2.0.3.0 [Release 10.2 to 11.2]
Information in this document applies to any platform.
This problem can occur on any platform.

Symptoms

JDBC application inserting VARRAY of VARCHAR works on some databases but not on others.

When the issue occurs, the VARRAY is inserted with the correct number of elements but each element is null.

Here is a simple demo adapted from a demo provided with the standard JDBC demos:

import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.OracleDataSource;



public class ArrayExample2
{
  public static void main (String args[])
    throws Exception
  {
    OracleDataSource ods = new OracleDataSource();

    String url = "jdbc:oracle:thin:scott/tiger@host:1521:ORCL";
    ods.setURL(url);

    // Connect to the database
    Connection conn = ods.getConnection();

    // It's faster when auto commit is off
    conn.setAutoCommit (false);

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

    try
    {
      stmt.execute ("DROP TABLE varray_table");
      stmt.execute ("DROP TYPE char_varray");     
    }
    catch (SQLException e)
    {
      // the above drop statements will throw exceptions
      // if the types and tables did not exist before. Just ingore it.
    }
 
    stmt.execute ("CREATE TYPE char_varray AS VARRAY(5) OF VARCHAR2(10)");
    stmt.execute ("CREATE TABLE varray_table (col1 char_varray)");
    stmt.execute ("INSERT INTO varray_table VALUES (char_varray('first', 'second'))");

    ResultSet rs = stmt.executeQuery("SELECT col1 FROM varray_table");
    showResultSet (rs);

    //now insert a new row

    // create a new ARRAY object    
    String elements[] = { "third", "fourth", "fifth", "sixth" };
    ArrayDescriptor desc = ArrayDescriptor.createDescriptor
                                           ("CHAR_VARRAY", conn);
    ARRAY newArray = new ARRAY(desc, conn, elements);
    
    PreparedStatement ps = 
      conn.prepareStatement ("insert into varray_table values (?)");
    ((OraclePreparedStatement)ps).setARRAY (1, newArray);

    ps.execute ();

    rs = stmt.executeQuery("SELECT col1 FROM varray_table");
    showResultSet (rs);

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

  }   

  public static void showResultSet (ResultSet rs)
    throws SQLException
  {       
    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 element is of type code "
                          + array.getBaseType()); 
      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++) 
      {
        String value = (String) values[i];
        System.out.println(">> index "+i+" = "+ value);
      }
    }
  }
}


Bad result:

Row 1 :
Array is of type SCOTT.CHAR_VARRAY
Array element is of type code 12
Array is of length 2
>> index 0 = ???
>> index 1 = ???
Row 1 :
Array is of type SCOTT.CHAR_VARRAY
Array element is of type code 12
Array is of length 2
>> index 0 = ???
>> index 1 = ???
Row 2 :
Array is of type SCOTT.CHAR_VARRAY
Array element is of type code 12
Array is of length 4
>> index 0 = null
>> index 1 = null
>> index 2 = null
>> index 3 = null


Above example was run using JDBC/thin 11.2.0.3 ( ojdbc6.jar)  connecting to various databases version 10.2, 11.1 and 11.2.   This issue is not JDBC driver version specific reproduces on JDBC 10.2 and 11.1 drivers.  The issue is however database dependent and may or may not reproduce depending on the database character set used.

Note:
The above source code is adapted from a demo called ArrayExample which is provided in the demo.zip at:
http://download.oracle.com/otn/utilities_drivers/jdbc/11203/demo.zip

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms