Oracle ORA-01861 오류 원인과 해결 방법 완벽 가이드

ORA-01861 literal does not match format string 는?

ORA-01861 에러는 Oracle 데이터베이스에서 날짜(DATE) 또는 타임스탬프(TIMESTAMP) 형식의 문자열을 변환할 때, 입력된 리터럴 값이 지정된 포맷 마스크(format mask)와 일치하지 않을 경우 발생합니다. 예를 들어 TO_DATE('2024/01/15', 'YYYY-MM-DD')처럼 실제 값의 구분자는 슬래시(/)인데 포맷 마스크는 하이픈(-)을 사용하는 경우 이 에러가 발생합니다. 이 에러는 개발 초기 단계뿐만 아니라 운영 중인 시스템에서도 NLS 설정 변경이나 외부 데이터 연동 시 빈번하게 발생하므로 반드시 정확한 원인 파악과 대응이 필요합니다.

---

주요 발생 원인

1. TO_DATE / TO_TIMESTAMP 함수의 포맷 마스크 불일치

가장 흔한 원인으로, 날짜 문자열의 실제 형식과 TO_DATE 또는 TO_TIMESTAMP 함수에 지정한 포맷 마스크가 다를 때 발생합니다. 예를 들어 입력값은 '15-JAN-2024' 형식인데 포맷 마스크를 'YYYY-MM-DD'로 지정하면 Oracle은 값을 파싱하지 못하고 ORA-01861을 반환합니다. 이는 개발자가 날짜 포맷을 임의로 가정하거나, 타 시스템에서 전달된 날짜 문자열의 형식을 정확히 확인하지 않았을 때 자주 발생합니다.

2. NLS_DATE_FORMAT 세션/시스템 설정과 리터럴 불일치

Oracle은 명시적인 변환 함수 없이 문자열을 날짜 컬럼에 직접 삽입하거나 비교할 때 NLS_DATE_FORMAT 파라미터를 기준으로 암묵적 변환(implicit conversion)을 수행합니다. 세션 또는 데이터베이스 레벨의 NLS_DATE_FORMAT이 'DD-MON-RR'로 설정되어 있는데 '2024-01-15' 형식의 문자열을 그대로 날짜 컬럼과 비교하면 ORA-01861이 발생합니다. 특히 개발 환경과 운영 환경의 NLS 설정이 다를 경우 개발 중에는 문제없던 쿼리가 운영 배포 후 에러를 일으키는 원인이 되기도 합니다.

3. 외부 데이터 소스 또는 사용자 입력값의 포맷 불일치

배치 프로그램이나 ETL 작업, 또는 웹 애플리케이션을 통해 입력되는 날짜 데이터가 예상된 포맷과 다른 형식으로 넘어오는 경우 발생합니다. 예를 들어 미국식 날짜 포맷인 '01/15/2024'(MM/DD/YYYY)를 처리하는 로직에서 한국식 '2024-01-15'(YYYY-MM-DD) 포맷의 데이터가 들어오면 에러가 발생합니다. 이 경우 입력 데이터에 대한 유효성 검사 없이 바로 SQL을 실행하는 코드 구조가 근본 원인이 됩니다.

---

해결 방법

원인 1 해결: TO_DATE 포맷 마스크를 실제 값과 정확히 일치시키기

입력 날짜 문자열의 형식을 정확히 파악한 뒤, 포맷 마스크를 그에 맞게 수정합니다.

-- 잘못된 예시 (에러 발생)
SELECT TO_DATE('2024/01/15', 'YYYY-MM-DD') FROM DUAL;
-- ORA-01861: literal does not match format string

-- 올바른 예시 1: 슬래시 구분자 사용
SELECT TO_DATE('2024/01/15', 'YYYY/MM/DD') FROM DUAL;

-- 올바른 예시 2: 하이픈 구분자 사용
SELECT TO_DATE('2024-01-15', 'YYYY-MM-DD') FROM DUAL;

