Re: Oracle Bug? Ora-06502 Please Help

Try replacing your CHARs with VARCHAR2

M

frankbucher_at_gmx.de (Frank Bucher) wrote in message news:<f742146.0111062252.2b106e5_at_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 - 06:48:05 CST