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

ORA-01867 the interval is invalid 는?

ORA-01867 에러는 Oracle 데이터베이스에서 INTERVAL 데이터 타입을 사용할 때 유효하지 않은 값을 입력하거나 잘못된 형식으로 변환을 시도할 경우 발생합니다. 이 에러는 주로 INTERVAL YEAR TO MONTH 또는 INTERVAL DAY TO SECOND 타입에서 허용 범위를 벗어난 값이나 문법적으로 맞지 않는 리터럴 문자열을 사용했을 때 나타납니다. 30년 DBA 경력 중 이 에러는 날짜/시간 연산 로직을 새로 구현하거나 외부 시스템에서 데이터를 마이그레이션하는 과정에서 특히 자주 목격되었습니다.

---

주요 발생 원인

1. INTERVAL 리터럴 문자열의 형식 오류

Oracle에서 INTERVAL 값을 직접 리터럴로 표현할 때는 엄격한 형식 규칙을 따라야 합니다. 예를 들어 INTERVAL '13' MONTH는 유효하지만, INTERVAL '13' YEAR TO MONTH 형식에서 월 부분이 0~11 범위를 벗어나거나, DAY TO SECOND 형식에서 시(Hour) 값이 0~23을 초과하는 경우 즉시 ORA-01867이 발생합니다. 특히 문자열을 동적으로 조합하여 INTERVAL 변환을 수행하는 애플리케이션에서 입력 값 검증 없이 처리할 때 빈번하게 나타나는 원인입니다.

2. TO_YMINTERVAL / TO_DSINTERVAL 함수에 잘못된 문자열 전달

TO_YMINTERVAL 함수는 '년-월' 형식의 문자열만 허용하고, TO_DSINTERVAL 함수는 '일 시:분:초' 형식만 허용합니다. 이 함수들에 해당 형식에 맞지 않는 문자열(예: 공백, 구분자 누락, 음수 등)을 넘기면 ORA-01867이 발생합니다. 외부 API나 배치 처리에서 받아온 문자열 데이터를 직접 이 함수에 넣을 때 사전 정제(sanitize) 과정이 생략되면 운영 환경에서 치명적인 오류로 이어질 수 있습니다.

3. INTERVAL 타입 컬럼에 범위 초과 값 INSERT/UPDATE

INTERVAL DAY(n) TO SECOND 또는 INTERVAL YEAR(n) TO MONTH 타입을 정의할 때 지정한 정밀도(precision)를 초과하는 값을 삽입하려 할 때도 이 에러가 발생할 수 있습니다. 예를 들어 INTERVAL DAY(2) TO SECOND 컬럼에 3자리 일수(100일 이상)를 입력하려 하거나, 필드 값 자체가 해당 타입의 유효 범위를 벗어날 경우 에러가 트리거됩니다. 스키마 설계 시 정밀도를 충분히 고려하지 않은 채 운영을 시작했을 때 나중에 데이터가 쌓이면서 예기치 않게 발생하는 경우가 많습니다.

---

해결 방법

원인 1 해결: INTERVAL 리터럴 형식 교정

INTERVAL 리터럴을 작성할 때 각 필드 값이 허용 범위 안에 있는지 확인하고, 올바른 형식으로 수정합니다.

-- 잘못된 예: 월(MONTH) 부분이 12 이상인 경우 (0~11만 허용)
-- ORA-01867 발생
SELECT INTERVAL '1-12' YEAR TO MONTH FROM DUAL;

-- 올바른 예: 12개월은 1년 0개월로 표현
SELECT INTERVAL '1-0' YEAR TO MONTH FROM DUAL;

-- 잘못된 예: 시(HOUR)가 24 이상인 경우 (0~23만 허용)
-- ORA-01867 발생
SELECT INTERVAL '1 24:00:00' DAY TO SECOND FROM DUAL;

-- 올바른 예: 24시간은 1일 0시간으로 표현
SELECT INTERVAL '2 00:00:00' DAY TO SECOND FROM DUAL;

-- INTERVAL 각 필드의 유효 범위 확인용 쿼리
SELECT
    INTERVAL '9' YEAR        AS year_ok,
    INTERVAL '11' MONTH      AS month_ok,
    INTERVAL '99' DAY        AS day_ok,
    INTERVAL '23' HOUR       AS hour_ok,
    INTERVAL '59' MINUTE     AS minute_ok,
    INTERVAL '59' SECOND     AS second_ok
FROM DUAL;

원인 2 해결: TO_YMINTERVAL / TO_DSINTERVAL 입력값 사전 검증

함수에 전달하기 전에 입력 문자열의 형식을 검증하고, 예외 처리를 추가합니다.

-- 잘못된 예: 형식이 맞지 않는 문자열
-- ORA-01867 발생
SELECT TO_YMINTERVAL('2/6') FROM DUAL;   -- 구분자가 '-' 여야 함
SELECT TO_DSINTERVAL('1:12:00:00') FROM DUAL; -- 형식 불일치

-- 올바른 형식: TO_YMINTERVAL은 'YYYY-MM', TO_DSINTERVAL은 'DD HH:MI:SS'
SELECT TO_YMINTERVAL('2-6') FROM DUAL;         -- 2년 6개월
SELECT TO_DSINTERVAL('1 12:00:00') FROM DUAL;  -- 1일 12시간

