ORA-01722 수치가 부적합합니다. 해결 방법

오라클 데이터베이스 사용 중 아래와 같은 오류를 만난적이 있을 것이다.

ORA-01722("invalid number") error occurs when an attempt is made to convert a character string into anumber, and the string cannot be converted into a valid number.

오늘은 오라클 오류 ORA-01722에 대한 원인과 해결방법에 대해서 알아보도록 하겠습니다.

 

 

오류 원인 및 해결방법

넘버 타입으로 변환해야 하는데 문자 값인 "원"을 포합하고 있어서 나타나는 경우

--오류 쿼리

select to_number('100원')

from dual;

 

--정상 쿼리

select to_number('100')

from dual;

 

옵티마이저의 묵시적 형변환으로 발생하는 경우

1. TEST001 이라는 테이블을 생성합니다.

나이(age)컬럼 한 개를 varchar2 (20) 포함시킵니다.

CREATE TABLE TEST001
(
    age    VARCHAR2(20)
)

 

2. 해당 컬럼에 2개의 값을 넣습니다. (11, 11a)

Insert into TEST001 VALUES('11');

Insert into TEST001 VALUES('11a');

 

3. 아래의 쿼리를 실행 합니다 .(오류가 발생함)

select * from TEST001 where age = 11;

원인은 오라클 옵티마이저가 숫자 11과 비교하기 위해서 묵시적 변환을 진행합니다.

age -> to_number(age) 이 때 age 컬럼 값에는 '11a'라는 문자가 존재하기 때문에 varchar -> number로 자동 변환하는 묵시적 변환이 불가하게 되어 오류가 발생하는 원인이다.

 

4. 쿼리 변경

아래와 같이 age의 숫자 값만 가져오고 뒤에 비교하는 11을 문자 타입으로 변경하면 오류 해결이 가능합니다.

(2가지 방법 다 가능)

select * from TEST001 where substr(age,1,2) = '11';

select * from TEST001 where substr(age,1,2) = to_char(11);

 

이상으로 오라클 쿼리 사용중 ORA-01722 수치가 부적합합니다 오류 발생시 해결하는 방법에 대해서 알아보았습니다.