Oralce的一些版本,当在函数里执行了min
/
max操作,并且这个min
/
MAX的参数是一个char型的字段
将出错,这是一个BUG , BUG号Bug 4458790
我测试了win的9.2.0.7没有问题,但是10.2.0.1由问题
SQL > select * from v$version ;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL / SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32 - bit Windows : Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
5 rows selected .
SQL > create table test ( name char ( 5 ));
Table created .
SQL > insert into test values ( 'a' );
1 row created .
SQL > commit ;
Commit complete .
SQL > create or replace function myf ( p in varchar2 ) return char
2 is
3 v_1 char ;
4 begin
5 select min ( name ) into v_1 from test ;
6 return( v_1 );
7 end myf ;
8 /
Function created .
SQL > select myf ( 'a' ) from dual ;
select myf ( 'a' ) from dual
*
ERROR at line 1 :
ORA - 06502 : PL / SQL : numeric or value error : character string buffer too small
ORA - 06512 : at "A.MYF" , line 5
解决方案
将表字段由char为varchar2型
drop table test ;
create table test ( name varchar2 ( 5 ));
insert into test values ( 'A' );
SQL > select myf ( 'a' ) from dual ;
MYF ( 'A' )
---------------------------------
A
1 row selected .[@more@]
将出错,这是一个BUG , BUG号Bug 4458790
我测试了win的9.2.0.7没有问题,但是10.2.0.1由问题
SQL > select * from v$version ;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL / SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32 - bit Windows : Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
5 rows selected .
SQL > create table test ( name char ( 5 ));
Table created .
SQL > insert into test values ( 'a' );
1 row created .
SQL > commit ;
Commit complete .
SQL > create or replace function myf ( p in varchar2 ) return char
2 is
3 v_1 char ;
4 begin
5 select min ( name ) into v_1 from test ;
6 return( v_1 );
7 end myf ;
8 /
Function created .
SQL > select myf ( 'a' ) from dual ;
select myf ( 'a' ) from dual
*
ERROR at line 1 :
ORA - 06502 : PL / SQL : numeric or value error : character string buffer too small
ORA - 06512 : at "A.MYF" , line 5
解决方案
将表字段由char为varchar2型
drop table test ;
create table test ( name varchar2 ( 5 ));
insert into test values ( 'A' );
SQL > select myf ( 'a' ) from dual ;
MYF ( 'A' )
---------------------------------
A
1 row selected .[@more@]
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/94041/viewspace-967110/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/94041/viewspace-967110/