-- PL/SQL에서 안전하게 처리하는 예제
DECLARE
    v_input_ym  VARCHAR2(20) := '2-6';
    v_input_ds  VARCHAR2(30) := '1 12:30:00';
    v_interval_ym INTERVAL YEAR TO MONTH;
    v_interval_ds INTERVAL DAY TO SECOND;
BEGIN
    BEGIN
        v_interval_ym := TO_YMINTERVAL(v_input_ym);
        DBMS_OUTPUT.PUT_LINE('YEAR TO MONTH: ' || v_interval_ym);
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('YMINTERVAL 변환 오류: ' || SQLERRM);
    END;

    BEGIN
        v_interval_ds := TO_DSINTERVAL(v_input_ds);
        DBMS_OUTPUT.PUT_LINE('DAY TO SECOND: ' || v_interval_ds);
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('DSINTERVAL 변환 오류: ' || SQLERRM);
    END;
END;
/

-- 입력값 정규식 검증 함수 예제
CREATE OR REPLACE FUNCTION is_valid_yminterval(p_str IN VARCHAR2)
RETURN VARCHAR2 IS
    v_interval INTERVAL YEAR TO MONTH;
BEGIN
    v_interval := TO_YMINTERVAL(p_str);
    RETURN 'VALID';
EXCEPTION
    WHEN OTHERS THEN
        RETURN 'INVALID: ' || SQLERRM;
END;
/

-- 사용 예
SELECT is_valid_yminterval('2-6')  AS test1,
       is_valid_yminterval('2/6')  AS test2,
       is_valid_yminterval('abc')  AS test3
FROM DUAL;

원인 3 해결: 컬럼 정밀도(Precision) 조정

INTERVAL 컬럼의 정밀도가 부족하다면 테이블을 수정하거나 데이터 입력 전 변환 로직을 적용합니다.

-- 현재 정밀도 확인
SELECT column_name, data_type, interval_precision, char_length
FROM user_tab_columns
WHERE table_name = 'MY_TABLE'
  AND data_type LIKE '%INTERVAL%';

-- 잘못된 예: DAY(2) 컬럼에 100일 초과 값 입력 시 에러 발생
CREATE TABLE test_interval (
    id      NUMBER,
    duration INTERVAL DAY(2) TO SECOND  -- 최대 99일까지만 허용
);

-- ORA-01867 발생 가능 (100일 이상)
INSERT INTO test_interval VALUES (1, INTERVAL '100' DAY);

-- 해결: 컬럼 정밀도를 DAY(3)으로 변경 (최대 999일)
ALTER TABLE test_interval
MODIFY (duration INTERVAL DAY(3) TO SECOND);

-- 정밀도 수정 후 정상 INSERT
INSERT INTO test_interval VALUES (1, INTERVAL '100' DAY(3));
COMMIT;

-- 데이터 정합성 확인
SELECT id,
       duration,
       EXTRACT(DAY FROM duration)    AS days,
       EXTRACT(HOUR FROM duration)   AS hours,
       EXTRACT(MINUTE FROM duration) AS minutes
FROM test_interval;

---

예방 방법

1. 입력 데이터 검증 래퍼(Wrapper) 함수 공통화

운영 시스템에서 INTERVAL 변환이 필요한 모든 지점에 직접 TO_YMINTERVAL / TO_DSINTERVAL을 호출하지 말고, 반드시 검증 로직이 포함된 래퍼 함수를 공통 라이브러리로 만들어 사용하십시오. 이 함수 내부에서 정규식이나 예외 처리를 통해 잘못된 입력이 데이터베이스 레이어까지 도달하기 전에 차단하는 구조를 만들어 두면, 외부 연동이나 배치 처리 환경에서도 안정적으로 운영할 수 있습니다.

2. INTERVAL 컬럼 설계 시 충분한 정밀도 여유 확보

초기 스키마 설계 단계에서 INTERVAL 컬럼의 정밀도를 예상 최대값보다 넉넉하게 설정하는 습관을 들이십시오. 예를 들어 최대 며칠 정도를 저장할 것 같더라도 DAY(4) 이상으로 여유를 두고, 월/연도도 마찬가지로 YEAR(4) 등으로 충분히 확보해 두어야 나중에 데이터가 증가하거나 요구사항이 변경될 때 에러 없이 대응할 수 있습니다. 운영 중인 테이블의 컬럼 정밀도를 변경하는 작업은 서비스 중단 리스크가 있으므로, 처음부터 넉넉하게 설계하는 것이 최선의 예방책입니다.

---

관련 에러

• ORA-01843: 월(Month)이 유효하지 않을 때 발생하며, DATE 타입 변환 시 자주 함께 나타납니다.
• ORA-01850: 시간 값이 0~23 범위를 벗어날 때 발생하며 ORA-01867과 증상이 유사합니다.
• ORA-01873: INTERVAL 리터럴의 선행 정밀도(leading precision)가 너무 작을 때 발생하며, ORA-01867과 함께 INTERVAL 관련 에러 트러블슈팅 시 반드시 확인해야 합니다.
• ORA-01874: 시간대(Timezone) 관련 INTERVAL 처리 중 발생할 수 있으며, INTERVAL 타입과 TIMESTAMP WITH TIME ZONE을 함께 사용하는 환경에서 주의가 필요합니다.

---

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