使用CLOB列更新表时出错:ORA-01461 [英] Error while updating a table with CLOB column:ORA-01461

问题描述

我在Oracle数据库中有一个表，如下所示，

I have a table in Oracle Database as follows,

create table test_clob(
id1 number,
clob_col clob);

如果我尝试将大小大于4000的varchar2变量插入到CLOB列中，则插入时不会有任何问题.

If i try to insert a varchar2 variable with size more than 4000 into the CLOB column, it inserts without any problem.

insert into test_clob values (1,rpad('a',32760,'a'));
commit;

如果我尝试按以下方式更新CLOB列，则效果很好.

If i try to update the CLOB column as follows, it works perfectly fine.

update test_clob set clob_col = rpad('b',32760,'b') where id1 = 1;
commit;

但是，如果我尝试按以下方式运行更新语句，则由于"ORA-01461:只能将LONG值绑定为仅可插入LONG列"而失败.

However, if i try to run the update statement as follows, it is failing due to "ORA-01461: can bind a LONG value only for insert into a LONG column" error.

declare
large_string varchar2(32767) := rpad('c',32760,'c');
begin
update test_clob set clob_col = nvl(large_string,clob_col) where id1 = 1;
commit;
end;

我怀疑是NVL功能引起了问题.我们对此表示任何帮助.

I suspect it is the NVL function that is causing the problem. Any help on this is highly appreciated.

注意:我在示例中使用了一个简单的表，但是实际上该表有几列，并且update语句必须一次更新许多列.

Note: I have used a simple table in the example, but actually the table has several columns and update statement has to update many colums at a time.

推荐答案

实际上，当从SQL调用rpad('a',32760,'a')时，它只会返回4k字符串，这就是它起作用的原因.

actually, rpad('a',32760,'a') when called from SQL would only return a 4k string which is why it works.

SQL中的Varchar类型限制为4k，因此，当您尝试从pl/sql绑定32k varchar2变量时，它将失败(因为从pl/sql调用rpad时将返回32k).

A Varchar type in SQL is limited to 4k, so when you try to bind a 32k varchar2 variable from pl/sql it will fail (as rpad when called from pl/sql will return the 32k).

例如:

SQL> select length(rpad('a',32760,'a'))  from dual;

LENGTH(RPAD('A',32760,'A'))
---------------------------
                       4000

它默默地限制您获得4k的回报.但pl/sql不会限制为4k:

it silently limits the return to 4k for you. but pl/sql will not limit to 4k:

SQL> declare
  2  large_string varchar2(32767) := rpad('c',32760,'c');
  3  begin
  4  dbms_output.put_line(length(large_string));
  5  end;
  6  /
32760

您应将pl/sql变量定义为clob而不是varchar2(32760):

You should define your pl/sql variable as clob and NOT varchar2(32760):

SQL> create table test_clob(
  2  id1 number,
  3  clob_col clob);

Table created.

SQL> insert into test_clob values (1,rpad('a',32760,'a'));

1 row created.

SQL> select length(clob_col) from test_clob;

LENGTH(CLOB_COL)
----------------
            4000

SQL> commit;

Commit complete.

SQL> declare
  2  large_string clob := rpad('c',32760,'c');
  3  begin
  4  update test_clob set clob_col = nvl(large_string,clob_col) where id1 = 1;
  5  commit;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select length(clob_col) from test_clob;

LENGTH(CLOB_COL)
----------------
           32760

SQL>

这篇关于使用CLOB列更新表时出错:ORA-01461的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！