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

简单描述下：主机HP 11.31 数据库ORACLE 9205 升级到10205
星期天，对一个库进行了升级；升级完，应用运行时，出现
ORA-06502:PL/SQL:数字或值错误，字符串缓冲区太小
经分析，可能是原应用程序的SQL代码使用了CHAR导致
METLINK上解释如下：

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 - Enterprise Edition - Version: 10.1.0.4 to 10.1.0.4
Oracle Server - Enterprise Edition - Version: 9.2.0.6 to 9.2.0.7
Oracle Server - Enterprise Edition - 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:


DECLARE
C CHAR(1);
BEGIN
SELECT MIN('Y') INTO C FROM dual;
END;
/


DECLARE
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4

Changes

* After upgrading to Oracle10gR2 10.2.0.1
* After applying 10.1.0.4 patchset
* After applying 9.2.0.6 patchset
* After applying 9.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 Oracle9i 9.2.0.6, Oracle10g 10.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;

References
BUG:3499258 - SQL FUNCTION FOR CHAR DATA RETURNS AS VARCHAR IN PL/SQL
BUG:4308587 - SELECT MIN / MAX FROM A CHAR VALUE RETURNS ORA-6502 IN PL/SQL
BUG:4458790 - ORA-6502 HAPPENS IN SELECT MIN FROM CHAR
PATCH:4458790 - ORA-6502 HAPPENS IN SELECT MIN FROM CHAR