ORA-01722: 잘못된 숫자 형식입니다(invalid number)

ORA-01722: 잘못된 숫자 형식입니다(invalid number)

ORA-01722 오류는 오라클 데이터베이스에서 발생하는 오류 중 하나로, "잘못된 숫자 형식입니다(invalid number)"라는 의미입니다. 이 오류는 숫자형 데이터가 필요한 곳에 숫자로 변환할 수 없는 문자열이 있을 때 발생합니다. 즉, 숫자로의 암시적 또는 명시적 형변환이 실패하는 상황입니다.

 

ORA-01722 오류의 주요 원인

• 숫자형 컬럼에 문자 데이터 저장 : 숫자형으로 정의된 컬럼에 숫자로 변환할 수 없는 문자 데이터가 저장되어 있는 경우 발생합니다. 예를 들어, NUMBER 타입의 컬럼에 'ABC'와 같은 문자열이 저장되어 있다면, 해당 컬럼을 사용하는 연산이나 쿼리에서 ORA-01722 오류가 발생할 수 있습니다.
• 문자열을 숫자로 변환 시 오류 : TO_NUMBER 함수를 사용하여 문자열을 숫자로 명시적으로 변환하려고 할 때, 문자열이 숫자로 변환될 수 없는 형식이면 오류가 발생합니다. 예를 들어, TO_NUMBER('123A')는 오류를 발생시킵니다.
• 비교 연산 시 데이터 타입 불일치 : 숫자형 데이터와 문자형 데이터를 비교하려고 할 때 암시적 형변환이 시도되는데, 문자형 데이터가 숫자로 변환될 수 없으면 오류가 발생합니다. 예를 들어, WHERE 숫자컬럼 = 'ABC'와 같은 조건에서 오류가 발생할 수 있습니다.
• 함수 인자 타입 불일치 : TRUNC, MOD, ROUND, CEIL 등의 숫자 관련 함수에 문자형 데이터를 인자로 전달했을 때, 해당 데이터가 숫자로 변환될 수 없으면 오류가 발생합니다.
• SQL Loader 사용 시 데이터 형식 불일치 : SQL Loader를 사용하여 데이터를 로드할 때, 숫자형 컬럼에 숫자로 변환할 수 없는 데이터가 포함된 파일에서 데이터를 로드하려고 하면 오류가 발생합니다.

ORA-01722 오류 해결 방법

 

1. 데이터 검사 및 수정 : 오류가 발생하는 컬럼의 데이터를 확인하여 숫자로 변환할 수 없는 데이터가 있는지 확인합니다. 다음과 같은 쿼리를 사용하여 문제를 일으키는 데이터를 찾을 수 있습니다. 문제가 되는 데이터를 수정하거나 삭제하여 숫자 형식으로 맞춰주어야 합니다.

 

2. 아래 쿼리

SELECT *
FROM 테이블이름
WHERE REGEXP_LIKE(컬럼이름, '[^0-9]'); -- 숫자가 아닌 문자가 포함된 행 찾기 (정규 표현식 사용)
-- 또는
SELECT *
FROM 테이블이름
WHERE NOT REGEXP_LIKE(컬럼이름, '^[+-]?\d*(\.\d+)?$'); -- 숫자 형식이 아닌 모든 행 찾기

 

3. TO_NUMBER 함수 사용 시 오류 처리 : TO_NUMBER 함수를 사용할 때, 변환 대상 문자열이 숫자로 변환될 수 있는지 미리 확인하거나, 예외 처리를 사용하여 오류를 방지할 수 있습니다. VALIDATE_CONVERSION 함수를 사용하여 변환 가능 여부를 확인하는 것이 좋습니다.

 

4. 아래 쿼리

SELECT CASE
           WHEN VALIDATE_CONVERSION('123A' AS NUMBER) = 1 THEN TO_NUMBER('123A')
           ELSE NULL -- 또는 다른 처리
       END AS converted_number
FROM dual;

5. 비교 연산 시 명시적 형변환 : 숫자와 문자를 비교할 때는 명시적으로 형변환을 수행하여 데이터 타입 불일치로 인한 오류를 방지합니다.

 

6. 아래쿼리

SELECT *
FROM 테이블이름
WHERE 숫자컬럼 = TO_NUMBER('123'); -- 문자열 '123'을 숫자로 변환

 

7. 함수 인자 타입 확인 : 숫자 관련 함수에 전달하는 인자의 데이터 타입을 확인하고, 필요한 경우 명시적 형변환을 수행합니다.
8. SQL Loader 제어 파일 수정 : SQL Loader를 사용하는 경우, 제어 파일에서 데이터 타입을 명확하게 지정하고, 필요에 따라 EXTERNAL 데이터 유형을 사용하여 문자 데이터를 숫자 데이터로 변환하도록 설정합니다. 데이터 파일에 숫자로 변환할 수 없는 데이터가 포함되어 있다면, 데이터 파일을 수정하거나, SQL Loader의 전처리 기능을 사용하여 데이터를 정제해야 합니다.

예시

• EMPLOYEES 테이블의 salary 컬럼이 NUMBER 타입이라고 가정해 보겠습니다.

-- 오류 발생
SELECT * FROM EMPLOYEES WHERE salary = 'abc';

-- 해결 방법 (문자열 '1000'과 비교하는 경우)
SELECT * FROM EMPLOYEES WHERE salary = TO_NUMBER('1000');

-- 데이터에 문제가 있는 경우 (salary 컬럼에 'abc'와 같은 값이 저장되어 있는 경우)
SELECT * FROM EMPLOYEES WHERE NOT REGEXP_LIKE(salary, '^[+-]?\d*(\.\d+)?$'); -- 문제 데이터 찾기

 

※ ORA-01722 오류는 데이터 타입 불일치로 인해 발생하는 오류이므로, 데이터의 형식을 정확하게 관리하는 것이 중요합니다. 오류 메시지를 주의 깊게 읽고, 위에서 설명한 해결 방법을 참고하여 문제의 원인을 파악하고 해결할 수 있습니다. 특히, 데이터 검사, 명시적 형변환, 예외 처리를 통해 오류를 예방하는 것이 좋습니다.