同事遇到一个问题，执行了一个匿名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 过程已成功完成。