• Oracle bug 使用max或min函数into到一个char类型报字符缓冲区太小的错误


    这个BUG出现会报错如下:

    selectto_char(max(RENEWAL_DATE)) intoM_YEAR_MONTH fromt_renewal_schedule;

    ORA-06502: PL/SQL:数字或值错误字符串缓冲区太小

    这个时候有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、Use 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;

    CAST是强制转换函数。

    参数BLANK_TRIMMING设置为TRUE的时候可以解决我们的BUG问题,同时这个参数设置为TRUE会在进行变量的赋值的时候忽略末尾的空格,同时这个参数必须关闭数据库进行更改。所以请评估下,是否可行。

     

    试验如下:

     

    SQL> CREATE TABLE abc (

      2  cont_no char(8),

      3  plan_id char(7));

     

    Table created

    SQL> INSERT INTO abc VALUES ('123456  ', '7654321');   ---(这里空格有2位)

    1 row inserted

    SQL> commit;

     

    SQL> DECLARE

     2     c_Plan_ID char(6);

      3  BEGIN

      4     SELECT cont_no INTO c_Plan_ID from abc;

      5     dbms_output.put_line(c_Plan_ID);

      6  END;

      7  /

    DECLARE

       c_Plan_ID char(6);

    BEGIN

       SELECT cont_no INTO c_Plan_ID from abc;

       dbms_output.put_line(c_Plan_ID);

    END;

    ORA-06502: PL/SQL:数字或值错误字符串缓冲区太小

    ORA-06512:line 5

    然后我们修改参数BLANK_TRIMMINGtrue

    alter system set BLANK_TRIMMING=true scope=spfile;

    重启数据库进行测试

    进行测试

    SQL>  DECLARE

     2         c_Plan_ID char(6);

      3      BEGIN

      4         SELECT cont_no INTO c_Plan_ID from abc;

      5         dbms_output.put_line(c_Plan_ID);

      6      END;

      7  /

     

    123456

     

    PL/SQL procedure successfully completed

    看到可以完成了

    下面是METALINK的原话

    SELECT containing a MIN or MAX into a CHAR variable inside a PL/SQL block Fails With ORA-06502 [ID 311653.1]  

    Applies to:

    Oracle Server -EnterpriseEdition - Version:10.1.0.4 to 10.1.0.4

    Oracle Server -EnterpriseEdition - Version:9.2.0.6 to 9.2.0.7

    Oracle Server -EnterpriseEdition - Version:10.2.0.1 to 10.2.0.1

    This problem can occur on any platform.

     

    Symptoms

    SELECT containing a MIN or MAX into a CHAR variable inside a PL/SQL block Fails With:

     

     

    ORA-06502: PL/SQL: numeric or value error: character string buffer too small

     

    Example:

     DECLAREC CHAR(1);BEGINSELECT MIN('Y') INTO C FROM dual;END;/DECLARE*ERROR at line 1:ORA-06502: PL/SQL: numeric or value error: character string buffer too smallORA-06512: at line 4Changes

    After upgrading to Oracle10gR210.2.0.1

    After applying10.1.0.4 patchset

    After applying9.2.0.6 patchset

    After applying9.2.0.7 patchset

    Cause

    This problem has been identified as

     

    Bug:4458790 ORA-6502 selecting MAX/MIN into a CHAR variable in PLSQL

     

    This problem is introduced in Oracle9i9.2.0.6, Oracle10g10.1.0.4 and 10.2.0.1 by the fix for Bug:3499258.

     

    A PLSQL block which SELECTs a MAX or MIN into a fixed CHAR variable can fail with an unexpected ORA-6502 "character string buffer too small" error.

    Solution

    This problem is fixed in

     

    Oracle9iR2 (9.2.0.8, terminal patchset)

    Oracle11gR1(11.1.0.x or higher)

    Oracle10gR1(10.1.0.5 or higher)

     

    Oracle10gR2 (10.2.0.2 or higher)

     

    Workarounds:

     

    setting initialisation parameter BLANK_TRIMMING=TRUE

     

     

    declare PL/SQL CHAR and VARCHAR2 variable used in the INTO clause of SELECT statement as 4,000 bytes.

     

     

    Use 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;

     

  • 相关阅读:
    ios UIImageView
    ios UILable
    [leetCode]116. 填充每个节点的下一个右侧节点指针
    [leetCode]1002. 查找常用字符
    [leetCode]199. 二叉树的右视图
    [leetCode]784. 字母大小写全排列
    [leetCode]1297. 子串的最大出现次数
    [leetCode]1239. 串联字符串的最大长度
    1095. 山脉数组中查找目标值
    [leetCode]1235. 规划兼职工作
  • 原文地址:https://www.cnblogs.com/jmax/p/3161826.html
Copyright © 2020-2023  润新知