[Oracle] 1. ORA-01722 invalid number

if you faces this error, check this 4 cases.

1. you put the invalid parameter.
2. miss the quotation marks.
3. When passing values from java, Without clearing the “+…+”
4. put wrong type.

Ex1)

user_id = 'user_id'
-> user_id = '123'

Ex2)

<> 1
-> <> '1'

Ex3)

'"+id+"'
-> '1122'

• you must change next all thing : <”+…+”>

Ex4)

schema

column_name   data_type

emp_no        varchar(20)
emp_nm        varchar(20)

table

emp_no        emp_nm

AA00001       Tora
1200002       James

select *
from emp
where substr(emp_no, 1, 2) = 12;

–> ERROR!! - data type is not matched. -> the left side is varchar, the right side is int. –> Fix it 2 cases.

1)

select *
from emp
where substr(emp_no, 1, 2) = ’12';

2)

select *
from emp
where substr(emp_no, 1, 2) = to_char(12);

출처 : 본인 Github 블로그 - Tora's Programming World

주소 : syjkim0125.github.io