ORA-01722 invalid number — 숫자 변환 실패 에러 원인·해결·예방

ORA-01722 invalid number — 숫자 변환 실패 에러 원인·해결·예방

Oracle Database에서 SQL을 실행하다가 ORA-01722: invalid number 에러를 만나는 경우가 있다. 단순한 TO_NUMBER 변환 실패뿐 아니라 WHERE 절 암시적 변환, NLS 설정 충돌, 옵티마이저의 술어 재배열, JOIN 타입 불일치처럼 다양한 원인이 같은 에러로 표시되어 진단이 까다롭다.

 

본 글은 ORA-01722의 정확한 의미와 자주 발생하는 6가지 케이스, 단계별 디버깅, 안전한 변환 패턴(VALIDATE_CONVERSION·REGEXP_LIKE·CASE WHEN), NLS 영향, 유사 에러 ORA-06502와의 차이까지 정리한 트러블슈팅 자료다.

 

ORA-01722

이 글의 구성

 

01에러 메시지와 카테고리

02발생 원인 6가지

03재현 시나리오와 코드 예제

047단계 디버깅 순서

05안전한 변환 패턴 4가지

06NLS 영향과 ORA-06502 차이

Q&A자주 묻는 질문 5가지

---

01 에러 메시지와 카테고리

ORA-01722는 Oracle Database가 문자열을 숫자로 변환하려고 시도했으나 입력 값이 유효한 숫자 형식이 아닐 때 발생한다. SQL 엔진 단계에서 발생하는 형 변환 오류다.

항목	내용
에러 코드	ORA-01722
영문 메시지 (기본)	invalid number
영문 메시지 (12.2 이상)	unable to convert string value containing invalid_char to a number: column_or_expression
한국어 의미	문자열을 숫자로 변환할 수 없음
Cause (공식)	The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions.
Action (공식)	Check that only valid numeric characters (0-9, sign, decimal, E/e) are present and re-execute.
발생 영역	SQL SELECT·INSERT·UPDATE·DELETE·MERGE의 모든 변환 지점

핵심 관찰

ORA-01722는 명시적 TO_NUMBER뿐 아니라 암시적 변환에서도 발생한다. WHERE varchar_col = 100처럼 비교 연산자만 써도 Oracle이 내부적으로 VARCHAR2 → NUMBER 변환을 시도하기 때문이다. 옵티마이저가 술어 순서를 재배열하면 변환이 의도와 다른 행에서 실행되어 갑자기 에러가 터지는 경우도 있다.

---

02 발생 원인 6가지

원인 1 — 암시적 변환 (WHERE 절)

VARCHAR2 컬럼을 숫자 리터럴과 비교할 때 Oracle이 컬럼 값을 숫자로 변환하려다 비숫자 행에서 실패한다. 가장 자주 발생하는 패턴이다.

원인 2 — TO_NUMBER 직접 호출

명시적으로 TO_NUMBER를 호출했는데 입력 문자열에 알파벳·기호가 섞여 있는 경우다.

원인 3 — 공백·통화기호·콤마 포함

'1,234.56'·'$100'·' 100 '처럼 콤마·통화기호·공백이 섞이면 기본 TO_NUMBER가 실패한다. 포맷 마스크를 명시해야 한다.

원인 4 — NLS 설정 차이 (유럽식 vs 미국식)

세션의 NLS_NUMERIC_CHARACTERS가 콤마를 소수점으로 정의해 둔 경우(유럽식 ,.), 미국식 '1.5'가 변환에 실패할 수 있다. 클라이언트와 서버 NLS가 다르면 동일 SQL이 환경별로 다르게 작동한다.

원인 5 — JOIN 조건 타입 불일치

두 테이블의 조인 컬럼 데이터 타입이 NUMBER와 VARCHAR2로 다른 경우, 옵티마이저가 VARCHAR2 → NUMBER 암시 변환을 시도해 비숫자 데이터에서 실패한다.

원인 6 — 옵티마이저 술어 재배열

WHERE type='N' AND TO_NUMBER(val) > 0처럼 필터 후 변환을 의도했지만, CBO가 술어 순서를 바꿔 변환을 먼저 적용하면 다른 행에서 에러가 터진다. View나 Join Predicate Pushdown에서 자주 발생한다.

