ORA-01461的解决过程~~

转自：http://blog.itpub.net/7607759/viewspace-521189

 

近日生产库中的一个过程报出了ora-1461的错误，虽然错误实际处理起来非常简单，但解决过程中与yangtingkun老大就该问题思想火花的碰撞还是让我颇有收益。

 

创建一个最简单的示例演示一下错误的触发：

 

SQL> CREATE TABLE T_VARCHAR (ID VARCHAR2(10));

 

Table created

 

SQL> DECLARE

  2  V_STR VARCHAR2(10000) := lpad('a',10000,'a');

  3  BEGIN

  4   INSERT INTO T_VARCHAR VALUES (SUBSTR(V_STR,1,10));

  5  END;

  6  /

 

DECLARE

V_STR VARCHAR2(10000) := lpad('a',10000,'a');

BEGIN

 INSERT INTO T_VARCHAR VALUES (SUBSTR(V_STR,1,10));

END;

 

ORA-01461: can bind a LONG value only for insert into a LONG column

ORA-06512: at line 5

 

一条最简单的insert居然报错了，有意思吧，是因为v_str的值超长？不应该啊，因为我们在insert时是执行了substr的，甚至说将substr改成这样，还是会报错：

SQL> DECLARE

  2  V_STR VARCHAR2(10000) := lpad('a',10000,'a');

  3  BEGIN

  4   INSERT INTO T_VARCHAR VALUES (SUBSTR(V_STR,1,1));

  5  END;

  6  /

 

DECLARE

V_STR VARCHAR2(10000) := lpad('a',10000,'a');

BEGIN

 INSERT INTO T_VARCHAR VALUES (SUBSTR(V_STR,1,1));

END;

 

ORA-01461: can bind a LONG value only for insert into a LONG column

ORA-06512: at line 5

 

 

显然不会是因为多字节截取问题导致的插入失败，而且如果是插入字符超出列长度的话，应该是报这个错误才对：

 

SQL> insert into t_varchar values (lpad('a',11,'a'));

 

insert into t_varchar values (lpad('a',11,'a'))

 

ORA-12899: value too large for column "TEST"."T_VARCHAR"."ID" (actual: 11, maximum: 10)

 

下面，改一下我们的插入语句：

 

SQL> DECLARE

  2  V_STR VARCHAR2(10000) := lpad('a',10000,'a');

  3  BEGIN

  4   V_STR := SUBSTR(V_STR,1,10);

  5   INSERT INTO T_VARCHAR VALUES (V_STR);

  6  END;

  7  /

 

PL/SQL procedure successfully completed

 

这下成功了，似乎有点儿头绪，再改一下：

 

SQL> DECLARE

  2  V_STR CHAR(4000) := 'a';

  3  BEGIN

  4   INSERT INTO T_CHAR VALUES (SUBSTR(V_STR,1,10));

  5  END;

  6  /

 

PL/SQL procedure successfully completed

 

也成功了。ok，基本确认问题所在，总结如下：

字符类型在pl/sql中做为变量存大，最大可支持32767个字节，但在sql中通常只能够支持到4000字节(char/nchar为2000)，因此如果声明的变量长度超出了sql中类型长度，并且变量实际值也超出类型可接受最大值时，就会触发ORA-01461错误，解决方法自然相当简单，只要在插入/更新之前截取字符长度到符合要求的长度就可以了。

 

另外，这里需要注意，一定要在插入/更新之前截取，而不要在插入/更新时substr，如例：

SQL> DECLARE

  2  V_STR VARCHAR2(10000) := lpad('a',10000,'a');

  3  BEGIN

  4   INSERT INTO T_VARCHAR VALUES (SUBSTR(V_STR,1,10));

  5  END;

  6  /

 

DECLARE

V_STR VARCHAR2(10000) := lpad('a',10000,'a');

BEGIN

 INSERT INTO T_VARCHAR VALUES (SUBSTR(V_STR,1,10));

END;

 

ORA-01461: can bind a LONG value only for insert into a LONG column

ORA-06512: at line 5

这样也仍然是会出错的，这应该是由于pl/sql引擎在编译时对变量长度的判断先于substr进行，因此不等执行就先报错了。