Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle Bug? Ora-06502 Please Help

Re: Oracle Bug? Ora-06502 Please Help

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Wed, 7 Nov 2001 09:35:53 +0100
Message-ID: <tuhsjhoc6e4s88@corp.supernews.com>

"Frank Bucher" <frankbucher_at_gmx.de> 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.

Personal Oracle 8.1.6

SQL> create table test(bsz char(4));

Tabel is aangemaakt.

SQL> insert into test values ('AAAA');

1 rij is aangemaakt.

SQL> insert into test values(null);

1 rij is aangemaakt.

SQL> commit
  2 .
SQL> commit
  2 /

Commit is voltooid.

SQL> CREATE OR REPLACE FUNCTION TESTCHAR (BSZ CHAR) RETURN VARCHAR2 IS   2 dummy VARCHAR2(60);
  3 -- dummy2 CHAR(60);
  4 BEGIN
  5 dummy := substr(BSZ,2,3);
  6 -- dummy2 := BSZ;
  7 -- dummy := substr(dummy2,2,3);
  8 RETURN dummy;
  9 END TESTCHAR;
 10 /

Functie is aangemaakt.

SQL> select testchar(bsz) from test;

TESTCHAR(BSZ)




AAA SQL> set feedback 1
SQL> / TESTCHAR(BSZ)


AAA 2 rijen zijn geselecteerd.

SQL> insert into test values ('ABBB');

1 rij is aangemaakt.

SQL> commit
  2 /

Commit is voltooid.

SQL> select testchar(bsz) from test;

TESTCHAR(BSZ)




AAA BBB 3 rijen zijn geselecteerd.

SQL> delete from test where BSZ = 'AAAA';

1 rij is verwijderd.

SQL> commit
  2 /

Commit is voltooid.

SQL> select testchar(bsz) from test;

TESTCHAR(BSZ)



BBB 2 rijen zijn geselecteerd.

SQL> No problem here!! You are sure you posted the *exact* code? This is a transcript of cutting and pasting your code! Also your dummy variable is uninitialised and you don't have any error trapping in your function. This means you will get an untrapped 6502 if substr fails to produce a result because the string is shorter than you are specifying by hardcoding 2,3. You could as well have used substr(bsz,2) with identical results.

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address
Received on Wed Nov 07 2001 - 02:35:53 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US