Re: ORA-06502 at runtime?

On Feb 13, 11:00 am, sybrandb <sybra..._at_gmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

I was able to duplicate the problem on 9.2.0.6 on AIX 5.2 and 10.2.0.1 (Oracle Express) on Windows XP. The problem also still exists if varchar2 is used instead of varchar.

SQL> connect mark
Enter password:
Connected.
SQL> create or replace package pkg_test as   2
  3 function ret_str return varchar;
  4
  5 end;
  6 /

Package created.

SQL>
SQL> create or replace package body pkg_test as   2 c_str varchar2(5):='0123456789';
  3
  4 function ret_str return varchar is   5 begin
  6 return c_str;
  7 end;
  8
  9 end;
 10 /

Package body created.

SQL> select pkg_test.ret_str from dual;
select pkg_test.ret_str from dual

       *
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "MARK.PKG_TEST", line 2

SQL> select * from product_component_version where product like 'PL%';

PRODUCT VERSION STATUS

SQL> For what little my opiniomn is worth I agree this problem should be flagged at compile time.

HTH -- Mark D Powell -- Received on Wed Feb 13 2008 - 11:40:40 CST