Copying a Clob in another Clob with different implementation

Question

In my company we are performing a database migration. For lots of reasons we cannot use a database migration tool, so we had to develop our migration tool to copy from a DB to another all rows contained in some specific tables. We have developed a tool using JDBC of multiple database. At the moment, we are migrating from DB2 to Oracle, with an intermediate step to H2. Same tables ha a Clob column. When we export this column from DB2 to H2 we get no errors or issues, but when we try to copy the Clob from H2 to Oracle using JDBC we get the following exception:

ClassCastException: cannot cast from org.h2.jdbc.JdbcClob to oracle.jdbc.Clob

Is there a way or a procedure to perform this kind of conversion? Something like a ClobCopy utility within different Clob types? Unfortunately we can do this task only using Java and Jdbc, no JPA or DB migration tools due to customer specifications.

This is an example of what I'm trying to do:

public class CopyTable {

    public void doCopy(){
        Connection h2 = getH2Connection(); //suppose this exists and works
        Connection oracle = getOracleConnection(); //suppose this exists and works

        String sqlSelect = "select * from tabletoexport";
        String sqlInsert = "insert into tabletofill(ID, DATA) values (?,?)";

        PreparedStatement select = h2.prepareStatement(sqlSelect);
        PreparedStatement insert = oracle.prepareStatement(sqlInsert);
        ResultSet rs = select.executeQuery();
        while (rs.next()){
            insert.setLong(1, rs.getLong("ID"));
            insert.setClob(2, rs.getClob("DATA")); //this throws an exception
            insert.executeUpdate();
        }

    }

}

Answer 1

The Clob interface has a getCharacterStream() method which returns a Reader, and the PreparedStatement interface has a setClob() method which takes a Reader. All you need to do to get the copy working is to use these methods.

In other words, replace the line

    insert.setClob(2, rs.getClob("DATA")); //this throws an exception

with

    insert.setClob(2, rs.getClob("DATA").getCharacterStream());

As for why the import from DB/2 to H2 didn't complain, perhaps the H2 JDBC driver doesn't assume that Clob values passed in to setClob come from H2, but the Oracle JDBC driver does assume that Clobs passed in in the same way are from Oracle. However, the Oracle JDBC can't reasonably make any such assumptions about a Reader, as these could come from anywhere