Re: Ora-6502 Error in assigining value to varchar2 out param

From: <debashish.majumdar_at_gmail.com>
Date: 27 Aug 2006 08:08:42 -0700
Message-ID: <1156691322.922021.323650@i3g2000cwc.googlegroups.com>

Hi,
This Sp is modified a little. The same problem is still there.

  1 create or replace procedure checkit(a out varchar2) as   2 begin
  3 a := 'Deb';
  4* end;
SQL> var b varchar2
SQL> exec checkit(:b);
BEGIN checkit(:b); END;

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

Thanks in advance
Deb

Rene Nyffenegger wrote:
> On 2006-08-27, debashish.majumdar_at_gmail.com <debashish.majumdar_at_gmail.com> wrote:
> > hi,
> > I ve the following proc:
> >
> > 1 create or replace procedure checkit(a out varchar2) as
> > 2 begin
> > 3 a := 'Deb';
> > 4* end;
> >
> > When i execute this SP i am greeted by the following error:
> >
> > SQL> exec checkit(:a1);
> >
> > BEGIN checkit(:a1); END;
> >
> > *
> > ERROR at line 1:
> > ORA-06502: PL/SQL: numeric or value error: character to number
> > conversion error
> > ORA-06512: at line 1
> >
> >
> > It wd be gr8 if anyone could help me in this. Sorry for asking such
> > a basic question.
> >
> > Thanks in advance
> > Deb

> The problem is not the procedure, it's the type of the bound variable
> that you pass to the function. It seems to be a number type instead
> of a character type.

>


>     RENE> variable f number

>     RENE> exec checkit(:f)

>     BEGIN checkit(:f); END;

>


>     *

>     ERROR at line 1:

>     ORA-06502: PL/SQL: numeric or value error: character to number conversion error

>     ORA-06512: at line 1

>
>

> But if the bound variable is a varchar2, it works:

>


>     RENE> variable f varchar2(10)

>     RENE> exec checkit(:f)

>


>     PL/SQL procedure successfully completed.

>


>     RENE> print f

>


>     F

>     ------------------------------------------------

>     Deb

>
> --
> Rene Nyffenegger
> http://www.adp-gmbh.ch/
Received on Sun Aug 27 2006 - 10:08:42 CDT