[ORACLE] ORA-01722 : invalid number 오류 완전 정복

[ORACLE] ORA-01722

ORA-01722 오류란 무엇인가?

ORA-01722: invalid number 오류는 Oracle 데이터베이스에서 자주 발생하는 에러 중 하나입니다. 이 오류는 일반적으로 TO_NUMBER() 함수나 숫자형 컬럼에 문자열 데이터를 비교하거나 삽입할 때 발생합니다. 초보자뿐만 아니라 숙련된 개발자들도 실수하기 쉬운 오류로, SQL 쿼리의 성능 저하나 데이터 정확성 문제를 유발할 수 있습니다.

오류 발생 조건 및 원인

• 숫자형 컬럼에 문자형 데이터를 삽입 시도
• WHERE 절에서 숫자형 컬럼과 문자형 값 비교
• TO_NUMBER() 함수에 숫자가 아닌 문자열 입력
• 암묵적 형 변환(Implicit Conversion)으로 인해 예상치 못한 에러 발생

실제 오류 사례 분석

SELECT * FROM employees WHERE employee_id = 'ABC';

위 쿼리는 employee_id가 NUMBER 타입일 경우, 'ABC'는 숫자로 변환할 수 없는 값이므로 ORA-01722 오류가 발생합니다. 비슷한 문제는 외부에서 파라미터를 문자열로 받았을 때 흔히 발생합니다.

문제 해결 방법

1. 숫자형 컬럼과 비교 시 항상 숫자형 리터럴 또는 명시적 변환 사용
2. IS NUMERIC 또는 정규식을 통해 데이터 사전 검증
3. CASE WHEN REGEXP_LIKE(value, '^\d+$') THEN TO_NUMBER(value) ELSE NULL END 사용
4. 암묵적 형 변환을 피하고 명시적 형 변환 사용하기

실무 팁: 암묵적 형 변환 확인 방법

다음 SQL을 사용하면 컬럼 타입과 형 변환 여부를 사전에 확인할 수 있습니다:

SELECT column_name, data_type
FROM all_tab_columns
WHERE table_name = 'EMPLOYEES';
      

비교 요약표

상황	에러 발생	원인	해결 방법
숫자 컬럼과 문자 리터럴 비교	예	문자열이 숫자로 변환되지 않음	문자열을 숫자형으로 명시적 변환 또는 예외 처리
TO_NUMBER()에 잘못된 문자열	예	'ABC', '12A4' 등의 문자열	REGEXP로 필터링 후 TO_NUMBER 적용
명시적 숫자형 값 사용	아니오	형 변환 필요 없음	숫자형 값 그대로 사용

오류 방지를 위한 코드 예제

SELECT * 
FROM employees 
WHERE REGEXP_LIKE(employee_code, '^\d+$') 
AND TO_NUMBER(employee_code) = 1001;
      

위 코드는 employee_code가 숫자 형태인 경우에만 TO_NUMBER를 적용하여 안전하게 쿼리를 수행합니다.

실무자 노하우: 로그 기반 문제 추적

Oracle에서는 DBMS_SQLDIAG이나 V$SQL 뷰를 통해 문제가 발생한 SQL을 추적할 수 있습니다. 또한 PL/SQL 개발 환경에서는 EXCEPTION 처리를 통해 문제가 발생한 시점의 로그를 남겨 추후 분석에 용이하도록 설정할 수 있습니다.

마치며

ORA-01722 오류는 단순한 형 변환 문제처럼 보일 수 있으나, 대량의 데이터 처리나 실시간 쿼리 환경에서는 시스템 장애로 이어질 수 있는 위험한 오류입니다. 데이터를 다루는 모든 SQL 쿼리에 있어 데이터 타입의 일관성과 명시적 형 변환은 필수적인 요소이며, 이를 통해 시스템 안정성과 성능을 확보할 수 있습니다.

참고 자료

• Oracle® Database Error Messages 21c, Oracle Corporation
• https://docs.oracle.com/en/database/
• AskTom Oracle Forum: https://asktom.oracle.com
• Practical Oracle SQL, Kim Berg Hansen, Apress, 2019