Ora 01722 invalid number updating date

assumptions were made that were not valid -- that there is a defined order of operation in SQL. 1000 * ERROR at line 4: ORA-01722: invalid number Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: RULE 1 0 MERGE JOIN 2 1 FIXED TABLE (FULL) OF 'X$KSPPCV' 3 1 FILTER 4 3 SORT (JOIN) 5 4 FIXED TABLE (FULL) OF 'X$KSPPI' but if we use CBO the query works [email protected] / VALUE -------------------------------------------------------- 1800 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=32 Card=1 Bytes=337) 1 0 NESTED LOOPS (Cost=32 Card=1 Bytes=337) 2 1 FIXED TABLE (FULL) OF 'X$KSPPI' (Cost=16 Card=1 Bytes=60) 3 1 FIXED TABLE (FIXED INDEX #2) OF 'X$KSPPCV' (Cost=16 Card=5 Bytes=1385) The explanation to this is because of the first plan involves full table scan of x$ksppcv, computing to_number() function on non-numeric values.

ora 01722 invalid number updating date-43

Ora 01722 invalid number updating date

Finally we discovered a site-dba had added an index as follows: index: IX_ADDRESS$TONUMBERLEGACY_ID expression: TO_NUMBER("LEGACY_ID") This appears to have effectively created a silent constraint.

The ORA-01722 event is so discreet about cause that you can't even identify the failing column from GUI, Command Line or Jdbc.

Be rearranging the order of the where clause i got it to work. If I have a domain table cg_ref_codes with fields domain, low_value, high_value, abbreviation, meaning (all varchar2).

If further some of my domains contain purely numeric values.

We've attempted to either explicity or implicity convert a character string to a number and it is failing. It generally happens in SQL only (during a query) not in plsql (plsql throws a different exception for this error).