Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> ORA-06502. PL/SQL: Numeric or value error

ORA-06502. PL/SQL: Numeric or value error

From: Alexander Day <ajd_at_sqlsystems.dk>
Date: 1998/03/06
Message-ID: <350000DD.37E8B069@sqlsystems.dk>#1/1

Help please!

I am an ORACLE developer, currently developing an Intranet system, and I have run into a problem with IN OUT parameters in stored procedures. The problem is that when you call the procedure from a URL, the length of the IN OUT parameter is determined from what you pass as the actual parameter value. This means that when it comes time to assign a NEW value to the parameter in your procedure (since it is an IN OUT parameter), if the NEW value's length is greater than that of what was passed in, you receive a "ORA-06502: PL/SQL: Numeric or value error" error message. Most irritating. I assume that this is a bug. Has anyone else had this problem and, even more importantly, is there a solution/patch/workaround to this?

Regards,

Alexander Day

Here is a list of ORACLE 'thingies' I'm using:

ORACLE Server 7.3.3.0.0 (Windows NT 4.0 platform) ORACLE WebServer 2.1.1 (Windows NT 4.0 platform) Netscape Navigator 4.04 (Windows NT/Win95)

To help illustrate this problem, consider the code below:

procedure IN_OUT_BUG(io_our_var in out varchar2) is
begin
  io_our_var := 'Horse Man';
  htp.p(io_our_var);
end IN_OUT_BUG;

If you call this from the following URL, you will get the error message:

http://<host>:<port>/<dcd_name>/owa/IN_OUT_BUG?io_our_var=x

NOTE that the length of the passed in value for the parameter is LESS THAN the length of 'Horse Man' (the value assigned in the procedure).

If you call this from the following URL, you WON'T get the error message:

http://<host>:<port>/<dcd_name>/owa/IN_OUT_BUG?io_our_var=xxxxxxxx

NOTE that the length of the passed in value for the parameter => (length('Horse Man') - 1) Received on Fri Mar 06 1998 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US