-
1. Re: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
aJohny Jun 3, 2015 8:05 AM (in response to Rajesh123)1 person found this helpfultry with v_no:= v_no||lpad(NVL(to_char(c_rec.order_no),' '),15);
-
2. Re: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
BluShadow Jun 3, 2015 8:46 AM (in response to Rajesh123)1 person found this helpfulYou said it yourself, the c_rec.order_no is a number data type, but yet your NVL statement is saying "give me the value of c_rec.order_no, but if it's null set it to a single space character", yet a single space character is not a numeric value, so you cannot convert a space to match the datatype (number) of c_rec.order_no.
However if you stop abusing implicit conversions of datatypes and do it correctly, you'd have something like:
v_no:= v_no||lpad(NVL(to_char(c_rec.order_no),' '),15,' ');
That way, you are converting the c_rec.order_no into a string datatype first before checking if it's null to make it a space character. The result of the NVL is therefore a string which is what is needed for the LPAD function, so that's the way to do it.
-
3. Re: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
Rajesh123 Jun 3, 2015 9:08 AM (in response to BluShadow)Thanks to both..