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

ORA-01839
2026년 08월 03일 | DBMS Error 가이드

이 글에서 다루는 내용

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

ORA-01839 date not valid for month specified 는?

ORA-01839 에러는 Oracle 데이터베이스에서 특정 월(Month)에 존재하지 않는 날짜(Day)를 입력하거나 계산하려 할 때 발생하는 에러입니다. 예를 들어, 2월에는 28일(윤년은 29일)까지밖에 없는데 2월 30일이나 31일을 입력하려 하면 이 에러가 발생합니다. 주로 날짜 문자열을 TO_DATE 함수로 변환하거나, 날짜 연산(날짜 + 숫자)을 수행할 때, 또는 외부 시스템에서 데이터를 로드할 때 자주 마주치는 에러입니다.

주요 발생 원인

1. TO_DATE 함수에서 잘못된 날짜 문자열 변환

가장 흔한 원인으로, 날짜 형식 문자열이 실제로 존재하지 않는 날짜를 포함하고 있을 때 발생합니다. 예를 들어 ‘2023-02-30’, ‘2023-04-31’처럼 해당 월에 존재하지 않는 일(Day) 값을 TO_DATE로 변환하려 하면 즉시 ORA-01839가 발생합니다. 사용자 입력값을 그대로 날짜로 변환하는 로직에서 유효성 검증 없이 처리할 경우 특히 자주 발생합니다.

2. ADD_MONTHS 또는 날짜 연산 결과가 유효하지 않은 날짜를 생성

날짜 연산 시, 특정 월 말일이 기준 날짜의 일(Day)보다 작을 때 내부적으로 유효하지 않은 날짜가 만들어지는 경우가 있습니다. 예를 들어, 1월 31일에서 1개월을 더하면 2월 31일이 되는데, 이는 존재하지 않는 날짜입니다. ADD_MONTHS 함수는 이 경우 자동으로 말일로 조정해주지만, 직접 숫자를 더하는 방식(SYSDATE + 30)에서 경계 케이스를 잘못 처리하면 에러가 발생할 수 있습니다.

3. 외부 데이터 로드 또는 ETL 과정에서 잘못된 날짜 데이터 유입

CSV, Excel, 외부 API 등에서 데이터를 가져올 때 소스 시스템에서 날짜 유효성 검증이 미흡한 경우 잘못된 날짜 값이 Oracle로 유입될 수 있습니다. 특히 월(MM)과 일(DD)의 순서를 혼동한 날짜 형식(예: MM/DD vs DD/MM)이 잘못 매핑되거나, 소스 데이터 자체에 ’00’일, ’00’월 같은 더미 데이터가 포함된 경우에도 이 에러가 발생합니다. 대량 INSERT 또는 SQL*Loader, Oracle Data Pump 사용 시 이 에러 하나 때문에 전체 배치가 실패하는 경황이 많습니다.

해결 방법

원인 1 해결: TO_DATE 변환 전 유효성 검증

TO_DATE를 사용하기 전에 입력 날짜가 유효한지 확인하는 래퍼 함수를 만들어 활용합니다.

-- 유효하지 않은 날짜 변환 시 에러 재현
SELECT TO_DATE('2023-02-30', 'YYYY-MM-DD') FROM DUAL;
-- ORA-01839: date not valid for month specified

-- 해결 방법 1: CASE 문과 예외 처리를 이용한 안전한 변환 함수
CREATE OR REPLACE FUNCTION safe_to_date(
    p_date_str  IN VARCHAR2,
    p_format    IN VARCHAR2 DEFAULT 'YYYY-MM-DD'
) RETURN DATE IS
    v_date DATE;
BEGIN
    v_date := TO_DATE(p_date_str, p_format);
    RETURN v_date;
EXCEPTION
    WHEN OTHERS THEN
        -- 유효하지 않은 날짜는 NULL 반환 또는 별도 처리
        RETURN NULL;
END safe_to_date;
/

-- 함수 사용 예시
SELECT safe_to_date('2023-02-30', 'YYYY-MM-DD') AS result FROM DUAL;
-- 결과: NULL (에러 없이 처리)

