How to insert a String Array into a VARRAY column?

Question

I'm trying to do insertions using jdbc into a table with has two VARRAY columns; one is an VARRAY of int, the other an VARRAY of varchar2. The first column is being inserted perfectly. However, the second one inserts all values as null.

This is the table:

CREATE OR REPLACE TYPE ARRAY_DTBN_STRING AS VARRAY(10) OF VARCHAR2(100);
CREATE OR REPLACE TYPE ARRAY_DTBN_INTEGER AS VARRAY(10) OF int;
CREATE TABLE DTBN_DETAILS(
    ID VARCHAR2(100) PRIMARY KEY ,
    VALS ARRAY_DTBN_STRING,
    SIZES ARRAY_DTBN_INTEGER
);

This is the Java code for the insertion using JDBC:

DtbnDetails det = new DtbnDetails();
det.setId("ABC");
det.setValues(new String[] {"room","house"});
det.setSizes(new int[] {3,5});

String query = "INSERT INTO DTBN_DETAILS "+ 
    "(ID, VALS, SIZES) VALUES (?, ?, ?)";

Connection conAux = Conexion.getConnection("main");                     
OracleConnection con = conAux.unwrap(OracleConnection.class);

PreparedStatement statement = con.prepareStatement(query);

int i=1;

String id = details.getId();
sentencia.setString(i++,id);

String[] values = details.getValues()==null ? new String[0] : details.getValues();
Array valuesArray = con.createOracleArray("ARRAY_DTBN_STRING", values);
statement.setArray(i++, valuesArray);

int[] sizes = details.getSizes()==null ? new int[0] : details.getSizes();
Array sizesArray = con.createOracleArray("ARRAY_DTBN_INTEGER", sizes);
statement.setArray(i++, sizesArray);

statement.execute();        
con.commit();

After executing this code, the "VALS" column has two values, but both are null:

SELECT * FROM DTBN_DETAILS;

ABC
ARRAY_DTBN_STRING(NULL, NULL)
ARRAY_DTBN_INTEGER(3, 5)

Answer 1

I was faced with the same problem as you. The solution was adding orai18n.jar to the classpath.

According to this.

If the clients and the server use different character sets, then the driver provides the support to perform the conversions between the database character set and the client character set.

The basic ojdbc JAR file contains all the necessary classes to provide complete globalization support for:

• Oracle character sets for CHAR, VARCHAR. LONGVARCHAR, or CLOB data that is not being retrieved or inserted as a data member of an Oracle 8 Object or Collection type.

• CHAR or VARCHAR data members of Object and Collection for the character sets US7ASCII, WE8DEC, WE8ISO8859P1 and UTF8.

To use any other character sets in CHAR or VARCHAR data members of Objects or Collections, you must include orai18n.jar in your application's CLASSPATH.