Re: Ora-06502

LEE_HARRIS <lee_harris52_at_hotmail.com> wrote in message news:<2916417.1053711384_at_dbforums.com>...
> I have a client using a intranet web site running on W2000 Server, ADO
> 2.6, IIS/5.0, Script Engine 2.1 - connecting to an oracle database
> 9.02, and performing updates. The client intermittently gets a PL/SQL
> error - invalid number or value:, in a previous answer to a similar
> error someone else had, someone stated that this could be due to a ','
> being understood to be a '.' when the data is being sent to Oracle from
> a string i.e. '200,10' and oracle doesn't understand that the , should
> be treated as a decimal. Firstly, can this be the case. Secondly, how
> would I check this out - as the clients using the web server are from
> multinational locations which may be using the , as a decimal
> indicator. Thirdly, could ADO be breaking down somewhere when passing
> the data through to Oracle and simply sending incomplete update
> parameters i.e. NULLS where there shouldn't be. This is quite confusing
> as the problem is intermittent and restarting Oracle, i.e. releasing
> all the connections resolves the problem. This problem also surfaced
> about 9months ago when the database was 8.17. If the first question is
> yes, how do I go about telling Oracle that , are can also be decimals?
> I understand the general reasons for receiving this error, invalid
> field sizes, invalid conversion between strings and numerics, but this
> is very strange
>
> Thanks in advance for any help

The most likley cause of a 06502 error is either a varchar column that is attempted to be converted to a number and the char column/variable contains a non-number character, that is, it does not consist soley of the digits 0 - 9 or other recogned character such as negative sign.

The next likely cause is trying to nove a larger value into a variable or column defined too small to hold the data.

If you are moving data from Europe where ',' is the decimal indicator to the US where '.' is the decimal indicator I would transform the values first.

Oracle has nls_parameter settings for these items that can be defined at the session level and handled in format masks used by to_number and to_char.

HTH -- Mark D Powell -- Received on Fri May 23 2003 - 19:30:19 CDT