如何使用 Java/JDBC 在 Oracle 数据库中存储超过 4000 个字符的字符串？

How do I store a string longer than 4000 characters in an Oracle Database using Java/JDBC?

提问by Ventrue

I'm not sure how to use Java/JDBC to insert a very long string into an Oracle database.

我不确定如何使用 Java/JDBC 将一个很长的字符串插入到 Oracle 数据库中。

I have a String which is greater than 4000 characters, lets say it's 6000. I want to take this string and store it in an Oracle database.

我有一个大于 4000 个字符的字符串，假设它是 6000。我想把这个字符串存储在 Oracle 数据库中。

The way to do this seems to be with the CLOB datatype. Okay, so I declared the column as description CLOB.

这样做的方法似乎是使用 CLOB 数据类型。好的，所以我将该列声明为描述 CLOB。

Now, when it comes time to actually insert the data, I have a prepared statement pstmt. It looks like pstmt = conn.prepareStatement(“INSERT INTO Table VALUES(?)”).

现在，当需要实际插入数据时，我有一个准备好的语句 pstmt。看起来像pstmt = conn.prepareStatement(“INSERT INTO Table VALUES(?)”)。

So I want to use the method pstmt.setClob(). However, I don't know how to create a Clob object with my String in it; there's no constructor (presumably because it can be potentially much larger than available memory).

所以我想用这个方法pstmt.setClob()。但是，我不知道如何使用我的 String 创建一个 Clob 对象；没有构造函数（大概是因为它可能比可用内存大得多）。

How do I put my String into a Clob?

如何将我的字符串放入 Clob 中？

Keep in mind I'm not a very experienced programmer; please try to keep the explanations as simple as possible. Efficiency, good practices, etc. are not a concern here, I just want the absolute easiest solution. I'd like to avoid downloading other packages if it all possible; right now I'm just using JDK 1.4 and what is labelled ojdbc14.jar. I've looked around a bit but I haven't been able to follow any of the explanations I've found.

请记住，我不是一个非常有经验的程序员；请尽量保持解释简单。效率、良好实践等在这里不是问题，我只想要绝对最简单的解决方案。如果可能的话，我想避免下载其他软件包；现在我只使用 JDK 1.4 和标记为ojdbc14.jar. 我环顾四周，但我无法理解我找到的任何解释。

If you have a solution that doesn't use Clobs, I'd be open to that as well, but it has to be one column.

如果您有一个不使用 Clob 的解决方案，我也会对此持开放态度，但它必须是一列。

回答by Bozho

You have (at least) two options:

您（至少）有两个选择：

• use connection.createClob()to create a Clob, set the data on it, and set it on the prepared statement. This will work for smaller data

• use preparedStatement.setClob(position, reader)- here you will have a Readerinstance.

• 用于connection.createClob()创建一个Clob，在其上设置数据，并将其设置在准备好的语句上。这适用于较小的数据

• 使用preparedStatement.setClob(position, reader)- 在这里您将有一个Reader实例。

回答by Naelson Matheus Junior

Also, this below solution explains how to handle this using PL/SQL

此外，以下解决方案解释了如何使用 PL/SQL 处理此问题

Reference: http://gogates.blogspot.com/2013/09/inserting-clob-data-which-is-more-than.html

参考：http: //gogates.blogspot.com/2013/09/inserting-clob-data-which-is-more-than.html

---

---

Inserting CLOB data which is more than 4000 characters into Oracle Table I was trying to insert 4000+ characters data into an Oracle table having CLOB as data type, but could not do that.. using simple insert statement

将超过 4000 个字符的 CLOB 数据插入 Oracle 表中

Table Structure which I used is as below

我使用的表结构如下

CREATE TABLE ClobTest (id number, clobData clob );

Table structure is pretty simple, having only 2 columns a number and a clob data type.

表结构非常简单，只有 2 列，一个数字和一个 clob 数据类型。

Now... if you run below query it will get execute without any error

现在......如果你在查询下面运行，它将被执行而没有任何错误

INSERT INTO ClobTest VALUES(1, 'ClobTestData');

However, when you increase the length of your data set 4000+ it will not succeed.and will give error as below

但是，当您将数据集的长度增加 4000+ 时，它不会成功。并且会出现如下错误

Error report: SQL Error: ORA-01704: string literal too long 01704. 00000 - "string literal too long" *Cause: The string literal is longer than 4000 characters. *Action: Use a string literal of at most 4000 characters. Longer values may only be entered using bind variables.

错误报告：SQL 错误：ORA-01704：字符串文字太长 01704. 00000 - “字符串文字太长” *原因：字符串文字长度超过 4000 个字符。*操作：使用最多 4000 个字符的字符串文字。只能使用绑定变量输入更长的值。

After googling this issue, came to know that, if you want to insert 4000+ char data into CLOB data type, try to do that using PL-SQL block.

在谷歌搜索这个问题后，才知道，如果要将 4000+ 字符数据插入到 CLOB 数据类型中，请尝试使用 PL-SQL 块来做到这一点。

Root cause of this is, Oracle SQL do not support char / varchar data type which is more than 4000 characters; however PL-SQL does so.

根本原因是，Oracle SQL 不支持超过 4000 个字符的 char / varchar 数据类型；但是 PL-SQL 会这样做。

I used below anonymous pl-sql to insert 4000+ character data into CLOB data type

我使用下面的匿名 pl-sql 将 4000+ 个字符数据插入到 CLOB 数据类型中

DECLARE

vClob VARCHAR(8000);

BEGIN

vClob := '<Charater data having 4000+ characters>';

INSERT INTO ClobTest VALUES(1, vClob);

END;

To verify if really 4000+ characters got inserted into target executed below query

验证是否真的有 4000+ 个字符插入到在查询下面执行的目标中

SELECT LENGHT(clobData) FROM ClobTest 

I hope that helps

我希望有帮助

回答by hobbs

Here is an example at oracle.comfor using LOB columns with Oracle and JDBC. Basically, it's inserting a LOB locator for an empty LOB (actually two, since it's demonstrating both BLOBs and CLOBs), locking the row for update, and then using the BLOB and CLOB OutputStream interfaces to write the data into the LOBs. When the "SELECT ... FOR UPDATE" is released, the LOBs will be refreshed in the database and the newly inserted data will be visible.

这是oracle.com上的一个示例，用于将 LOB 列与 Oracle 和 JDBC 结合使用。基本上，它为空 LOB（实际上是两个，因为它同时演示 BLOB 和 CLOB）插入一个 LOB 定位器，锁定行以进行更新，然后使用 BLOB 和 CLOB OutputStream 接口将数据写入 LOB。当“SELECT ... FOR UPDATE”被释放时，LOB 将在数据库中刷新，新插入的数据将可见。

回答by stacker

There are several methods see Java Database Connectivity (JDBC) - Convert String to CLOB and Insert into Oracle database

有几种方法参见Java Database Connectivity (JDBC) - Convert String to CLOB and Insert into Oracle database