Oracle Bug? Ora-06502 Please Help

From: Frank Bucher <frankbucher_at_gmx.de>
Date: 6 Nov 2001 22:52:23 -0800
Message-ID: <f742146.0111062252.2b106e5@posting.google.com>

Hi all,

we're using Racle 8.1.7 and NT4 on server and clientside. Now we have detected a strange behaviour of Oracle. Maybe it's bug or maybe we're just too stupid (hopefully no):

First we're creating a table and filling it with some data:

create table test (BSZ CHAR(4));

insert into test values ('AAAA');

insert into test values (NULL);

commit;

Then we have a function (o.k. it's a small example):

CREATE OR REPLACE FUNCTION TESTCHAR (BSZ CHAR) RETURN VARCHAR2 IS dummy VARCHAR2(60);
-- dummy2 CHAR(60);
BEGIN

	 dummy := substr(BSZ,2,3);


--	 dummy2 := BSZ;

--	 dummy := substr(dummy2,2,3);

	 RETURN dummy;

END TESTCHAR; Trying to use the function via select works fine:

select testchar(BSZ) from test;         

Adding another row and trying again still works fine:

insert into test values ('ABBB');

commit;

select testchar(BSZ) from test;

But when I delete the first row (it seems to be important that the first selected row contains null):

delete from test where BSZ = 'AAAA';

commit;

select testchar(BSZ) from test;

The function calls fails:

ERROR:
ORA-06502: PL/SQL: numerisch oder Wertefehler : Zeichenfolgenpuffer zu klein
ORA-06512: in "TESTCHAR", Zeile 8
ORA-06512: in Zeile 1

(sorry that this in German).

if I call:

select testchar(nvl(BSZ,null)) from test;

anything is fine, as well as when I take the way over dummy2 in the function.
Oh and don't try:

select testchar(bsz) from test where bsz is null;

it'll work too.
Now I'am really disturbed. Have we missed sth.? Any help appreciated.

TIA
Frank.

PS: Could you please answer via email (frankbucher_at_gmx.de)? It would be great help. Received on Wed Nov 07 2001 - 00:52:23 CST