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

ORA-01843
2026년 08월 04일 | DBMS Error 가이드

이 글에서 다루는 내용

ORA-01843 에러의 원인 분석, 해결 SQL, 예방 방법을 실무 관점에서 정리합니다.

ORA-01843 not a valid month 는?

ORA-01843 에러는 Oracle 데이터베이스에서 날짜(DATE) 또는 타임스탬프(TIMESTAMP) 형식의 데이터를 변환하거나 삽입할 때, 월(Month) 값이 유효하지 않은 경우 발생하는 에러입니다. 예를 들어 ’13월’, ’00월’, 또는 알아볼 수 없는 문자열이 월 자리에 들어올 때 Oracle 파서가 이 에러를 반환합니다. 특히 애플리케이션과 데이터베이스 사이의 날짜 포맷(NLS_DATE_FORMAT) 설정이 불일치하거나, 외부 시스템에서 받은 데이터를 그대로 INSERT/UPDATE할 때 자주 발생하며, 운영 환경에서 예상치 못한 장애로 이어질 수 있어 반드시 원인을 정확히 파악하고 대처해야 합니다.

주요 발생 원인

1. NLS_DATE_FORMAT 불일치로 인한 암묵적 변환 실패

가장 흔한 원인으로, 세션 또는 시스템의 NLS_DATE_FORMAT 설정과 실제 입력되는 날짜 문자열의 포맷이 맞지 않을 때 발생합니다. 예를 들어 NLS_DATE_FORMAT이 ‘DD-MON-RR’로 설정된 환경에서 ‘2024-13-01’과 같은 형식의 문자열을 날짜로 암묵적 변환하려 하면, Oracle은 ’13’을 월로 해석하지 못하거나 포맷 자체를 잘못 파싱하여 ORA-01843을 반환합니다. 개발 환경과 운영 환경의 NLS 설정이 다른 경우 개발 단계에서는 정상 동작하다가 운영 배포 후 갑자기 에러가 발생하는 케이스가 매우 많습니다.

2. 유효하지 않은 월 값(0, 13 이상, 잘못된 문자열) 입력

월 값이 1~12 범위를 벗어나거나, 영문 약자(JAN, FEB 등)가 아닌 엉뚱한 문자열이 월 위치에 입력된 경우에도 에러가 발생합니다. 외부 API나 레거시 시스템에서 전달받은 데이터에 ’00’, ’13’, ’99’ 같은 잘못된 월 값이 포함되어 있거나, 언어 설정에 따라 ‘JAN’ 대신 ‘JAN.’ 같이 점(.)이 포함된 문자열이 들어오는 경우가 실무에서 빈번히 발생합니다. 배치 프로그램이나 데이터 마이그레이션 작업 중에 특히 주의가 필요합니다.

3. TO_DATE / TO_TIMESTAMP 함수에서 포맷 마스크 누락 또는 오기재

TO_DATE() 함수를 사용할 때 포맷 마스크를 생략하거나 잘못 지정하면 Oracle이 세션의 NLS_DATE_FORMAT에 의존하여 날짜를 파싱하게 되며, 이때 포맷이 맞지 않으면 ORA-01843이 발생합니다. 예를 들어 TO_DATE(‘2024/07/15’)처럼 포맷 마스크 없이 호출하면, NLS_DATE_FORMAT이 ‘DD-MON-RR’인 환경에서는 ‘2024’를 일(Day)로, ’07’을 월로 해석하려다 실패합니다. 코드에서 TO_DATE를 사용할 때는 반드시 명시적인 포맷 마스크를 지정하는 것이 기본 원칙입니다.

해결 방법

원인 1 해결: 세션 NLS_DATE_FORMAT 명시적 설정

현재 세션의 NLS 설정을 확인하고, 필요한 경우 세션 레벨에서 포맷을 변경합니다.

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

-- 세션 레벨에서 날짜 포맷 변경
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';

-- 변경 후 정상 동작 확인
SELECT TO_DATE('2024-07-15', 'YYYY-MM-DD') AS TEST_DATE FROM DUAL;

-- 시스템 전체 NLS 파라미터 확인
SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER LIKE 'NLS_DATE%';

> 주의: ALTER SESSION은 해당 세션에만 적용됩니다. 영구 변경이 필요하다면 DBA에게 요청하여 init.ora 또는 spfile에서 NLS_DATE_FORMAT을 수정해야 합니다.

원인 2 해결: 입력 데이터 유효성 검사 및 클렌징

배치나 마이그레이션 시 데이터를 INSERT하기 전 유효하지 않은 날짜 데이터를 사전에 걸러냅니다.

-- 잘못된 날짜 데이터 사전 탐지 (스테이징 테이블 기준)
SELECT raw_date_col
FROM staging_table
WHERE NOT REGEXP_LIKE(raw_date_col,
      '^\d{4}-(0[1-9]|1[0-2])-(0[1-9]|[12]\d|3[01])$');

-- 월 값이 범위를 벗어나는 경우 NULL 처리 후 삽입
INSERT INTO target_table (id, reg_date)
SELECT id,
       CASE
           WHEN REGEXP_LIKE(raw_date_col,
                '^\d{4}-(0[1-9]|1[0-2])-(0[1-9]|[12]\d|3[01])$')
           THEN TO_DATE(raw_date_col, 'YYYY-MM-DD')
           ELSE NULL
       END AS reg_date
