Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Oracle STRUCT use OBJECT TYPE with CLOB attribute

Javier RinconOct 9 2019 — edited Oct 16 2019

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.

Comments
Post Details
Added on Oct 9 2019
6 comments
1,226 views