5 Replies Latest reply on Oct 16, 2019 11:24 AM by Billy~Verreynne

    Oracle STRUCT use OBJECT TYPE with CLOB attribute

    Javier Rincon

      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.