FROM staging_table;

-- 이미 문자열로 저장된 날짜 컬럼에서 월 값만 추출하여 검증
SELECT raw_date_col,
       SUBSTR(raw_date_col, 6, 2) AS month_part
FROM staging_table
WHERE TO_NUMBER(SUBSTR(raw_date_col, 6, 2)) NOT BETWEEN 1 AND 12;

원인 3 해결: TO_DATE / TO_TIMESTAMP에 명시적 포맷 마스크 사용

모든 날짜 변환 함수에는 반드시 명시적 포맷 마스크를 지정합니다.

-- 잘못된 방법 (포맷 마스크 없음 - NLS 의존)
-- 아래는 NLS 설정에 따라 ORA-01843 발생 가능
SELECT TO_DATE('2024-07-15') FROM DUAL;  -- 비권장

-- 올바른 방법 (명시적 포맷 마스크 사용)
SELECT TO_DATE('2024-07-15', 'YYYY-MM-DD') AS correct_date FROM DUAL;

-- 다양한 포맷 예시
SELECT TO_DATE('15/07/2024', 'DD/MM/YYYY')   AS fmt1 FROM DUAL;
SELECT TO_DATE('July 15, 2024', 'Month DD, YYYY') AS fmt2 FROM DUAL;
SELECT TO_DATE('20240715', 'YYYYMMDD')        AS fmt3 FROM DUAL;

-- TIMESTAMP 변환 시에도 동일 원칙 적용
SELECT TO_TIMESTAMP('2024-07-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS')
AS correct_ts FROM DUAL;

-- 에러가 발생하는 날짜를 안전하게 처리하는 함수 (Oracle 12c 이상)
-- DEFAULT 절을 활용한 안전한 변환
SELECT TO_DATE('2024-13-01' DEFAULT NULL ON CONVERSION ERROR,
               'YYYY-MM-DD') AS safe_date
FROM DUAL;

-- 또는 사용자 정의 함수로 안전하게 처리
CREATE OR REPLACE FUNCTION safe_to_date(
    p_date_str  IN VARCHAR2,
    p_fmt       IN VARCHAR2 DEFAULT 'YYYY-MM-DD'
) RETURN DATE IS
    v_date DATE;
BEGIN
    v_date := TO_DATE(p_date_str, p_fmt);
    RETURN v_date;
EXCEPTION
    WHEN OTHERS THEN
        RETURN NULL;
END safe_to_date;
/

-- 함수 사용 예시
SELECT safe_to_date('2024-13-01', 'YYYY-MM-DD') AS result FROM DUAL;
-- 결과: NULL (에러 없이 처리)
SELECT safe_to_date('2024-07-15', 'YYYY-MM-DD') AS result FROM DUAL;
-- 결과: 2024-07-15

예방 방법

1. 애플리케이션 레벨에서 항상 명시적 포맷 마스크 강제화

코드 리뷰 규칙 또는 개발 가이드라인에 “TO_DATE, TO_TIMESTAMP 사용 시 반드시 포맷 마스크를 명시한다”는 원칙을 포함시키고, 정적 분석 도구(SonarQube 등)를 통해 포맷 마스크 없는 TO_DATE 호출을 자동으로 탐지하도록 구성합니다. JDBC나 ORM(MyBatis, Hibernate)을 사용하는 환경에서는 PreparedStatement의 날짜 바인딩을 활용하여 SQL 문자열 내 날짜 리터럴 자체를 최소화하는 것이 가장 안전한 방법입니다.

-- 개발/운영 환경 일관성을 위한 로그온 트리거 설정 (DBA 권한 필요)
CREATE OR REPLACE TRIGGER set_nls_on_logon
AFTER LOGON ON DATABASE
BEGIN
    EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD''';
    EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_TIMESTAMP_FORMAT=''YYYY-MM-DD HH24:MI:SS''';
END;
/

2. 데이터 입력 전 CHECK 제약 조건 및 트리거를 통한 유효성 검사

테이블 설계 단계에서부터 날짜 컬럼의 유효 범위를 CHECK 제약 조건으로 정의하고, 문자열 형태로 날짜를 저장해야 하는 레거시 구조라면 트리거를 활용해 삽입/수정 전에 날짜 유효성을 검증합니다. 가능하다면 날짜 데이터는 반드시 DATE 또는 TIMESTAMP 타입으로 저장하고, VARCHAR2로 날짜를 관리하는 설계는 지양해야 합니다.

-- 날짜 컬럼에 CHECK 제약 조건 추가 예시
ALTER TABLE orders
ADD CONSTRAINT chk_order_date
CHECK (order_date >= DATE '2000-01-01' AND order_date <= DATE '2099-12-31');

-- VARCHAR2로 날짜를 저장하는 테이블에 유효성 트리거 추가
CREATE OR REPLACE TRIGGER trg_validate_date
BEFORE INSERT OR UPDATE ON legacy_table
FOR EACH ROW
DECLARE
    v_dummy DATE;
BEGIN
    IF :NEW.date_str IS NOT NULL THEN
        v_dummy := TO_DATE(:NEW.date_str, 'YYYY-MM-DD');
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20001,
            'Invalid date format: ' || :NEW.date_str ||
            '. Expected format: YYYY-MM-DD');
END;
/