SELECT safe_to_date('2023-02-28', 'YYYY-MM-DD') AS result FROM DUAL;
-- 결과: 2023-02-28

-- 해결 방법 2: REGEXP로 기초 형식 검증 후 변환
SELECT
    CASE
        WHEN REGEXP_LIKE(date_str, '^\d{4}-\d{2}-\d{2}$')
             AND TO_NUMBER(SUBSTR(date_str, 6, 2)) BETWEEN 1 AND 12
             AND TO_NUMBER(SUBSTR(date_str, 9, 2)) BETWEEN 1 AND 31
        THEN TO_DATE(date_str, 'YYYY-MM-DD')
        ELSE NULL
    END AS valid_date
FROM (
    SELECT '2023-02-28' AS date_str FROM DUAL UNION ALL
    SELECT '2023-02-30' AS date_str FROM DUAL UNION ALL
    SELECT '2023-13-01' AS date_str FROM DUAL
);

원인 2 해결: 날짜 연산 시 ADD_MONTHS 활용

직접 숫자를 더하는 방식 대신 Oracle 내장 함수를 활용하여 안전하게 날짜 연산을 수행합니다.

-- 문제가 될 수 있는 직접 날짜 연산
-- 1월 31일 + 31일 = 3월 3일 (의도와 다를 수 있음)
SELECT TO_DATE('2023-01-31', 'YYYY-MM-DD') + 31 AS calc_date FROM DUAL;

-- ADD_MONTHS 함수로 안전하게 월 단위 연산
-- 자동으로 해당 월의 말일로 조정
SELECT ADD_MONTHS(TO_DATE('2023-01-31', 'YYYY-MM-DD'), 1) AS next_month FROM DUAL;
-- 결과: 2023-02-28 (2월 말일로 자동 조정)

-- 특정 월의 마지막 날 구하기 (LAST_DAY 함수 활용)
SELECT LAST_DAY(TO_DATE('2023-02-01', 'YYYY-MM-DD')) AS last_day FROM DUAL;
-- 결과: 2023-02-28

-- 월말 기준 안전한 날짜 계산 예시
SELECT
    TO_DATE('2023-01-31', 'YYYY-MM-DD') AS start_date,
    ADD_MONTHS(TO_DATE('2023-01-31', 'YYYY-MM-DD'), 1) AS plus_1_month,
    ADD_MONTHS(TO_DATE('2023-01-31', 'YYYY-MM-DD'), 2) AS plus_2_months,
    ADD_MONTHS(TO_DATE('2023-01-31', 'YYYY-MM-DD'), 3) AS plus_3_months
FROM DUAL;

원인 3 해결: ETL 데이터 로드 전 유효성 검증 및 클렌징

외부 데이터를 로드하기 전에 스테이징 테이블을 활용하여 날짜 유효성을 검증합니다.

-- 스테이징 테이블에 VARCHAR2로 먼저 적재
CREATE TABLE stg_orders (
    order_id    NUMBER,
    order_date  VARCHAR2(20),  -- 날짜를 문자열로 먼저 받음
    amount      NUMBER
);

-- 유효하지 않은 날짜 데이터 확인
SELECT
    order_id,
    order_date,
    CASE
        WHEN safe_to_date(order_date, 'YYYY-MM-DD') IS NULL
        THEN '유효하지 않은 날짜'
        ELSE '정상'
    END AS date_status
FROM stg_orders;

-- 유효한 데이터만 실제 테이블로 이관
INSERT INTO orders (order_id, order_date, amount)
SELECT
    order_id,
    TO_DATE(order_date, 'YYYY-MM-DD'),
    amount
FROM stg_orders
WHERE safe_to_date(order_date, 'YYYY-MM-DD') IS NOT NULL;

-- 유효하지 않은 데이터는 에러 테이블로 이관
INSERT INTO stg_orders_error (order_id, order_date, amount, error_msg, error_date)
SELECT
    order_id,
    order_date,
    amount,
    'ORA-01839: 유효하지 않은 날짜',
    SYSDATE
