Unable to convert the CLOB data into String

Question

I am trying to convert java.sql.Clob data into String by using SubString method (This method giving good performance compared with other). The clob data having near or morethan to 32MB. AS my observation substring method able to to return upto 33554342 bytes only.

if clob data is crossing 33554342 bytes then this it's throwing below sql exception
ORA-24817: Unable to allocate the given chunk for current lob operation

EDIT CODE:

public static void main(String[] args) throws SQLException {

    Main main = new Main();
    Connection con = main.getConnection();
    if (con == null)    {
        return;
    }

    PreparedStatement   pstmt   =   null;
    ResultSet   rs  =   null;
    String sql  =   "SELECT Table_ID,CLOB_FILE FROM TableName WHERE SOMECONDITION ";

    String table_Id = null;
    String directClobInStr  =   null;
    CLOB clobObj = null;
    String clobStr = null;
    Object obj= null;
    try {
        pstmt   =   con.prepareStatement(sql);
        rs      =   pstmt.executeQuery();
        while (rs.next())   {
            table_Id    =  rs.getString( "Table_ID" ) ; 
            directClobInStr =  rs.getString( "clob_FILE" ) ;
                      obj       =  rs.getObject( "CLOB_FILE");

            clobObj = (CLOB) obj;
             System.out.println("Table id " + table_Id);
             System.out.println("directClobInStr "   + directClobInStr);
             clobStr = clobObj.getSubString(1L, (int)clobObj.length() );//33554342
             System.out.println("clobDataStr = " + clobStr);
        }
    }
    catch (SQLException e) {
        e.printStackTrace();
        return;
    }
    catch (Exception e) {
        e.printStackTrace();
        return;
    }
    finally {
        try {
        rs.close();
        pstmt.close();
        con.close();
        }
        catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }
}

NOTE:- here obj = rs.getObject( "CLOB_FILE"); working but I am not expecting this. because I am getting ResultSet object from somewhere as Object. I have to convert and get the data from CLOB

Any Idea how to achieve this?

Answer 1

Instead:

clobStr = clobObj.getSubString(1L, (int)clobObj.length() );

Try something like:

int toread = (int) clobObj.length();
int read = 0;
final int block_size = 8*1024*1024;
StringBuilder str = new StringBuilder(toread);
while (toread > 0) {
    int current_block = Math.min(toread, block_size);
    str.append(clobObj.getSubString(read+1, current_block));
    read += current_block;
    toread -= current_block;
}
clobStr = str.toString();

It extracts substrings using a loop (8MB per iteration).

But remember that, as far as I known, Java Strings are limited to 2 GB (this is the reason why read is declared as int instead of long) and Oracle CLOBs are limited to 128 TB.