ORA-01722: 수치가 부적합합니다

시스템 운영 중에 ORA-01722 라는 오류를 접하게 되었습니다.

이미 알고 있는 내용도 있었지만, 어떤 오류이고, 어떻게 해결할 수 있는지 한 번 정리해 보았습니다.

그럼 ORA-01722 오류는 무엇일까요?

오류 로그 상으로는 “ORA-01722: 수치가 부적합합니다” 라고 로그가 남습니다.

무슨 의미일까요? 일단 oracle docs 에서는 아래와 같이 설명하고 있습니다.

AnORA-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. Valid numbers contain the digits '0' through '9', with possibly one decimal point, a sign (+ or -) at the beginning or end of the string, or an 'E' or 'e' (if it is a floating point number in scientific notation). All other characters are forbidden. There are numerous situations where this conversion may occur. A numeric column may be the object of an INSERT or an UPDATE statement. Or, a numeric column may appear as part of a WHERE clause. It is even possible for this error to appear when there are no numeric columns appearing explicitly in the statement!

출처 : http://www.orafaq.com/wiki/ORA-01722

내용인 즉, 문자열(String) 을 숫자(Number) 로 변환할 때 발생하는 오류라고 되어 있습니다.

무슨 말인지 이해가 되지 않는다면 예를 한 번 들어보겠습니다.

— 오류발생 select to_number(‘1,234,567,890’) from dual;

— 정상수행 select to_number(‘1234567890’) from dual;

왼쪽 SQL 을 수행하면 ORA-01722 오류가 발생합니다.

그 이유는 ‘1,234,567,890’ 값이 문자를 포함하고 있기 때문입니다.

숫자는 0~9와 +/- 만으로 구성된 값을 의미하는데, 위에서는 Comma(,) 를 포함하고 있기 때문입니다.

또 다른 예를 들어보겠습니다.

select 'abc' - 123 from dual;

이 또한 오류가 발생합니다.

사칙연산을 수행하기 위해서 ‘abc’ 를 숫자로 변환(묵시적 형변환)을 시도하게 되는데, abc 라는 문자열로 구성되어 있어 오류가 발생하는 것입니다.

이 오류를 어떻게 해결할 수 있을까요?

형병환이 발생하는 시점에서는 Column 이나 데이터 타입을 잘 확인해야 합니다.

명시적 형변환의 경우, 예를 들어 to_number() 같은 경우는 오류 발생시 찾기도 어렵지 않으며, 확인하기도 쉽습니다.

하지만 묵시적 형변환의 경우는 잘 눈에 띄지 않으며, Column 과 데이터 타입을 잘 확인하지 않으면 찾기도 쉽지 않습니다.

묵시적 형변환시 ORA-01722 오류 예제를 한 번 살펴보겠습니다.

아래와 같은 Schema 가 있다고 가정하겠습니다.

위처럼 데이터를 넣어 두었습니다.

앞 2자리가 어떤 의미를 갖는다고 할 때, “12” 인 직원을 조회한다면 아래와 같이 SQL 을 작성할 수 있습니다.

(예를 들어 만든 샘플이며, 실무에서 이렇게 사용하는 경우는 없습니다. 오류를 만들어 내기 위한 시나리오라고 생각하시면 됩니다.)

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

예상하신 대로 오류가 발생합니다.

왜 그럴까요?

그 이유는 좌변인 substr(emp_no, 1, 2) 는 문자열(VARCHAR2형) 이며, 12는 숫자형(NUMBER) 형으로 좌변을 숫자형으로 변환하는 묵시적 형변환이 발생합니다.

즉, DB 는 to_number(substr(amp_no, 1, 2)) 와 같은 시도를 하게 되는 것입니다.

하지만 데이터를 살펴보면 숫자로 변환할 수 없는 문자(AA00001 - 홍길동) 을 포함하고 있기 때문에 오류가 발생하는 것입니다.

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

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

위와 같이 수정하여 수행하면 정상적으로 수행됩니다.

정리하며...

Optimizer 에 의해 명시적 형변환이 발생하도록 두는게 나을지 명시적 형변환으로 명확하게 해야할 지 아직도 잘 모르겠습니다만, 오류를 방지하기 위해서는 명시적 형변환을 사용하는 것이 좋다는 생각을 해 보았습니다.

물론 형변환 자체가 발생하지 않도록 DataType 을 잘 구성한다면 더 좋을 것입니다.

P.S 구글링을 해 보면서 접하게 된 케이스인데, 테이블 Join 시 키 값이 두 테이블에서 Type 이 다른 경우가 종종 있다는 이야기가 있었습니다.

지양해야 할 케이스이지만, 불가피하다면 Join 시 명시적 형변환을 사용하는 것이 오류를 방지하는데 좋은 방법일 것 같습니다.

---

참고자료

• http://www.orafaq.com/wiki/ORA-01722
• http://www.gurubee.net/article/22192
• http://gywn.net/2012/09/dbms-implicit-type-conversion/