[Oracle] ORA-01722: invalid number 원인과 문제 데이터 찾는 법 - PLUV

ORA-01722: invalid number 오류는 숫자로 변환할 수 없는 문자열을 숫자 데이터 타입으로 변환하려고 할 때 발생하는, 데이터 타입 불일치 오류입니다. 예를 들어, ‘ABC’나 ‘1,000’(쉼표 포함)과 같은 문자열에 TO_NUMBER() 함수를 사용하거나 숫자와 비교하는 경우에 발생합니다.

이 오류가 개발자를 특히 괴롭히는 이유는, 수많은 데이터 중 어떤 행의 어떤 값 때문에 오류가 발생했는지 알려주지 않는다는 점입니다. 이번 포스팅에서는 ORA-01722 오류의 발생 원인을 알아보고, 문제를 일으키는 데이터를 효율적으로 찾아내는 구체적인 방법을 제시합니다.

`ORA-01722` 오류의 주요 발생 원인

이 오류는 명시적 또는 암시적 형 변환 과정에서 발생합니다.

1. 명시적 형 변환 (Explicit Conversion) 오류

개발자가 TO_NUMBER() 함수를 사용하여 의도적으로 문자열을 숫자로 바꾸는 과정에서, 변환할 수 없는 문자가 포함된 경우입니다.

발생 예시:

SELECT TO_NUMBER('100A') FROM DUAL; 
-- ORA-01722: 수치가 부적합합니다

원인 분석: 문자열 '100A'에 숫자 외의 문자인 ‘A’가 포함되어 있어 TO_NUMBER 함수가 실패했습니다. 쉼표(,), 공백, 통화 기호 등 숫자 형식에 맞지 않는 모든 문자가 원인이 될 수 있습니다.

2. 암시적 형 변환 (Implicit Conversion) 오류

이 경우가 더 흔하고 찾기 어렵습니다. WHERE 절 등에서 문자열 타입의 컬럼을 숫자 값과 비교할 때, Oracle이 내부적으로 문자열을 숫자로 자동 변환(암시적 형 변환)하다가 오류가 발생하는 경우입니다.

발생 예시: SALES 테이블의 PRICE 컬럼이 VARCHAR2 타입일 때 (잘못된 설계)

-- PRICE 컬럼에 '5000', '7000', '3,000'(쉼표 포함) 등의 값이 저장되어 있다고 가정 
SELECT * 
FROM   SALES 
WHERE  PRICE > 4000; -- PRICE(VARCHAR2)를 숫자 4000과 비교 
-- ORA-01722: 수치가 부적합합니다

원인 분석: WHERE PRICE > 4000 조건을 평가하기 위해 Oracle은 PRICE 컬럼의 모든 값을 숫자로 변환합니다. 그러다 '3,000'과 같이 쉼표가 포함된 값을 만나면 변환에 실패하여 쿼리 전체가 중단됩니다.

핵심: 문제를 일으키는 데이터 찾는 법 🔍

오류 메시지만으로는 어떤 데이터가 문제인지 알 수 없습니다. 아래의 방법을 사용해 문제 데이터를 색출할 수 있습니다.

방법 1: 정규식 `REGEXP_LIKE` 사용 (가장 간편한 방법)

정규 표현식을 사용하여 숫자(0-9) 이외의 문자가 포함된 데이터를 간단하게 찾아낼 수 있습니다.

사용법:[^0-9]는 ‘숫자가 아닌 문자가 하나라도 포함된’이라는 의미의 정규 표현식입니다.
- 참고: 만약 소수점(.)이나 음수 부호(-)를 정상으로 간주해야 한다면 [^0-9.-]와 같이 표현식을 확장할 수 있습니다.

-- SALES 테이블의 PRICE 컬럼에서 숫자로만 구성되지 않은 모든 데이터를 조회 
SELECT PRICE 
FROM   SALES 
WHERE  REGEXP_LIKE(PRICE, '[^0-9]');

장점: 별도의 객체 생성 없이 쿼리 한 줄로 빠르게 문제 데이터를 식별할 수 있습니다.

방법 2: 사용자 정의 함수(PL/SQL Function) 생성 (가장 확실한 방법)

변환 로직을 담은 PL/SQL 함수를 만들어두면 더 복잡한 숫자 형식도 검증할 수 있고, 여러 곳에서 재사용이 가능합니다.

사용법:
1. 아래와 같이 문자열이 숫자인지 판별하는 함수를 생성합니다. 이 함수는 TO_NUMBER 변환이 성공하면 1을, VALUE_ERROR (ORA-01722와 동일한 예외)가 발생하면 0을 반환합니다.
2. 생성한 함수를 WHERE 절에서 사용하여 문제 데이터를 조회합니다.

CREATE OR REPLACE FUNCTION IS_NUMBER (p_str IN VARCHAR2) 
RETURN NUMBER 
IS 
  v_num NUMBER; 
BEGIN 
  v_num := TO_NUMBER(p_str); 
  RETURN 1; -- 변환 성공 
EXCEPTION 
  WHEN VALUE_ERROR THEN 
    RETURN 0; -- 변환 실패 
END; 
/

SELECT PRICE 
FROM   SALES 
WHERE  IS_NUMBER(PRICE) = 0;

장점: 한번 만들어두면 어떤 쿼리에서든 재사용이 가능하며, 복잡한 로직을 추가하여 확장할 수 있습니다.

근본적인 해결 및 예방 방안

문제 데이터를 찾아 수정하는 것은 임시방편입니다. 가장 근본적인 해결책은 다음과 같습니다.

올바른 데이터 타입 사용: 테이블 설계 시, 숫자 정보를 저장할 컬럼은 반드시 NUMBER 타입으로 지정해야 합니다. 이것이 ORA-01722 오류를 원천적으로 방지하는 가장 좋은 방법입니다.
데이터 입력 시 검증: 애플리케이션 단에서 데이터베이스에 데이터를 INSERT하기 전에 해당 값이 유효한 숫자인지 미리 검증하는 로직을 추가해야 합니다.

요약 ✅

ORA-01722는 숫자로 변환 불가능한 문자열을 숫자로 바꾸려 할 때 발생합니다.
가장 큰 문제는 어떤 데이터가 원인인지 찾기 어렵다는 점입니다.
**REGEXP_LIKE**를 사용하면 쿼리 한 줄로 문제 데이터를 빠르게 찾을 수 있습니다.
사용자 정의 함수를 만들면 더 안정적이고 재사용 가능한 검증 로직을 구현할 수 있습니다.
가장 중요한 예방책은 처음부터 컬럼에 올바른 데이터 타입(NUMBER)을 사용하는 것입니다.

ORA-01722 오류의 주요 발생 원인