谈笑有鸿儒,往来无白丁

在恰当的时间、地点以恰当的方式表达给恰当的人...  阅读的时候请注意分类,佛曰我日里面是谈笑文章,其他是各个分类的文章,积极的热情投入到写博的队伍中来,支持blogjava做大做强!向dudu站长致敬>> > 我的微博敬请收听

CAUSE

The Oracle oracle.sql.BLOB OutputStream writes the data in chunks. Since autocommit defaults to true, the first chunk is committed. This results in the write operation for the next chunk of the Blob to fail since it appears to be in the next transaction.
In those conditions, the ORA-22990 exception will occur with any version of Oracle JDBC driver.

SOLUTION


Issue the setAutoCommit(false) command. Then, explicitly commit the transaction after all of the Blob chunks have been written to the row and the stream.close() method has been executed.

If using the Oracle 10g JDBC driver (or greater version), a second solution consists of using the standard JDBC api (setBinaryStream method of java.sql.PreparedStatement interface). And in this case, AutoCommit can be set to true.

Here is an example:

PreparedStatement stmt = conn.prepareStatement("INSERT INTO blobTest VALUES (?,?)"); 
File fd = new File(testFile); 
fis = new FileInputStream(fd); 
stmt.setInt(1,1); 
stmt.setBinaryStream(2,fis,(int)fd.length());



where blobTest is a table defined as the following:

SQL> create table blobTest (id number (4), data blob);
posted on 2015-08-25 13:56 坏男孩 阅读(669) 评论(1)  编辑  收藏 所属分类: ORACLE篇章

FeedBack:
# re: While inserting a large BLOB using JDBC, the application fails with:ORA-22990
2015-08-25 14:03 | 坏男孩
The following error occurs while inserting empty blob to return blob locators.

ORA-22990: lob locator cannot span transaction

DML statements were executed within a for loop to insert an empty BLOB and an attempt to fetch is made followed by commit within the loop.
SOLUTION

It is not advisable to use a COMMIT inside a loop. Use commit after the loop ends.
  回复  更多评论
  

只有注册用户登录后才能发表评论。


网站导航: