Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » ORA-06502: PL/SQL: numeric or value error (Apex 4.0,win8,Oracle 10g )
ORA-06502: PL/SQL: numeric or value error [message #627850] Mon, 17 November 2014 08:36 Go to next message
Xandot
Messages: 235
Registered: January 2014
Location: India
Senior Member
Hello,

I have create one validation for check the range of ":P101_value" (Datatype is varchar2(200) ) but when I write text in :P101_value and click to apply button than system raise error ORA-06502,Please Help me out.

 
if :P101_value not between :P101_LLimit and :P101_ULimit then
    return 'Value must be between '||:P101_LLimit||' and '||:P101_ULimit;
 else
    return null;
 end if;


Thanks,
Xandot
Re: ORA-06502: PL/SQL: numeric or value error [message #627866 is a reply to message #627850] Mon, 17 November 2014 10:37 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oracle

ORA-06502: PL/SQL: numeric or value error string

Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER(2).

Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.


Do these 3 item's datatypes match? If P101_VALUE is VARCHAR2, what are P101_LLIMIT and P101_ULIMIT?

BETWEEN suggests that you are, actually, comparing numbers. If that's so, why didn't you choose a NUMERIC FIELD (instead of TEXT)? If you still want to enter strings, are you aware of the fact that '14' is between '1' and '2'?

Did you run that page in debug mode? If not, perhaps you should have. It'll tell you what is going on and - hopefully - which part of it raises an error.

See whether everything you read above helps. If not, provide some more details which would enable us to simulate what you are doing and see what happens.
Re: ORA-06502: PL/SQL: numeric or value error [message #627903 is a reply to message #627866] Tue, 18 November 2014 01:08 Go to previous messageGo to next message
Xandot
Messages: 235
Registered: January 2014
Location: India
Senior Member
In :P101_Value sometime it takes string and sometime it takes number value that's why datatype of :P101_Value is varchar2 but the other two are P101_LLIMIT and P101_ULIMIT numeric fields.
When the user insert a number into P101_value than the range validation working correctly but when user insert character than only error raised.


I am not able to change the datatype of :P101_Value because its database column.Is there any solution for this problem?
Re: ORA-06502: PL/SQL: numeric or value error [message #627905 is a reply to message #627903] Tue, 18 November 2014 01:15 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So, lower limit = 100 and upper limit = 200. You are saying that it is OK to enter 150 into VALUE (which passes the validation), but it is OK to enter 'ax335' or 'Littlefoot' or 'Dodge Viper' into VALUE? Although it is OK to validate whether 'Dodge Viper' is between 100 and 200 (it is not), but - does it make sense? I don't see any.

Therefore, why don't you set limit items to be VARCHAR2 as well?
Re: ORA-06502: PL/SQL: numeric or value error [message #627909 is a reply to message #627905] Tue, 18 November 2014 02:02 Go to previous messageGo to next message
Xandot
Messages: 235
Registered: January 2014
Location: India
Senior Member
Same condition here like http://www.orafaq.com/forum/t/194909/.
When Its number type than check the limit and if user insert a string than it'll raise message like "Field should be numeric" and if its varchar2 than its Ok.
Re: ORA-06502: PL/SQL: numeric or value error [message #627918 is a reply to message #627909] Tue, 18 November 2014 03:01 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In other words: if you mix data types, you have problems. As you don't want to make them uniform, try to use TO_CHAR or TO_NUMBER in validation process and see whether it'll help.
Re: ORA-06502: PL/SQL: numeric or value error [message #627950 is a reply to message #627918] Tue, 18 November 2014 04:12 Go to previous messageGo to next message
Xandot
Messages: 235
Registered: January 2014
Location: India
Senior Member
Thanks littlefoot. Its working with the help of to_number.
Re: ORA-06502: PL/SQL: numeric or value error [message #627959 is a reply to message #627950] Tue, 18 November 2014 04:44 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Of course, it will fail as soon as you pass anything but numbers to it.
Previous Topic: running pl/sql in apex.oracle
Next Topic: APEX Help
Goto Forum:
  


Current Time: Thu Mar 28 04:51:49 CDT 2024