---

03 재현 시나리오와 코드 예제

원인 1 — 암시적 변환

-- 테이블 구조: acct_no VARCHAR2(20)
-- 데이터: '100', '200', 'A001' (혼합)

-- ✗ 숫자 리터럴과 비교 → 'A001'에서 ORA-01722
SELECT * FROM accounts WHERE acct_no = 100;

-- ✓ 문자열 리터럴로 비교
SELECT * FROM accounts WHERE acct_no = '100';

원인 2 — TO_NUMBER 직접 호출

-- ✗ 알파벳 포함
SELECT TO_NUMBER('ABC123') FROM dual;
-- ORA-01722

-- ✓ 숫자 부분만 추출 후 변환
SELECT TO_NUMBER(REGEXP_REPLACE('ABC123', '[^0-9]', '')) FROM dual;

원인 3 — 공백·콤마·통화기호

-- ✗ 콤마·통화기호 포함
SELECT TO_NUMBER('1,234.56') FROM dual;
SELECT TO_NUMBER('$100')     FROM dual;
-- ORA-01722

-- ✓ 포맷 마스크 명시
SELECT TO_NUMBER('1,234.56', '9,999.99')  FROM dual;
SELECT TO_NUMBER('$100', '$999')         FROM dual;

-- ✓ 공백 제거 후 변환
SELECT TO_NUMBER(TRIM(' 100 ')) FROM dual;

원인 4 — NLS 차이

-- 현재 세션 NLS 확인
SELECT value FROM nls_session_parameters
 WHERE parameter = 'NLS_NUMERIC_CHARACTERS';

-- ✗ 유럽식 환경에서 미국식 데이터
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.';
SELECT TO_NUMBER('1.5') FROM dual;
-- ORA-01722 (콤마가 소수점이라 1.5가 비유효)

-- ✓ nlsparam으로 명시
SELECT TO_NUMBER('1.5', '9D9',
  'NLS_NUMERIC_CHARACTERS=''.,''') FROM dual;

원인 5 — JOIN 타입 불일치

-- a.id: NUMBER, b.code: VARCHAR2 (혼합 데이터)

-- ✗ 타입 불일치 → 암시적 변환에서 실패
SELECT * FROM a, b WHERE a.id = b.code;

-- ✓ 명시적 변환 + 사전 필터
SELECT * FROM a, b
 WHERE TO_CHAR(a.id) = b.code
   AND REGEXP_LIKE(b.code, '^[0-9]+$');

원인 6 — 옵티마이저 술어 재배열

-- val: VARCHAR2. type='N'인 행만 숫자 보유 가정

-- ✗ 옵티마이저가 TO_NUMBER를 먼저 평가 → 다른 행에서 ORA-01722
SELECT * FROM t
 WHERE type = 'N' AND TO_NUMBER(val) > 0;

-- ✓ CASE WHEN으로 방어적 변환
SELECT * FROM t
 WHERE type = 'N'
   AND CASE WHEN REGEXP_LIKE(val, '^-?\d+(\.\d+)?$')
              THEN TO_NUMBER(val) END > 0;

---

04 7단계 디버깅 순서

단계별 디버깅 순서

 

0112.2 이상이면 에러 메시지의 invalid_char와 column_or_expression을 확인한다.

02대상 컬럼·표현식의 데이터 타입을 DESC table로 점검한다.

03REGEXP_LIKE로 비숫자 행을 식별한다.

04실행계획(EXPLAIN PLAN)으로 옵티마이저가 술어 순서를 어떻게 평가하는지 확인한다.

05세션 NLS 설정(NLS_NUMERIC_CHARACTERS)을 점검한다.

06View·JOIN·서브쿼리 정의에 숨은 암시적 변환이 있는지 추적한다.

07DUMP·LENGTH·LENGTHB로 비가시 문자(BOM·NBSP·탭) 여부를 검출한다.

-- 비숫자 행 찾기
SELECT col FROM t
 WHERE REGEXP_LIKE(col, '[^0-9.+\-]');

-- 비가시 문자 검출
SELECT col, DUMP(col, 1016), LENGTH(col), LENGTHB(col)
  FROM t WHERE ROWNUM <= 10;

