Java类，用于从LOB dataType的Oracle_db中检索大字符串 [英] Java Class for retrieving Large String from Oracle_db of LOB dataType

问题描述

在我的新工作/第一份工作中，我们有一个oracle数据库，其字段之一设置为LOB(大对象)数据类型.当我运行选择语句 ，我遇到了这个问题，这给我一个如下错误.

At my new/first job We have an oracle database with one of its fields set to LOB(large objects) data type. When I run a select statement , I get this issue, which gives me an error as following.

ERROR:
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 10228,      
maximum: 4000)

每个管中的字符串(即日志)的大小比缓冲区的最大容量大10228.现在我的老板要求我编写一个Java类，这将使我们能够
检索整个字符串.

This size of the string(which are logs) inside each tuble is 10228 more than the Max capacity of the buffer . Now my boss has asked me to write a java class that will make us able to
retrieve the entire string.

我需要一些有关如何解决此问题的指导. *如果说，我已经准备好Java类，如何配置它与oracle一起使用(服务器名称，用户密码)? 它将与JDBC和其他jar文件放在同一目录中吗? .您还认为以下链接是否完全相关.

I need some guidance on how to approach solving this issue . * If lets say, I have the java class ready how can I configure it to work with oracle, beside(server name, user. pass)? is it going to be placed in the same directory as JDBC and other jar files? . Also do you think the links below are relevant at all.

http://docs.oracle. com/cd/F49540_01/DOC/java.815/a64685/oraext4.htm#1003223

http://docs.oracle.com/cd/F49540_01/DOC/java.815/a64685/samapp2.htm

http://www.idevelopment.info/data/编程/java/jdbc/LOBS/BLOBFileExample.java

顺便说一句，我能够提出一个临时解决方案，如下所示:

BTW, I was able to come up with a temp solution as follow:

Select  dbms_lob.substr( BLOB_FieldName, 4000, 1 ) 
from Database name Where [Condition];

通过此查询，我能够获得字符串的前4000个字节，这很好，但是我们需要整个字符串.然后，在阅读了有关utl_raw.cast_to_varchar2的信息后，我尝试了以下

with this query I was able to get the first 4000 bytes of the string which is great, but we need the entire string. And then after reading about utl_raw.cast_to_varchar2 I tried the following

Select utl_raw.cast_to_varchar2( dbms_lob.substr( BLOB_fieldName, 19000, 1 ) ) 
from[dbname];

我之所以使用utl_raw.cast_to_varchar2，是因为我在网上某个地方读到[Varchar2]数据类型最多可容纳32k字节，并考虑将[LOB]数据类型强制转换为Varchar2，但这没有用.

I used utl_raw.cast_to_varchar2 because I read somewhere online that [Varchar2] datatype can hold up to 32k byte and thought of casting [LOB] data type to Varchar2 , but it did not work.

仅供参考， 我们被限制使用PL/SQL. 环境是linux，我可以访问sqldeveloper文件，该文件包含JDBC和所有其他jar文件，并且可以添加任何新文件.

FYI, We are restricted from using PL/SQL. Environment is linux and I have access to the sqldeveloper file which contains JDBC and all the other jar files and can add any new files.

先谢谢了.

推荐答案

使用任何最新的驱动程序，您只需选择CLOB列并调用ResultSet.getString()即可获取该值.无需任何特殊处理，例如dbms_lob().

With any up-to-date driver, you can just select CLOB columns and call ResultSet.getString() to obtain the value. No need for any special treatment like dbms_lob().

11.x驱动程序绝对有能力做到这一点，尽管不确定过时的10.x驱动程序.

The 11.x drivers are definitely capable of doing that, not sure about the outdated 10.x drivers though.

如果驱动程序太旧并且无法升级，则您在SQL语句中仍然不需要任何特殊处理，只需使用getCharacterStream()处理ResultSet中的CLOB-甚至可以使用9. x个驱动程序.

If the driver is too old and upgrading is not an option, you still don't need any special treatment in your SQL statement, just process the CLOB from the ResultSet using getCharacterStream() - that even works with 9.x drivers.

类似的东西:

ResultSet rs = statement.executeQuery("Select clob_column from table_name name ...");
Reader in = rs.getCharacterStream(1);
String clobValue = null;
if (!rs.wasNull())
{
   // process whatever the Reader returns, e.g. using Apache Commons IO
   clobValue = IOUtils.toString(in);
}
in.close();

使用当前驱动程序，您只需执行以下操作:

With a current driver, you'd just do:

ResultSet rs = statement.executeQuery("Select clob_column from table_name name ...");
String clobValue = rs.getString(1);

顺便说一句:Oracle 10已经过时了，您应该考虑升级.

Btw: Oracle 10 is pretty outdated, you should really think about upgrading.

这篇关于Java类，用于从LOB dataType的Oracle_db中检索大字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！