I am trying to use STRUCT for the insertion of massive data in my table DATA_TABLE, but generate error (java.sql.SQLException: Fail to convert to internal representation) with data type field CLOB and I can't find a solution to my problem, my code:
My table
CREATE TABLE DATA_TABLE (DAT_ID NUMBER,
DAT_CODE VARCHAR2(10),
DAT_TEXT CLOB);
My type object
CREATE OR REPLACE TYPE TY_OBJ_DATA AS OBJECT (DAT_ID NUMBER,
DAT_CODE VARCHAR2(10),
DAT_TEXT CLOB);
My type table from type object
CREATE OR REPLACE TYPE TY_TABLE_DATA AS TABLE OF SCHEMA.TY_OBJ_DATA;
My procedure
PROCEDURE pl_bulk_data(tyTableData IN OUT ty_table_data) IS
TYPE t_array_elements IS TABLE OF schema.data_table %rowtype INDEX BY BINARY_INTEGER;
l_data t_array_elements;
PRAGMA AUTONOMOUS_TRANSACTION;
CURSOR curData IS
SELECT dat_id, dat_code, dat_text
FROM TABLE(tyTableData);
BEGIN
OPEN curData;
LOOP
FETCH curData BULK COLLECT INTO l_data LIMIT 100;
FORALL i IN 1..l_data.COUNT
INSERT INTO schema.data_table VALUES l_data(i);
EXIT WHEN curData%notfound;
END LOOP;
CLOSE curData;
COMMIT;
END pl_bulk_data;
My simplified java method
public static void bulkData(List<DataTable> listDataInfo) throws Exception {
DataSource ds = (DataSource) getEntityManager().getEntityManagerFactory().getProperties().get("javax.persistence.jtaDataSource");
OracleConnection connection = ds.getConnection().unwrap(OracleConnection.class);
try{
StructDescriptor typeTableObject = StructDescriptor.createDescriptor("SCHEMA.TY_OBJ_DATA", conect);
STRUCT[] structData = new STRUCT[DataTable.size()];
int counter= 0;
for (DataTable d : listDataInfo) {
Clob clob = connection.createClob();
STRUCT m = new STRUCT(typeTableObject, connection,
new Object[]{d.getDatId(),
d.getDatCode,
clob.setString(1, d.getDatText)});
structData [counter++] = m;
}
ArrayDescriptor tyTable = ArrayDescriptor.createDescriptor("SCHEMA.TY_TABLE_DATA", connection);
ARRAY array = new ARRAY(tyTable, connection, structData);
String sqlQuery = "{ CALL PACKAGE_BULK.PL_BULK_DATA }";
CallableStatement cst = conect.prepareCall(sqlQuery);
cst.setArray(1, array );
cst.execute();
} catch (Exception e) {
throw new Exception(e);
} finally {
try {
connection.close();
} catch (SQLException e) {
throw new Exception(e);
}
}
}
i Use ojdbc6 version 11.2.0, java 8 and Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit.
Question: Is it possible to use STRUCT with fields of type CLOB? I am doing something wrong? my DatText field when it is remapped is of the String type and that was the best conversion that I managed to do from String to a clob but I still have problems, he tried without the conversion to clob and I have the same error. Any idea how I can solve this? Thank you.