Re: ORA-06502 at runtime?

From: sybrandb <sybrandb_at_gmail.com>
Date: Wed, 13 Feb 2008 08:00:15 -0800 (PST)
Message-ID: <cc3f9f22-9431-42df-b34b-e345dc01fcdb@1g2000hsl.googlegroups.com>


On Feb 13, 3:57 pm, steph <stepha..._at_yahoo.de> wrote:
> Here is an example I would like to habe an opinion about (version is
> 9.2.0.6.0):
>
> create or replace package pkg_test as
>
>   function ret_str return varchar;
>
> end;
> /
>
> create or replace package body pkg_test as
>   c_str varchar2(5):='0123456789';
>
>   function ret_str return varchar is
>   begin
>     return c_str;
>   end;
>
> end;
> /
>
> Obviously 'c_str' is defined too small for the string assigned. But
> still, the package compiles just fine.
>
> But when I call the function I get an error:
>
> SQL> select pkg_test.ret_str from dual;
> select pkg_test.ret_str from dual
>        *
> FEHLER in Zeile 1:
> ORA-06502: PL/SQL: numerischer oder Wertefehler: Zeichenfolgenpuffer
> zu klein
> ORA-06512: in "PFMS.PKG_TEST", Zeile 2
>
> So why is the package compiling without error? Why is the error
> appearing at runtime?
>
> thanks,
> stephan

Your 'constant' is varchar2 and you return a varchar. While varchar does exist as a datatype, it has never been in use, and even Oracle recommends not to use it.
This might be a 'hole' in PL/SQL, which you would need to verify from Metalink.
As you are not on the latest patchlevel and Oracle removed 9i from premier support, this potential bug is not going to be resolved.

--
Sybrand Bakker
Senior Oracle DBA
Received on Wed Feb 13 2008 - 10:00:15 CST

Original text of this message