Bug:4458790 ORA-6502

同事遇到一个问题，执行了一个匿名PL/SQL块遇到ORA-06502错误。

简单重现一下：

Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.

C:\Documents and Settings\yan>sqlplus admin/admin

SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 9月 27 17:19:59 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> set serverout on
SQL> declare
  2      lv_tmplevel  T_GCHKDEG.LEVNO%TYPE ;
  3  begin
  4      select levno into lv_tmplevel  from T_GCHKDEG where rownum=1;
  5      dbms_output.put_line(lv_tmplevel);
  6  end;
  7  /
01

PL/SQL 过程已成功完成。

SQL> declare
  2      lv_tmplevel  T_GCHKDEG.LEVNO%TYPE ;
  3  begin
  4     select min(levno) into lv_tmplevel  from T_GCHKDEG;
  5     dbms_output.put_line(lv_tmplevel);
  6  end;
  7  /
declare
*
第 1 行出现错误:
ORA-06502: PL/SQL: 数字或值错误 :  字符串缓冲区太小
ORA-06512: 在 line 4

SQL> desc T_GCHKDEG
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 LEVNO                                     NOT NULL CHAR(2)
 LEVELTYPE                                          CHAR(1)
 RISKTYPE                                  NOT NULL CHAR(2)
 VALTYPE                                   NOT NULL CHAR(1)
 BEGNUM                                    NOT NULL NUMBER
 ENDNUM                                             NUMBER
 MINVALUE                                           NUMBER(12,2)
 MAXVALUE                                           NUMBER(12,2)
 BEGDATE                                   NOT NULL DATE
 ENDDATE                                   NOT NULL DATE
 GAVENO                                             CHAR(10)
 GAVEDATE                                           DATE
 MOVENO                                             CHAR(10)
 MOVEDATE                                           DATE
 LASTUPDOPER                                        CHAR(10)
 LASTUPDTRA                                         VARCHAR2(14)
 LASTUPDTIME                                        CHAR(14)

SQL> select min(levno) from T_GCHKDEG;

MI
--
01

SQL> select length(min(levno)) from T_GCHKDEG;

LENGTH(MIN(LEVNO))
------------------
                 2

LEVNO定义的是CHAR(2)，MIN(LEVNO)也不可能会超出2个字符。

加了个TO_CHAR函数就能执行成功了。

SQL> declare
  2      lv_tmplevel  T_GCHKDEG.LEVNO%TYPE ;
  3  begin
  4     select to_char(min(levno)) into lv_tmplevel  from T_GCHKDEG;
  5     dbms_output.put_line(lv_tmplevel);
  6  end;
  7  /
01

PL/SQL 过程已成功完成。

查询了METALINK，发现是ORACLE的BUG。

Bug:4458790 ORA-6502 selecting MAX/MIN into a CHAR variable in PLSQL

SELECT containing a MIN or MAX into a CHAR variable inside a PL/SQL block Fails With ORA-06502 [ID 311653.1]

 

ORACLE给出了一下3种解决方法：

1、setting initialisation parameter BLANK_TRIMMING=TRUE

2、declare PL/SQL CHAR and VARCHAR2 variable used in the INTO clause of SELECT statement as 4,000 bytes.

3、se CAST SQL function to constraint the size to that of the variable size like

SELECT CAST(MIN('Y') AS CHAR(1)) INTO C FROM DUAL;

 

SQL> declare
  2      lv_tmplevel  char(4000) ;
  3  begin
  4     select min(levno) into lv_tmplevel  from T_GCHKDEG;
  5     dbms_output.put_line(lv_tmplevel);
  6  end;
  7  /
01

PL/SQL 过程已成功完成。

SQL> declare
  2      lv_tmplevel  T_GCHKDEG.LEVNO%TYPE ;
  3  begin
  4     select cast (min(levno) as char(2)) into lv_tmplevel  from T_GCHKDEG;
  5     dbms_output.put_line(lv_tmplevel);
  6  end;
  7  /
01

PL/SQL 过程已成功完成。