Re: PL/SQL - IN OUT PARAMETER problem: ORA-06502: PL/SQL: Numeric or value error

From: <ben.salemi_at_gcgc.com>
Date: 1998/03/09
Message-ID: <6e0v52$j1t$1@nnrp1.dejanews.com>#1/1

I believe the following will fix your problem.

Creat a package -- say default_pkg

which has a variable -- say default_string varchar2(50);

declare your procedure as follows

procedure IN_OUT_NO_BUG(io_our_var in out default_pkg.default_string%type)

That should pin the size of the variable.

Hope this helps.

Ben Salemi
Global Consulting Group

---

Ben.Salemi_at_gcgc.com
Contact us for turn-key, value-added custom software.

---

In article <35016A68.2C06657E_at_sqlsystems.dk>,   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)
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Mon Mar 09 1998 - 00:00:00 CST