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

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?

No, it's not a bug. The workaround is to make sure your calling process creates its parameters big enough to hold the maximum output values. And in the called procedure, use a substr(outval,1,maxlength) to ensure you don't try to send out too much text. For numeric parameters, define them as number, with no size constraints. This has become a general rule that I follow.

It sounds like you want the system to truncate your values automatically with no error issued, which might not be a bad idea, but then would you want the system to let you know that truncation was occurring? I would prefer to be notified, since something is happening that I did not plan for--which is what the ORA-06502 message is doing. I just wish they would identify the procedure being called when the error occurs.

Steve Cosner
http://members.aol.com/stevec5088 Received on Sat Mar 07 1998 - 00:00:00 CST