---

05 안전한 변환 패턴 4가지

ORA-01722를 회피하는 표준 패턴 4가지를 정리한다.

패턴 1 — REGEXP_LIKE 사전 검증

-- 변환 전에 숫자 형식 확인
SELECT TO_NUMBER(col) FROM t
 WHERE REGEXP_LIKE(col, '^-?\d+(\.\d+)?$');

패턴 2 — CASE WHEN 방어적 변환

-- 비숫자 행은 NULL 반환
SELECT CASE WHEN REGEXP_LIKE(col, '^-?\d+(\.\d+)?$')
            THEN TO_NUMBER(col)
       END AS num_val
  FROM t;

패턴 3 — VALIDATE_CONVERSION (12.2 이상)

-- 변환 가능 여부 사전 검사 (12.2 이상)
SELECT * FROM t
 WHERE VALIDATE_CONVERSION(col AS NUMBER) = 1;
-- 반환: 변환 가능 = 1, 불가 = 0, NULL = 1

패턴 4 — TO_NUMBER 기본값 파라미터 (12.2 이상)

-- 변환 실패 시 NULL 또는 기본값 반환 (12.2 이상)
SELECT TO_NUMBER(col DEFAULT NULL ON CONVERSION ERROR)
  FROM t;

SELECT TO_NUMBER(col DEFAULT 0 ON CONVERSION ERROR)
  FROM t;

---

06 NLS 영향과 ORA-06502 차이

NLS_NUMERIC_CHARACTERS의 역할

이 파라미터는 세션의 소수점·천단위 구분자를 정의한다. 기본값은 운영체제·클라이언트 NLS_LANG에 따라 다르다. 미국식은 '.,'(소수점·천단위), 유럽 일부 국가는 ',.'(소수점=콤마)다.

 

문제는 같은 데이터가 환경마다 다르게 해석된다는 점이다. 미국식 클라이언트에서 잘 작동하던 SQL이 유럽 NLS 클라이언트에서 ORA-01722를 던지는 케이스가 흔하다. 이를 회피하려면 TO_NUMBER 호출 시 nlsparam을 명시한다.

 

ORA-01722 vs ORA-06502 차이

구분	ORA-01722	ORA-06502
발생 레벨	SQL 엔진	PL/SQL 엔진
메시지	invalid number	numeric or value error
발생 시점	문자→숫자 변환 실패 (SQL 문맥)	PL/SQL 변수 대입·산술·NOT NULL 위반·버퍼 초과
대표 사례	WHERE varchar_col=1	v_num := 'ABC'; · NUMBER(3)에 1000 대입

같은 변환 실패라도 SQL 안에서는 ORA-01722, PL/SQL 변수 대입에서는 ORA-06502가 발생한다. 메시지가 다르므로 어느 레벨에서 실패했는지를 먼저 파악하는 것이 디버깅의 출발점이다.

---

07 자주 묻는 질문 5가지

Q1어제까지 잘 되던 SQL이 갑자기 ORA-01722가 발생한다

두 가지 원인이 가장 흔하다. 첫째, 새 데이터가 입력되면서 컬럼에 비숫자 값이 추가된 경우다. REGEXP_LIKE로 비숫자 행을 확인한다. 둘째, 옵티마이저 통계 업데이트나 인덱스 변경으로 실행 계획이 바뀌어 술어 평가 순서가 재배열된 경우다. EXPLAIN PLAN으로 변경 전후 실행 계획을 비교한다.

Q2WHERE 절에 type='N' 조건으로 필터링했는데 왜 다른 행에서 변환이 시도되나

옵티마이저는 술어 평가 순서를 자동으로 재배열할 수 있다. type='N' 필터가 TO_NUMBER보다 먼저 실행될 보장은 없다. 특히 인덱스·뷰·서브쿼리가 결합되면 변환이 다른 행에서 평가되어 에러가 터진다. 안전하려면 CASE WHEN으로 변환 자체를 방어적으로 감싸거나 12c 이상이면 TO_NUMBER(col DEFAULT NULL ON CONVERSION ERROR) 구문을 사용한다.

Q3데이터를 봐도 숫자처럼 보이는데 변환이 실패한다

