Oracle-L: Re: NUMERIC_OR_VALUE_ERROR

From: <Jared.Still_at_radisys.com>
Date: Wed, 13 Mar 2002 11:29:28 -0800
Message-ID: <F001.00427BB4.20020313112928@fatcity.com>

This has *always* been an application error in my experience.

You have the correct reason, sloppy programming.

Here's a demo script I wrote a few years ago to demo it.

HTH Jared

6502.sql
demonstrate common causes of ORA-6502
Jared Still
See note # 20063.1 on MetaLink

drop table t6502;
create table t6502 ( char_data varchar2(20), number_data number(6) );

insert into t6502 values('100', 10000);

set serveroutput on size 1000000

declare

small_char varchar2(2);
char_ok t6502.char_data%type;

small_int number(2);
number_ok t6502.number_data%type;

begin

varchar2 data begin
- this one succeeds select char_data into char_ok from t6502;
- this one fails select char_data into small_char from t6502;

exception

      when value_error then
         dbms_output.put_line('The value ' || char_ok || ' is too large

for the character variable small_char');

end;

numeric data begin
- this one succeeds select char_data into number_ok from t6502;
- this one fails select char_data into small_int from t6502;

exception

      when value_error then
         dbms_output.put_line('The value ' || number_ok || ' is too large

for the numeric variable small_int');

end;
end;
/

drop table t6502;

"Jackson Dumas" <tjaros_at_webmail.co.za>
Sent by: root_at_fatcity.com
03/13/02 04:48 AM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        NUMERIC_OR_VALUE_ERROR_ORA-6502 !!!

Yeah

My clients had a problem a couple weeks ago for the same application but different databases on different platforms, different weeks.

First occurance on DB1 : This happend on one user's machine and it came out that his PC was not properly setup. DB1 was running on WINDOWS NT. Second occurance on DB2 (running same application as DB1) : Here DB2 is running on HP-UX 10.20 and nothing was generated on alert.log and no trace files were there. During the investigation we ended not being able to connect to box (machine) and thus as a result the box bounced itself. Then I started the databases (by the way there are two databases running here, but only the one where the application running on DB1 is running had a problem but the other was fine) and the everything worked fine.

Third occurance on DB3 (running same database as DB1 and DB2) : This database is ruuning on Window NT.Here also no errors on alert log and trace files. The work around was that they loaded other stored procedures and it worked.

According to metalink the error reported was because of datatype kinda error where one is trying to select, fetch or assign a value that is longer (larger) than the declared size of a variable.

Now how sure can I be that the errors is really pointing to the application and convince the application developers to fix it. There are other regions running the same application but not yet impacted by the same error. I send them the document form metalink but they seem not convinced. How do I handle this error or help them.

Thank you,

Junior ORACLE DBA

http://www.webmail.co.za the South-African free email service
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Jackson Dumas
INET: tjaros_at_webmail.co.za

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Jared.Still_at_radisys.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

Re: NUMERIC_OR_VALUE_ERROR_ORA-6502 !!!