Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1114116
  • 博文数量: 350
  • 博客积分: 10
  • 博客等级: 民兵
  • 技术积分: 5668
  • 用 户 组: 普通用户
  • 注册时间: 2011-03-23 17:53
文章分类

全部博文(350)

文章存档

2013年(350)

分类: Oracle

2013-04-25 11:33:52

近日生产库中的一个过程报出了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个字节,但在中通常只能够支持到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进行,因此不等执行就先报错了。


阅读(732) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~