Example usage for java.sql CallableStatement setClob

Introduction

In this page you can find the example usage for java.sql CallableStatement setClob.

Prototype

void setClob(String parameterName, Reader reader) throws SQLException;

Source Link

Document

Sets the designated parameter to a Reader object.

Usage

From source file:org.apache.oozie.tools.OozieDBCLI.java

private void convertClobToBlobInOracle(Connection conn) throws Exception {
    if (conn == null) {
        return;//from  w w w . j a  va2 s. c o m
    }
    System.out.println("Converting clob columns to blob for all tables");
    Statement statement = conn.createStatement();
    CallableStatement tempBlobCall = conn.prepareCall("{call dbms_lob.CREATETEMPORARY(?, TRUE)}");
    tempBlobCall.registerOutParameter(1, java.sql.Types.BLOB);
    CallableStatement dbmsLobCallStmt = conn
            .prepareCall("{call dbms_lob.CONVERTTOBLOB(?, ?, ?, ?, ?, 0, ?, ?)}");
    dbmsLobCallStmt.registerOutParameter(1, java.sql.Types.BLOB);
    // Lob max size
    dbmsLobCallStmt.setInt(3, Integer.MAX_VALUE);
    dbmsLobCallStmt.registerOutParameter(4, java.sql.Types.INTEGER);
    // dest_offset
    dbmsLobCallStmt.setInt(4, 1);
    // src_offset
    dbmsLobCallStmt.registerOutParameter(5, java.sql.Types.INTEGER);
    dbmsLobCallStmt.setInt(5, 1);
    // blob_csid
    dbmsLobCallStmt.registerOutParameter(6, java.sql.Types.INTEGER);
    // lang_context
    dbmsLobCallStmt.setInt(6, 0);
    // warning
    dbmsLobCallStmt.registerOutParameter(7, java.sql.Types.INTEGER);
    dbmsLobCallStmt.setInt(7, 1);
    for (Map.Entry<String, List<String>> tableClobColumnMap : getTableClobColumnMap().entrySet()) {
        String tableName = tableClobColumnMap.getKey();
        List<String> columnNames = tableClobColumnMap.getValue();
        for (String column : columnNames) {
            statement.executeUpdate(getAddColumnQuery(tableName, TEMP_COLUMN_PREFIX + column, "blob"));
        }
        ResultSet rs = statement.executeQuery(getSelectQuery(tableName, columnNames));
        while (rs.next()) {
            for (int i = 0; i < columnNames.size(); i++) {
                Clob srcClob = rs.getClob(columnNames.get(i));
                if (srcClob == null || srcClob.length() < 1) {
                    continue;
                }
                tempBlobCall.execute();
                Blob destLob = tempBlobCall.getBlob(1);
                dbmsLobCallStmt.setBlob(1, destLob);
                dbmsLobCallStmt.setClob(2, srcClob);
                dbmsLobCallStmt.execute();
                Blob blob = dbmsLobCallStmt.getBlob(1);
                PreparedStatement ps = conn.prepareStatement("update " + tableName + " set "
                        + TEMP_COLUMN_PREFIX + columnNames.get(i) + "=? where id = ?");
                ps.setBlob(1, blob);
                ps.setString(2, rs.getString(1));
                ps.executeUpdate();
                ps.close();
            }
        }
        rs.close();
        for (String column : columnNames) {
            statement.executeUpdate(getDropColumnQuery(tableName, column));
            statement.executeUpdate(getRenameColumnQuery(tableName, TEMP_COLUMN_PREFIX + column, column));
        }
    }
    dbmsLobCallStmt.close();
    tempBlobCall.close();
    System.out.println("Done");
}

---