因未自动转换类型而ORA-01461之一例

oracle10.2.0.4版本

字符变量(比如v_mail_tmp)超过4000字节，即使是将substr(v_mail_tmp,1,1333)插入一个VARCHAR2(4000)的字段，该表只有一个4000的字段

也会报ORA-01461,can bind a LONG value only for insert into a LONG column

最后的解决办法是定义一个varchar2(4000)的变量v_CONTENT，做一次赋值v_CONTENT:=substr(v_mail_tmp,1,1333)，再将v_CONTENT插入表就不报错了

原因估计是超过4000后，当作long型处理，做substr，long型未变导致无法插入，而赋值给varchar2(4000)的变量可以将类型强制转回来

验证这个想法

SQL> create table ta (t1 varchar2(4000));

Table created

SQL> declare
  2  v_tmp varchar2(10000);
  3  begin
  4  v_tmp:='1';
  5  insert into ta (t1)values(v_tmp);
  6  end;
  7  /

PL/SQL procedure successfully completed

SQL> select * from ta;

T1
--------------------------------------------------------------------------------
1

SQL> declare
  2  v_tmp varchar2(10000);
  3  v_tmp1 varchar2(4000);
  4  begin
  5  for i in 1..5000 loop
  6  v_tmp:=v_tmp||'1';
  7  end loop;
  8  insert into ta (t1)values(substr(v_tmp,1,1));
  9  end;
 10  /

declare
v_tmp varchar2(10000);
v_tmp1 varchar2(4000);
begin
for i in 1..5000 loop
v_tmp:=v_tmp||'1';
end loop;
insert into ta (t1)values(substr(v_tmp,1,1));
end;

ORA-01461: can bind a LONG value only for insert into a LONG column
ORA-06512: at line 9

SQL> declare
  2  v_tmp varchar2(10000);
  3  v_tmp1 varchar2(4000);
  4  begin
  5  for i in 1..5000 loop
  6  v_tmp:=v_tmp||'1';
  7  end loop;
  8  v_tmp1:=substr(v_tmp,4000);
  9  insert into ta (t1)values(v_tmp1);
 10  end;
 11  /

PL/SQL procedure successfully completed