Character to number conversion error

Question

declare
   l_tot number := 0;

begin
   for i in 1..apex_application.g_f08.count loop
      l_tot := l_tot + nvl(to_number(apex_application.g_f08(i)),0);
   end loop;

 if l_tot = nvl(to_number(:P21_TOTAL_PRICE),0) then
      return true;
   else
      return false;
   end if;
end;

Got below error with above code

ORA-06502: PL/SQL: numeric or value error: character to number conversion error

Error occurred with :P21_TOTAL_PRICE. What is the wrong ? How can i correct this ?

do you have commas or spaces in the price field? – Sathyajith Bhat Jan 31 '12 at 05:38 — Sathyajith Bhat, Jan 31 '12 at 05:38
@Sathya Yes. Value in `:P21_TOTAL_PRICE` like `5,500.00` – Bishan Jan 31 '12 at 05:42 — Bishan, Jan 31 '12 at 05:42

score 4 · Answer 1 · edited May 23 '17 at 11:51

Rather than using REPLACE you should use the more powerful REGEXP_REPLACE function. http://www.orafaq.com/wiki/REGEXP_REPLACE

You can then remove any non-numeric character from the string before then using the TO_NUMBER function.

In your case it would be something like:

REGEXP_REPLACE(:P21_TOTAL_PRICE, '[^0-9]+', '');

See my answer to almost the exact same question here: Oracle To_Char function How to handle if it's already a string

score 2 · Accepted Answer · answered Jan 31 '12 at 06:00

The error rises because the number that you're representing is actually a character string involving commas etc. When you put a to_number to that, Oracle cannot replace the commas.

You might want to use replace function to strip off the commas

Change

if l_tot = nvl(to_number(:P21_TOTAL_PRICE),0) then

to

if l_tot = nvl(to_number(replace(:P21_TOTAL_PRICE,',','')),0) then

Character to number conversion error

2 Answers2