Wierd ORA-06502 error ...

From: anders <andersbj_at_danbbs.dk>
Date: 22 Feb 2006 02:36:25 -0800
Message-ID: <1140604585.863184.191340@o13g2000cwo.googlegroups.com>

Hi

I am having a strange problem with a 10g server:

create table tt1 (i char(1));
create table tt2 (i char(1 char));
create table tt3 (i varchar2(1));

insert into tt1 values('Æ'); insert into tt1 values('J');

insert into tt2 values('Æ'); insert into tt2 values('J'); insert into tt3 values('Æ'); insert into tt3 values('J');

DECLARE
var1 char(1 CHAR);
BEGIN
select min(i) into var1 from tt1;
END;

• OUTPUT --- Error report: ORA-06502: PL/SQL: numerisk fejl eller v?rdifejl: tegnstrengsbufferen er for lille ORA-06512: ved linje 4

  ---

I get this error when I select from table tt1 and tt2 into variable var1. BUT when I select from tt3 into var1 there is no problem ...

If I change nls_length_semantics (CHAR/BYTE) the result is the same.

Finally, the following - rather trivial - block runs succesfully:

DECLARE
var1 char(1 CHAR);
BEGIN
select SUBSTR(min(i),1,1) into var1 from tt1; END; To summarize the conversions/assigments:

char(1) --> char(1 char)               : Error
char(1 char) --> char(1 char)        : Error
varchar2(1) --> char(1 char)          : OK

SUBSTR(char(1),1,1) --> char(1 char) : Error

Any suggestions to the above errors???

Thanks,
Anders Received on Wed Feb 22 2006 - 04:36:25 CST