I have a table tblA ( ID varchar2(5), DESC varchar2(200) ) which includes data:

 '1', 'One'
 '2', 'Two'

My query is something like:

select anotherTbl.ID, T.txt
from anotherTbl, 
     (select 0 as ID, 'Undefined' as txt from dual
      select to_number(tblA.ID) as ID, tblA.DESC as txt from tblA
     ) T
where anotherTbl.extID = T.ID
and T.ID = 1 -- Actually 1 is a parameter

This query works in Oracle 12. However, in Oracle, an error ORA-01722: invalid number was raised.

More than weird, if I change to_number(tblA.ID) to to_number(trim(tblA.ID)), it would run sound in both Oracle 11 and Oracle 12!

I checked each IDs in tblA, just '1', '2', '3', ..., and '20', no spaces, no offence characters.

My guess is that Oracle 11 does not support this comparison, but could anyone please explain in a little more detail?

  • 1
    What is the data type of anotherTbl.extID? – Justin Cave Nov 6 at 18:30
  • Also, re: "Actually 1 is a parameter" -- of what data type, and how is it bound? – mustaccio Nov 6 at 18:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.