-- 올바른 예시 3: TIMESTAMP 처리
SELECT TO_TIMESTAMP('2024-01-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- 올바른 예시 4: 월 영문 약자 사용 시
SELECT TO_DATE('15-JAN-2024', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=AMERICAN') FROM DUAL;

원인 2 해결: 암묵적 변환 대신 명시적 변환 함수 사용

날짜 컬럼과 문자열을 비교하거나 삽입할 때는 반드시 TO_DATE 함수를 명시적으로 사용하여 NLS 설정에 의존하지 않도록 합니다.

-- 현재 NLS_DATE_FORMAT 확인
SELECT VALUE FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT';

-- 잘못된 예시 (암묵적 변환에 의존 - NLS 설정에 따라 에러 발생 가능)
SELECT * FROM ORDERS WHERE ORDER_DATE = '2024-01-15';

-- 올바른 예시 (명시적 변환 사용 - NLS 설정과 무관하게 안전)
SELECT * FROM ORDERS WHERE ORDER_DATE = TO_DATE('2024-01-15', 'YYYY-MM-DD');

-- 세션 NLS 설정을 임시로 변경하는 방법 (비권장, 임시 방편용)
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
SELECT * FROM ORDERS WHERE ORDER_DATE = '2024-01-15';

-- INSERT 시 명시적 변환 예시
INSERT INTO ORDERS (ORDER_ID, ORDER_DATE, AMOUNT)
VALUES (1001, TO_DATE('2024-01-15', 'YYYY-MM-DD'), 50000);

COMMIT;

원인 3 해결: 입력 데이터 유효성 검사 및 포맷 정규화

외부에서 들어오는 날짜 문자열에 대해 사전 유효성 검사를 수행하고, 표준 포맷으로 정규화한 후 SQL을 실행합니다.

-- CASE 문을 활용한 다양한 포맷 처리 (Oracle 12c 이상)
SELECT
    CASE
        WHEN REGEXP_LIKE(date_str, '^\d{4}-\d{2}-\d{2}$') THEN
            TO_DATE(date_str, 'YYYY-MM-DD')
        WHEN REGEXP_LIKE(date_str, '^\d{2}/\d{2}/\d{4}$') THEN
            TO_DATE(date_str, 'MM/DD/YYYY')
        WHEN REGEXP_LIKE(date_str, '^\d{2}-\d{3}-\d{4}$') THEN
            TO_DATE(date_str, 'DD-MON-YYYY')
        ELSE NULL
    END AS parsed_date
FROM (
    SELECT '2024-01-15' AS date_str FROM DUAL
    UNION ALL
    SELECT '01/15/2024' AS date_str FROM DUAL
);

-- Oracle 12c 이상: VALIDATE_CONVERSION 함수로 변환 가능 여부 사전 확인
SELECT
    date_str,
    VALIDATE_CONVERSION(date_str AS DATE, 'YYYY-MM-DD') AS is_valid
FROM input_data_table;

-- 변환 가능한 행만 처리하는 안전한 INSERT
INSERT INTO ORDERS (ORDER_ID, ORDER_DATE)
SELECT seq_id, TO_DATE(date_str, 'YYYY-MM-DD')
FROM input_data_table
WHERE VALIDATE_CONVERSION(date_str AS DATE, 'YYYY-MM-DD') = 1;

COMMIT;

---

예방 방법

1. 날짜 처리 시 항상 명시적 변환 함수와 포맷 마스크를 사용하는 코딩 표준 수립

팀 내 SQL 코딩 표준을 정립하여, 날짜 값을 다룰 때는 반드시 TO_DATE(값, '포맷마스크') 또는 TO_TIMESTAMP(값, '포맷마스크') 형태의 명시적 변환을 사용하도록 강제합니다. 암묵적 날짜 변환은 NLS 설정에 종속되어 환경에 따라 동작이 달라지므로, 코드 리뷰 단계에서 암묵적 변환 사용을 차단하는 것이 중요합니다. 또한 ANSI 표준 날짜 리터럴 표기법인 DATE '2024-01-15' 구문을 활용하면 포맷 마스크 없이도 안전하게 날짜를 지정할 수 있습니다.

-- ANSI 표준 날짜 리터럴 사용 (NLS 설정과 무관하게 항상 안전)
SELECT * FROM ORDERS WHERE ORDER_DATE >= DATE '2024-01-01'
                       AND ORDER_DATE <  DATE '2024-02-01';

2. 개발/운영 환경의 NLS 설정 통일 및 VALIDATE_CONVERSION 함수 활용

개발, 테스트, 운영 환경의 NLS_DATE_FORMAT 설정을 동일하게 유지하여 환경 차이로 인한 에러를 방지합니다. 외부 데이터를 처리하는 배치나 ETL 로직에서는 Oracle 12c 이상에서 제공하는 VALIDATE_CONVERSION 함수를 활용하여 데이터 적재 전 변환 가능 여부를 반드시 사전 검증하도록 프로세스를 구성하는 것이 좋습니다.

---

관련 에러

• ORA-01830: 날짜 포맷 문자열이 변환 전에 끝났을 때 발생하며, ORA-01861과 유사하게 포맷 마스크 길이 불일치 시 나타납니다.
• ORA-01843: 유효하지 않은 월(month) 값이 입력되었을 때 발생하며, 날짜 문자열의 월 부분이 잘못된 경우 ORA-01861 대신 이 에러가 나타나기도 합니다.
• ORA-01858: 숫자가 예상되는 위치에 숫자가 아닌 문자가 입력되었을 때 발생하며, 날짜 포맷 처리 중 자주 함께 발생합니다.
• ORA-01847: 월의 일(day of month) 값이 1~31 범위를 벗어났을 때 발생하는 관련 날짜 에러입니다.

---

본 포스트는 AI가 생성한 기술 가이드입니다. 운영 환경 적용 전 충분한 검토를 권장합니다.