Java ORA-24816：在实际 LONG 或 LOB 列之后提供的扩展非 LONG 绑定数据

ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column

提问by 1097733

I'm getting following exception, while updating table in Hibernate

在 Hibernate 中更新表时出现以下异常

ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column

ORA-24816：在实际 LONG 或 LOB 列之后提供的扩展非 LONG 绑定数据

I have extracted sql query as well, it looks like

我也提取了 sql 查询，它看起来像

Update table_name set columnName (LOB)=value, colmun2 (String with 4000)=value where id=?;

Entity class

实体类

class Test{

    @Lob
    private String errorText;

    @Column(length = 4000)
    private String text;
}

Please help me, what is wrong in this

请帮帮我，这有什么问题

Thanks Ravi Kumar

感谢拉维·库马尔

采纳答案by 1097733

I found issue. 1. When hibernate updating data in DB and entity has 4000 chars column and lob type column then hibernate throwing this exception

我发现问题。1.当hibernate更新数据库中的数据并且实体有4000个字符列和lob类型列时，hibernate抛出这个异常

I have solved this issue by writing two update queires 1. First i have saved entity by using Update() 2. Written another update query for lob column update

我已经通过编写两个更新查询解决了这个问题 1. 首先我使用 Update() 保存了实体 2. 为 lob 列更新编写了另一个更新查询

Thanks ravi

谢谢拉维

回答by mormegil

Running oerr ora 24816to get the details on the error yields:

运行oerr ora 24816以获取有关错误的详细信息会产生：

$ oerr ora 24816
24816, ... "Expanded non LONG bind data supplied after actual LONG or LOB column"
// *Cause:  A Bind value of length potentially > 4000 bytes follows binding for
//          LOB or LONG.
// *Action: Re-order the binds so that the LONG bind or LOB binds are all
//          at the end of the bind list.

So another solution that uses only 1 query would be to move your LOB/LONG binds after all your non-LOB/LONG binds. This may or may not be possible with Hibernate. Perhaps something more like:

因此，另一种仅使用 1 个查询的解决方案是在所有非 LOB/LONG 绑定之后移动 LOB/LONG 绑定。Hibernate 可能会也可能不会。也许更像：

update T set column2 (String with 4000)=:1, columnName (LOB)=:3 where id=:2;

This DML limitation appears to have been around since at least Oracle 8i.

这种 DML 限制似乎至少从 Oracle 8i 开始就存在。

References:

参考：

• http://openacs.org/forums/message-view?message_id=595742
• https://community.oracle.com/thread/417560

• http://openacs.org/forums/message-view?message_id=595742
• https://community.oracle.com/thread/417560

回答by prem

I have also encountered same error in oracle db and foudn that Hibernate Guys fixed here

我也在 oracle db 中遇到了同样的错误，并且发现Hibernate Guys 在这里修复了

In my case we were already using hiberante 4.3.7 but didnt mention that field is Lob in Entity

就我而言，我们已经在使用 hiberante 4.3.7，但没有提到该字段是实体中的 Lob

Reproducing Steps

复制步骤

1. Have fields with varchar2 datatype and clob data type.Make sure your column name are in this alphabetic order clob_field,varchar_two_field1,varchar_two_field2.
2. Now update clob_field with < 2000 bytes and varchar_two_field1 with 4000 bytes size.
3. This should end up with error ORA-24816: Expanded non LONG bind data supplied after actual LONG or LOB column

1. 具有 varchar2 数据类型和 clob 数据类型的字段。确保您的列名称按此字母顺序排列 clob_field、varchar_two_field1、varchar_two_field2。
2. 现在用 < 2000 字节更新 clob_field，用 4000 字节大小更新 varchar_two_field1。
3. 这应该以错误ORA-24816 结束：在实际 LONG 或 LOB 列之后提供的扩展非 LONG 绑定数据

Solution

解决方案

1. Make sure you have hiberante 4.1.8, < 4.3.0.Beta1

2. Annotate your clob/blob field in respective Entity as

   import javax.persistence.Lob;  
   ...
       @Lob
       @Column(name = "description")
       private String description;  
   ....
   

1. 确保你有 hiberante 4.1.8, < 4.3.0.Beta1

2. 在相应的实体中将您的 clob/blob 字段注释为

   import javax.persistence.Lob;  
   ...
       @Lob
       @Column(name = "description")
       private String description;  
   ....
   

If you want to see the difference , by after making above changes enable debug for sql statements by setting "true"for "hibernate.show_sql"in persistence.xml.

如果您想查看不同之处，请在进行上述更改后，通过在persistence.xml 中设置"true"for"hibernate.show_sql"来启用 sql 语句的调试。

回答by ramana mavuluri

I came across this issue today while trying to Insert the data into a table. To avoid this error, Just keep all the fields having "LOB"data type at the end of the insert statement.

我今天在尝试将数据插入表时遇到了这个问题。为避免此错误，只需在插入语句的末尾保留所有具有“LOB”数据类型的字段。

For Example

例如

Table1 has 8 Fields (Field1,Field2,.... Field8 etc..),

表 1 有 8 个字段（Field1、Field2、.... Field8 等），

of which

其中

Field1 and Field2 are of CLOB data types

and the rest are Varchar2 Data types

Field1 和 Field2 是 CLOB 数据类型

其余的是 Varchar2 数据类型

. Then while inserting the data make sure you keep Field1 and Field2values at the end like below.

. 然后在插入数据时确保在末尾保留Field1 和 Field2值，如下所示。

 INSERT INTO TABLE1 ( Field3,Field4,Field5,Field6,Field7,Field8,Field1,Field2)  
    Values ('a','b','c','d','e','f','htgybyvvbshbhabjh','cbsdbvsb')

回答by Srikrishnan

Place your LOB binding at the last. See if that solves the issue..

将 LOB 绑定放在最后。看看能不能解决问题。。

回答by Kristoffer

I do realise that this thread is quite old, but I thought I'd share my own experience with the same error message here for future reference.

我确实意识到这个线程已经很老了，但我想我会在这里分享我自己的经验，以供将来参考相同的错误消息。

I have had the exact same symptoms (i.e. ORA-24816) for a couple of days. I was a bit side-tracked by various threads I came across suggesting that this was related to order of parameter binding. In my case this was not a problem. Also, I struggled to reproduce this error, it only occurred after deploying to an application server, I could not reproduce this through integration tests.

我有几天完全相同的症状（即 ORA-24816）。我被我遇到的各种线程所困扰，这表明这与参数绑定的顺序有关。就我而言，这不是问题。此外，我努力重现此错误，它仅在部署到应用程序服务器后发生，我无法通过集成测试重现此错误。

However, I took a look at the code where I was binding the parameter and found:

但是，我查看了绑定参数的代码，发现：

preparedStatement.setString(index, someStringValue);

I replaced this with:

我将其替换为：

preparedStatement.setClob(index, new StringReader(someStringValue));

This did the trick for me.

这对我有用。

This thread from back in 2009was quite useful.

2009年的这个帖子非常有用。