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

From: Frank Hubeny <fhubeny_at_ntsource.com>
Date: 2000/04/06
Message-ID: <38EC2539.3D6B5497@ntsource.com>#1/1

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.
Received on Thu Apr 06 2000 - 00:00:00 CDT