비가시 문자가 포함된 경우다. 탭·NBSP·BOM·후행 줄바꿈은 눈으로 구분되지 않지만 변환을 막는다. DUMP 함수로 바이트 단위를 확인하거나 LENGTH와 LENGTHB를 비교해 검출한다. 발견되면 TRIM·REGEXP_REPLACE로 제거 후 변환한다.

Q4유럽 NLS 환경 클라이언트에서만 ORA-01722가 발생한다

NLS_NUMERIC_CHARACTERS가 콤마를 소수점으로 정의했기 때문이다. 미국식 데이터 '1.5'는 유럽 NLS에서 소수점이 아닌 천단위 구분자로 해석되어 변환에 실패한다. TO_NUMBER 호출 시 nlsparam을 명시하거나 세션 NLS를 ALTER SESSION으로 통일한다.

Q5VALIDATE_CONVERSION과 CASE WHEN REGEXP_LIKE 중 무엇이 더 좋은가

12.2 이상이면 VALIDATE_CONVERSION이 더 빠르고 간결하다. Oracle 내부 변환 로직과 동일한 규칙을 따르므로 정확성도 보장된다. 11g 이하 환경이거나 특정 정규식 규칙(예: 천단위 콤마 허용)이 필요하면 REGEXP_LIKE가 유연하다. 둘을 혼용해도 무방하다.

---

08 결론

ORA-01722는 단순한 변환 실패처럼 보이지만 암시적 변환·NLS·옵티마이저 술어 재배열·JOIN 타입 불일치까지 다양한 원인이 같은 에러로 표시되어 진단이 까다롭다. 핵심은 변환이 어디서 일어나는지를 식별하고 안전한 변환 패턴으로 방어하는 것이다.

실무에서 이 에러를 줄이려면 다음 세 가지 습관이 도움이 된다.

 

첫째, 가능하면 컬럼 데이터 타입을 처음부터 일치시킨다. NUMBER로 저장할 값은 NUMBER 컬럼에, 문자 데이터는 VARCHAR2 컬럼에 분리해 저장한다.

둘째, 변환이 필요한 위치에서는 항상 명시적 TO_NUMBER + 포맷 마스크를 사용한다. 12.2 이상이면 DEFAULT ... ON CONVERSION ERROR 구문을 적극 활용한다.

셋째, NLS 환경이 다른 시스템 간 데이터 교환 시에는 nlsparam을 항상 명시해 환경 의존성을 제거한다.

“

ORA-01722는 변환이 어디서 일어나는지를 식별하는 것이 핵심이다. 옵티마이저가 술어 순서를 재배열할 수 있으므로 방어적 변환을 기본으로 한다.

 

— VALIDATE_CONVERSION · DEFAULT ON CONVERSION ERROR · 명시적 포맷 마스크

트러블슈팅 체크리스트

 

01에러 메시지의 invalid_char와 column_or_expression을 확인한다 (12.2 이상).

02대상 컬럼의 데이터 타입과 실제 값을 점검한다.

03REGEXP_LIKE로 비숫자 행을 식별한다.

04EXPLAIN PLAN으로 옵티마이저 술어 평가 순서를 확인한다.

05NLS_NUMERIC_CHARACTERS와 클라이언트 환경 차이를 점검한다.

06DUMP·LENGTH·LENGTHB로 비가시 문자를 검출한다.

07VALIDATE_CONVERSION 또는 DEFAULT ON CONVERSION ERROR로 방어적 변환을 적용한다.

---

본 글은 Oracle Database SQL 변환 에러 ORA-01722의 일반적 원인과 해결 방법을 정리한 자료다. 옵티마이저 동작과 NLS 환경은 버전·설정마다 차이가 있으므로 운영 환경에 적용하기 전 테스트 환경에서 충분히 검증한다. Oracle 공식 문서와 최신 패치 노트를 함께 참고하면 더 안정적인 트러블슈팅이 가능하다.

 

#ORA01722 #invalidnumber #오라클에러 #Oracle #OracleDatabase #TONUMBER #VALIDATECONVERSION #암시적변환 #NLS #옵티마이저 #REGEXPLIKE #ORA06502 #SQL변환 #PLSQL #DB트러블슈팅