FROM stg_orders
WHERE safe_to_date(order_date, 'YYYY-MM-DD') IS NULL;

COMMIT;

-- 특정 달의 최대 일수를 넘는 데이터 찾기 (로드 전 검증 쿼리)
SELECT
    order_id,
    order_date,
    TO_NUMBER(SUBSTR(order_date, 9, 2)) AS day_part,
    TO_NUMBER(SUBSTR(order_date, 6, 2)) AS month_part,
    LAST_DAY(TO_DATE(SUBSTR(order_date, 1, 7) || '-01', 'YYYY-MM-DD')) AS last_valid_day
FROM stg_orders
WHERE REGEXP_LIKE(order_date, '^\d{4}-\d{2}-\d{2}$')
  AND TO_NUMBER(SUBSTR(order_date, 9, 2)) >
      TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE(SUBSTR(order_date, 1, 7) || '-01', 'YYYY-MM-DD')), 'DD'));

예방 방법

1. 날짜 입력/변환 시 항상 검증 함수(Wrapper) 사용 및 표준화

애플리케이션 레이어와 데이터베이스 레이어 양쪽에 날짜 유효성 검증 로직을 이중으로 적용하는 것이 Best Practice입니다. 위에서 작성한 safe_to_date 같은 공통 함수를 팀 내 공통 패키지(Common Package)로 만들어 모든 개발자가 동일하게 사용하도록 표준화하면, ORA-01839를 포함한 각종 날짜 관련 에러를 사전에 차단할 수 있습니다. 또한, NLS_DATE_FORMAT 세션 파라미터를 명확히 설정하여 암묵적 날짜 변환에 의존하지 않도록 하는 것도 중요합니다.

-- 세션 레벨 날짜 형식 명시적 설정
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';

-- 또는 공통 패키지로 관리
CREATE OR REPLACE PACKAGE pkg_date_utils AS
    FUNCTION safe_to_date(p_str VARCHAR2, p_fmt VARCHAR2 DEFAULT 'YYYY-MM-DD') RETURN DATE;
    FUNCTION is_valid_date(p_str VARCHAR2, p_fmt VARCHAR2 DEFAULT 'YYYY-MM-DD') RETURN BOOLEAN;
END pkg_date_utils;
/

2. 테이블 레벨 CHECK CONSTRAINT 및 트리거를 통한 데이터 무결성 강화

테이블 설계 단계에서부터 날짜 컬럼에 적절한 제약조건을 추가하여 잘못된 데이터가 데이터베이스에 저장되는 것 자체를 방지합니다. DATE 타입 컬럼을 올바르게 사용하면 Oracle이 자동으로 유효성을 검증해주지만, VARCHAR2로 날짜를 저장하는 레거시 시스템이라면 반드시 CHECK CONSTRAINT나 BEFORE INSERT/UPDATE 트리거를 통해 날짜 유효성을 강제해야 합니다.

-- VARCHAR2로 날짜를 저장하는 경우 CHECK CONSTRAINT 추가
ALTER TABLE orders ADD CONSTRAINT chk_order_date
    CHECK (safe_to_date(order_date_str, 'YYYY-MM-DD') IS NOT NULL);

-- BEFORE INSERT/UPDATE 트리거로 유효성 검증
CREATE OR REPLACE TRIGGER trg_validate_date
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW
BEGIN
    IF :NEW.order_date IS NULL THEN
        RAISE_APPLICATION_ERROR(-20001, '주문일자는 필수입니다.');
    END IF;
    -- DATE 타입이라면 Oracle이 자동 검증하므로 추가 검증 불필요
    -- VARCHAR2 날짜 문자열이라면 아래 검증 추가
    -- IF safe_to_date(:NEW.order_date_str, 'YYYY-MM-DD') IS NULL THEN
    --     RAISE_APPLICATION_ERROR(-20002, '유효하지 않은 날짜 형식입니다: ' || :NEW.order_date_str);
    -- END IF;
END;
/