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!

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,258 views