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_TRIMMING为true
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;
