最近SQL查询返回的结果给PL/SQL变量出现ORA-06502错误。这个错误的描述是ORA-06502: PL/SQL: numeric or value error: character string buffer too small. 显而易见的是字符变量定义的长度不够，加到20，到100，继续06502，汗，咋回事呢？
1、问题描述
--出现问题是在一个package里，有两个参数游标，一个父游标，一个子游标，当父游标输出的结果传递值给子游标时提示值太大
--父游标原sql语句较长，且复杂，为简化描述下面构造其环境
-->Oracle 版本
goex_admin@CNMMBO> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
-->创建一个测试表t并插入3条记录
goex_admin@CNMMBO> create table t(dt char(8));
Table created.
goex_admin@CNMMBO> insert into t select '20121218' from dual;
1 row created.
goex_admin@CNMMBO> insert into t select '20121219' from dual;
1 row created.
goex_admin@CNMMBO> insert into t select '20121220' from dual;
1 row created.
goex_admin@CNMMBO> commit;
Commit complete.
-->使用下面的查询输出结果时报ora-06502错误
-->查询语句也比较简单，取表t的dt列的最小值，在外层查询赋值给变量
-->外层的子查询貌似画蛇添足，纯粹是模拟原有环境
goex_admin@CNMMBO> DECLARE
2 tradedate_out CHAR (100);
3 BEGIN
4 SELECT tradedate
5 INTO tradedate_out
6 FROM (SELECT MIN (dt) AS tradedate FROM t) d;
7
8 DBMS_OUTPUT.Put_Line ('trade_date = ' || tradedate_out);
9 END;
10 /
DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4
2、改写查询
-->如果我们去掉外层查询没有类似的错误发生
-->如此这般，难道是值由子查询到外层的时候产生了变异？
goex_admin@CNMMBO> DECLARE
2 tradedate_out CHAR (100);
3 BEGIN
4 SELECT MIN (dt) INTO tradedate_out FROM t;
5
6 DBMS_OUTPUT.Put_Line ('trade_date = ' || tradedate_out);
7 END;
8 /
trade_date = 20121218
PL/SQL procedure successfully completed.
3、尝试不同版本执行该查询
-->下面在Oracle 11g做类似模拟
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
-->Author : Robinson
-->Blog : http://blog.csdn.net/robinson_0612
SQL> create table t(dt char(8));
Table created.
SQL> insert into t select '20121218' from dual;
1 row created.
SQL> insert into t select '20121219' from dual;
1 row created.
SQL> insert into t select '20121220' from dual;
1 row created.
SQL> commit;
Commit complete.
-->同样是原来的语句，而在Oracle 11g中没有这个问题
SQL> set serveroutput on;
SQL> DECLARE
2 tradedate_out CHAR (100);
3 BEGIN
4 SELECT tradedate
5 INTO tradedate_out
6 FROM (SELECT MIN (dt) AS tradedate FROM t) d;
7
8 DBMS_OUTPUT.Put_Line ('trade_date = ' || tradedate_out);
9 END;
10 /
trade_date = 20121218
PL/SQL procedure successfully completed.
4、解决
-->为保持原有查询语句不做大量修改，通过为外层查询添加TRIM函数后问题解决
goex_admin@CNMMBO> DECLARE
2 tradedate_out CHAR (100);
3 BEGIN
4 SELECT TRIM (tradedate)
5 INTO tradedate_out
6 FROM (SELECT MIN (dt) AS tradedate FROM t) d;
7
8 DBMS_OUTPUT.Put_Line ('trade_date = ' || tradedate_out);
9 END;
10 /
trade_date = 20121218
PL/SQL procedure successfully completed.
5、Oracle BUG
-->Metalink 上溜达了一下，还真有点收获，又是一个BUG，汗....
Bug:5564384 ORA-06502 assigning values from SQL to PL/SQL variables
Component: RDBMS
Fixed Ver(s): 10204 111
Symptom(s):
- When executing a SQL that contains a concatenation / MAX of CHAR values and assigning the result to a
PL/SQL output variable an ORA-6502 may be raised.
For example:
CREATE TABLE TESTE (T1 CHAR(1), T2 CHAR(1), T3 CHAR(1));
INSERT INTO TESTE VALUES ('S', 'S', 'S');
DECLARE
V_DUMMY VARCHAR2(3);
BEGIN
SELECT MAX(X) INTO V_DUMMY FROM (SELECT T1||T2||T3 X FROM TESTE);
END;
/
^
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Available Workaround(s):
Declare the PLSQL output variables as varchar2(4000);
References:
Note:5564384.8 Bug 5564384 - ORA-6502 assigning values from SQL to PLSQL variables
更多参考
PL/SQL 联合数组与嵌套表
PL/SQL 变长数组
PL/SQL --> PL/SQL记录
