ORA-06502 assigning values from SQL to PL/SQL variables

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏me的随笔

T-SQL基础(五)之增删改

在前面的文章中对T-SQL的查询做了基本总结,接下来我们看下SQL中的另外一个常用操作——数据的修改。

10020
来自专栏乐沙弥的世界

高效SQL语句必杀技

        No SQL,No cost. SQL语句是造成数据库开销最大的部分。而不良SQL写法直接导致数据库系统性能下降的情形比比皆是。那么如何才能称得...

11120
来自专栏存储技术

MySQL查询索引分析

前一段时间修改数据表时,给一个表添加一个datetime字段,当时遇到了一个问题:我是否需要给该datetime字段上加索引呢?如果不给该字段加索引,当wher...

32260
来自专栏Albert陈凯

理解SQL原理SQL调优你必须知道的10条铁律

原文地址: http://www.nowamagic.net/librarys/veda/detail/1502 我们做软件开发的,大部分人都离不开跟数据库打...

40250
来自专栏CaiRui

Mysql-5-数据表的基本操作

1.创建表:之前需要use database database_name 然后create table 表名(); 例:创建员工表tb_employee1,结构...

25860
来自专栏乐沙弥的世界

MySQL EXPLAIN SQL 输出信息描述

12220
来自专栏文渊之博

探索SQL Server元数据(二)

  上一篇中,我介绍了SQL Server 允许访问数据库的元数据,为什么有元数据,如何使用元数据。这一篇中我会介绍如何进一步找到各种有价值的信息。以触发器为例...

12320
来自专栏沃趣科技

语句效率统计视图 | 全方位认识 sys 系统库

在上一篇《统计信息查询视图|全方位认识 sys 系统库》中,我们介绍了利用sys 系统库的查询统计信息的快捷视图,本期将为大家介绍语句查询效率语句统计信息相关的...

28550
来自专栏Linux运维学习之路

MySQL索引

索引管理 索引是什么? 索引就好比一本书的目录,它会让你更快的找到内容; 让获取的数据更有目的性,从而提高数据库检索数据的性能; 索引建立在表的列上(字段)。 ...

51150
来自专栏乐沙弥的世界

PL/SQL --> 动态SQL的常见错误

动态SQL在使用时,有很多需要注意的地方,如动态SQL语句结尾处不能使用分号(;),而动态PL/SQL结尾处需要使用分号(;),但不能使用正

10720

扫码关注云+社区

领取腾讯云代金券