PlSql Translate function question

Question

I've created a cursor to select the actual data, and loop through it outputting the raw value and the value after it was converted to a number. The application occasionally throws invalid number errors. Below is my test (not including the select statement) code and the output.

LOOP
  FETCH myCursor into v_answer;
  EXIT WHEN myCursor%notfound;

  DBMS_OUTPUT.PUT_LINE('Raw answer: ' || v_answer );

  v_instr := INSTR(v_answer, '.',1 , 2) ;
  v_number := TO_NUMBER(REPLACE(TRANSLATE (CASE v_instr 
                                             WHEN 0 THEN UPPER(v_answer)
                                             ELSE 0 
                                           END,'ABCDEFGHIJKLMNOPQURSTWVXYZ+<>:',' '), ' ',''));

   DBMS_output.put_line('As number: ' || v_number);

Here is the output:

Raw answer: 4
As number: 4
Raw answer: 3
As number: 3
Raw answer: 1.00
As number: 1
Raw answer: <3

I receive:

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

...when the Raw answer is '<3'.

Please note that the actual code used by the application looks like so:

AND TO_NUMBER(REPLACE(TRANSLATE ( decode( INSTR(hra_ans.answer_text, '.',1 , 2), 0 , UPPER(hra_ans.answer_text) , 0),'ABCDEFGHIJKLMNOPQURSTWVXYZ+<>:',' '), ' ','')) 

and is part of the where clause in a dynamic sql string. I've replaced the decode statement with the case statement because I was getting an function or pseudo-column 'DECODE' may be used inside a SQL statement only error.

Finally, my questions are these:

1. Why isn't the translate function replacing the less than sign and
2. What is the difference (in layman's terms) between the ORA-1722 and ORA-06502 errors?

EDIT: I've noticed that when I change the case statement to:

CASE v_instr 
                                                 WHEN 0 THEN UPPER(v_answer)
                                                 ELSE '0'

,

I no longer receive the 06502 error. From looking at the original code line I posted, are there any suggestions as to what may be causing the invalid number error (assuming that no characters exist in the string to be translated are not accounted for)? Or, is there a better way to accomplish what the original developer was attempting to do?

Here are the variable declarations:

v_answer varchar2(2000);
v_number number;
v_instr number;

Answer 1

First, the TRANSLATE isn't replacing the '<' symbol because it's not getting the chance. The CASE statement is evaluating to a number in one condition and a char in the other. If the output of your CASE is consistent, I believe your errors go away:

  v_number := TO_NUMBER(REPLACE(TRANSLATE (CASE TO_CHAR(v_instr) 
                                             WHEN '0' THEN UPPER(v_answer)
                                             ELSE '0' 
                                           END,'ABCDEFGHIJKLMNOPQURSTWVXYZ+<>:',' '), ' ',''));

From this post on asktom.com:

ORA-1722 is Invalid number. We've attempted to either explicity or implicitly convert a character string to a number and it is failing.

This can happen for a number of reasons. It generally happens in SQL only (during a query) not in plsql (plsql throws a different exception for this error).

EDIT:

Your use of regexp_replace looks good, but if I were coding this I'd use CASE instead of DECODE just because I think it's easier to read:

v_number := CASE WHEN INSTR(v_answer, '.',1 , 2) = 0 THEN -- has 0 or 1 period
                     TO_NUMER(REGEXP_REPLACE(v_answer,'[^0-9.]',''))
                 ELSE 0  -- has more than one period
            END;