Execute sql statement via JDBC with CLOB binding

Question

I have the following query (column log is of type CLOB):

UPDATE table SET log=? where id=?

The query above works fine when using the setAsciiStream method to put a value longer than 4000 characters into the log column.

But instead of replacing the value, I want to append it, hence my query looks like this:

UPDATE table SET log=log||?||chr(10) where id=?

The above query DOES NOT work any more and I get the following error:

java.sql.SQLException: ORA-01461: can bind a LONG value only for insert into a LONG column

Luke Woodward · Accepted Answer · 2012-09-21 18:31:46Z

It looks to me like you have to use a PL/SQL block to do what you want. The following works for me, assuming there's an entry with id 1:

import oracle.jdbc.OracleDriver;
import java.sql.*;
import java.io.ByteArrayInputStream;

public class JDBCTest {

    // How much test data to generate.
    public static final int SIZE = 8192;

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

        // Generate some test data.
        byte[] data = new byte[SIZE];
        for (int i = 0; i < SIZE; ++i) {
            data[i] = (byte) (64 + (i % 32));
        }

        ByteArrayInputStream stream = new ByteArrayInputStream(data);

        DriverManager.registerDriver(new OracleDriver());
        Connection c = DriverManager.getConnection(
            "jdbc:oracle:thin:@some_database", "user", "password");

        String sql =
            "DECLARE\n" +
            "  l_line    CLOB;\n" +
            "BEGIN\n" +
            "  l_line := ?;\n" +
            "  UPDATE table SET log = log || l_line || CHR(10) WHERE id = ?;\n" +
            "END;\n";

        PreparedStatement stmt = c.prepareStatement(sql);
        stmt.setAsciiStream(1, stream, SIZE);
        stmt.setInt(2, 1);
        stmt.execute();
        stmt.close();

        c.commit();

        c.close();
    }
}

Aaron Digulla · Accepted Answer · 2009-03-24 16:33:23Z

2

BLOBs are not mutable from SQL (well, besides setting them to NULL), so to append, you would have to download the blob first, concatenate locally, and upload the result again.

The usual solution is to write several records to the database with a common key and a sequence which tells the DB how to order the rows.

answered Mar 24, 2009 at 16:33

Aaron Digulla

330k111 gold badges626 silver badges840 bronze badges

Collectives™ on Stack Overflow

Execute sql statement via JDBC with CLOB binding

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related