Re: ORA-06502: PL/SQL: numeric or value error

Thanks frank. But the problem has to do with using a varchar2 as a parameter in a stored proc. For example the follwing procedure will give me an ORA-06502: PL/SQL: numeric or value error.

PROCEDURE atest(

   p_Out out varchar2)
is

        xyz varchar2(32000);
begin
  xyz:= rpad('z',256);
  p_out:=xyz;
 end;

There seems to be a 256 character limit on p_Out. Is there a workaround?

In article <38EC2539.3D6B5497_at_ntsource.com>,   Frank Hubeny <fhubeny_at_ntsource.com> wrote:
> One possible cause of the numeric or value error is that variable that
> receives the string is not large enough.
>
> For example, the following will generate the 6502 error because the
 abc
> variable is not large enough to hold a four character string. Note
 that
> the procedure has no trouble being created.
>
> -----------------------------------------------------------------
 start
> example
> SQL> create or replace procedure testerror
> 2 is
> 3 abc varchar2(3) := '1234';
> 4 begin
> 5 null;
> 6 end;
> 7 /
>
> Procedure created.
>
> SQL> exec testerror
> BEGIN testerror; END;
>
> *
> ERROR at line 1:
> ORA-06502: PL/SQL: numeric or value error: character string buffer too
> small
> ORA-06512: at "PDFWEB.TESTERROR", line 3
> ORA-06512: at line 1
> -------------------------------------------------------------- end
 example
>
> The following example shows that a varchar2 out parameter can be as
 large
> as 32000:
>
> -------------------------------------------------------------- start
> example
> SQL> create or replace procedure largevarchar2(x out varchar2)
> 2 is
> 3 i binary_integer;
> 4 v varchar2(32100);
> 5 begin
> 6 for i in 1 .. 32000 loop
> 7 v := v || 'a';
> 8 end loop;
> 9 x := v;
> 10 end;
> 11 /
>
> Procedure created.
>
> SQL> create or replace procedure receivevarchar2
> 2 is
> 3 v varchar2(32100);
> 4 begin
> 5 largevarchar2(v);
> 6 dbms_output.put_line('The size of v is ' || to_char(length
 (v)));
> 7 end;
> 8 /
>
> Procedure created.
>
> SQL> exec receivevarchar2
> The size of v is 32000
>
> PL/SQL procedure successfully completed.
> ------------------------------------------------------ end example
>
> The above was run on NT4 with Oracle 8.1.5.
>
> Frank Hubeny
>
> spareway_at_my-deja.com wrote:
>
> > Hi,
> >
> > I have written a stored procedure that uses a varchar2 as an output
> > parameter. When i assign a large string(less than 32000
 characters) to
> > the varchar2 i get a ORA-06502: PL/SQL: numeric or value error. I
> > suspect that there is a default size for a varchar out parameter,
 since
> > a small string has no problem. Is there any way to change that
 default
> > size of this paramter?
> >
> > thanks for any help.
> >
> > Craig
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Apr 08 2000 - 00:00:00 CDT