通过带有 CLOB 绑定的 JDBC 执行 sql 语句

发布于 2024-07-15 20:38:29 字数 408 浏览 5 评论 0原文

我有以下查询（列日志的类型为 CLOB）：

UPDATE table SET log=? where id=?

当使用 setAsciiStream 方法将长度超过 4000 个字符的值放入日志列时，上面的查询工作正常。

但我不想替换该值，而是想附加它，因此我的查询如下所示：

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

上面的查询不再起作用，我收到以下错误：

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

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

难得心□动 2024-07-22 20:38:29

在我看来，您必须使用 PL/SQL 块来完成您想要的操作。假设有一个 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();
    }
}

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();
    }
}

回复收藏 0 原文