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)

I guess, instead of execute(), you should use executeUpdate() which returns the number of records which have been updated/inserted. — Enrico Giurin
– Enrico Giurin, Commented Oct 28, 2019 at 17:28
That doesn't seem to have any effect, apart from returning the number of rows inserted. Also, the rows is being inserted, it's just that column which get incorrect data. — javivalle
– javivalle, Commented Oct 28, 2019 at 18:13
Works for me fine, and BTW I never saw before setXxxx(i++,.. — Marmite Bomber
– Marmite Bomber, Commented Oct 28, 2019 at 20:01

Community · Accepted Answer · 2020-06-20 09:12:55Z

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.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Oct 28, 2019 at 22:31

SternK

13.3k34 gold badges40 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to insert a String Array into a VARRAY column?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related