ORA-06502assigningvaluesfromSQLtoPL/SQLvariable

最近SQL查询返回的结果给PL/SQL变量出现ORA-06502错误。这个错误的描述是ORA-06502: PL/SQL: numeric or value error: character

最近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.