最近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记录