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

On Fri, 06 Mar 1998 14:57:49 +0100, Alexander Day <ajd_at_sqlsystems.dk> wrote:

>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)
>

That is how it is suppose to work. The procedure parameters are constrained by the involking call's parameter definition. That's why you cannot constrain CHAR, VARCHAR2 and NUMBER parameters in the procedure declaration.

Solutions:

1. Ensure the passed parameter is defined to be larger that the procedure will make it.
2. Add an OUT parameter.

Oracle Press PL/SQL Programming Chapter 7, Subprograms: Procedures and Functions (pg 247-50 in O8) explains. Received on Sun Mar 08 1998 - 00:00:00 CST