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

ORA-01851
2026년 08월 05일 | DBMS Error 가이드

이 글에서 다루는 내용

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

ORA-01851 minutes must be between 0 and 59 는?

ORA-01851 에러는 Oracle 데이터베이스에서 시간 관련 함수나 INTERVAL 타입을 사용할 때 분(minutes) 값으로 0~59 범위를 벗어난 값이 입력되었을 때 발생하는 에러입니다. 주로 TO_DSINTERVAL, NUMTODSINTERVAL, TO_TIMESTAMP, INTERVAL 리터럴, 또는 날짜/시간 연산 과정에서 잘못된 분 값이 지정될 때 나타납니다. 이 에러는 애플리케이션에서 동적으로 생성된 시간 문자열이나 사용자 입력값이 유효성 검사 없이 Oracle 함수에 전달될 때 특히 자주 발생합니다.

주요 발생 원인

1. INTERVAL 리터럴 또는 TO_DSINTERVAL 함수에 잘못된 분 값 입력

INTERVAL 타입을 직접 리터럴로 사용하거나 TO_DSINTERVAL 함수를 호출할 때, 분 값으로 60 이상이거나 음수 값을 입력하면 에러가 발생합니다. 예를 들어 외부 시스템에서 시간 데이터를 받아 그대로 변환할 때, 분 정규화(normalization)가 되지 않은 상태로 함수에 넘기면 이 에러가 발생하기 쉽습니다. 배치 프로그램이나 ETL 작업에서 시간 포맷 변환 로직이 미흡할 경우 실무에서 빈번하게 마주치는 원인입니다.

2. TO_TIMESTAMP 또는 TO_DATE 함수에서 잘못된 시간 문자열 파싱

TO_TIMESTAMP('2024-01-15 10:75:00', 'YYYY-MM-DD HH24:MI:SS')와 같이 분 값이 59를 초과하는 문자열을 날짜/시간 변환 함수에 전달하면 ORA-01851이 발생합니다. 사용자로부터 직접 입력받거나 외부 파일(CSV, XML 등)에서 읽어온 데이터를 충분한 검증 없이 사용하는 경우, 이처럼 유효하지 않은 시간 값이 유입될 수 있습니다. 특히 레거시 시스템 연동 시 타임스탬프 형식이 맞지 않아 발생하는 경우가 많습니다.

3. 동적 INTERVAL 문자열 생성 로직의 오류

애플리케이션 레이어(Java, Python, PL/SQL 등)에서 시간 차이를 계산하여 동적으로 INTERVAL 문자열을 조합할 때, 분 단위 계산에서 올림/내림 처리를 잘못하면 60 이상의 분 값이 만들어질 수 있습니다. 예를 들어 총 분 수를 그대로 문자열에 넣어 '0 00:90:00' 형태로 생성하면 Oracle은 이를 거부합니다. 특히 시간 연산 결과를 문자열로 포맷팅하는 과정에서 시(hour)와 분(minute)을 분리하는 변환 로직이 누락되는 경우 발생합니다.

해결 방법

원인 1 해결: INTERVAL 값 정규화 후 사용

분 값이 60 이상일 경우 시간 단위로 올려서 정규화해야 합니다.

-- 잘못된 사용 예 (ORA-01851 발생)
SELECT INTERVAL '0 00:90:00' DAY TO SECOND FROM DUAL;

-- 올바른 사용 예: NUMTODSINTERVAL로 초 단위 변환 후 정규화
SELECT NUMTODSINTERVAL(90 * 60, 'SECOND') FROM DUAL;
-- 결과: +00 01:30:00.000000

-- 또는 총 분 수를 시/분으로 직접 분리
DECLARE
  v_total_minutes NUMBER := 90;
  v_hours         NUMBER;
  v_minutes       NUMBER;
  v_interval      INTERVAL DAY TO SECOND;
BEGIN
  v_hours   := TRUNC(v_total_minutes / 60);
  v_minutes := MOD(v_total_minutes, 60);
  v_interval := TO_DSINTERVAL('0 ' || TO_CHAR(v_hours, 'FM00') || ':' 
                              || TO_CHAR(v_minutes, 'FM00') || ':00');
  DBMS_OUTPUT.PUT_LINE('Interval: ' || v_interval);
END;
/

원인 2 해결: 입력 데이터 유효성 검사 후 변환

TO_TIMESTAMP 또는 TO_DATE 호출 전에 분 값의 범위를 사전에 검증합니다.

-- 잘못된 사용 예 (ORA-01851 발생)
SELECT TO_TIMESTAMP('2024-01-15 10:75:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- 안전한 변환: 분 범위 확인 후 처리
DECLARE
  v_time_str VARCHAR2(20) := '2024-01-15 10:75:00';
  v_minutes  NUMBER;
  v_result   TIMESTAMP;
BEGIN
  -- 분 값 추출 후 유효성 검사
  v_minutes := TO_NUMBER(SUBSTR(v_time_str, 15, 2));
  
  IF v_minutes BETWEEN 0 AND 59 THEN
    v_result := TO_TIMESTAMP(v_time_str, 'YYYY-MM-DD HH24:MI:SS');
    DBMS_OUTPUT.PUT_LINE('변환 성공: ' || v_result);
  ELSE
    DBMS_OUTPUT.PUT_LINE('ERROR: 유효하지 않은 분 값 (' || v_minutes || '). 0~59 범위여야 합니다.');
    -- 필요 시 분 값을 정규화하여 처리
    -- v_result := TO_TIMESTAMP(...) -- 정규화 로직 적용
  END IF;
END;
/

-- REGEXP를 활용한 배치 처리 전 유효성 필터링
SELECT *
FROM   raw_time_data
WHERE  TO_NUMBER(SUBSTR(time_string, 15, 2)) NOT BETWEEN 0 AND 59;
-- 위 쿼리로 잘못된 데이터를 사전에 색출하여 수정

원인 3 해결: 동적 INTERVAL 문자열 생성 로직 수정

총 시간(초 또는 분 단위)을 NUMTODSINTERVAL을 사용해 Oracle이 자동으로 정규화하도록 위임하는 것이 가장 안전합니다.

-- 문제 있는 동적 INTERVAL 문자열 생성 예
DECLARE
  v_total_minutes NUMBER := 150; -- 2시간 30분
  v_bad_interval  VARCHAR2(30);
BEGIN
  -- 잘못된 방법: 분을 그대로 사용 (ORA-01851 발생)
  v_bad_interval := '0 00:' || v_total_minutes || ':00';
  -- '0 00:150:00' -> 에러 발생!
  -- EXECUTE IMMEDIATE 'SELECT INTERVAL ''' || v_bad_interval || ''' ...' 
  DBMS_OUTPUT.PUT_LINE('Bad interval string: ' || v_bad_interval);
END;
/

-- 올바른 방법 1: NUMTODSINTERVAL 활용 (권장)
DECLARE
  v_total_minutes NUMBER := 150;
  v_interval      INTERVAL DAY TO SECOND;
BEGIN
  -- 분을 초로 변환하여 NUMTODSINTERVAL에 전달
  v_interval := NUMTODSINTERVAL(v_total_minutes * 60, 'SECOND');
  DBMS_OUTPUT.PUT_LINE('Interval: ' || v_interval);
  -- 결과: +00 02:30:00.000000
END;
/

-- 올바른 방법 2: 시/분 직접 분리하여 문자열 조합
DECLARE
  v_total_minutes NUMBER := 150;
  v_hours         NUMBER := TRUNC(v_total_minutes / 60);   -- 2
  v_minutes       NUMBER := MOD(v_total_minutes, 60);      -- 30
  v_interval      INTERVAL DAY TO SECOND;
BEGIN
  v_interval := TO_DSINTERVAL(
    '0 ' || LPAD(v_hours, 2, '0') || ':' || LPAD(v_minutes, 2, '0') || ':00'
  );
  DBMS_OUTPUT.PUT_LINE('Interval: ' || v_interval);
  -- 결과: +00 02:30:00.000000
END;
/

-- 실무 활용: 특정 시간 이후 데이터 조회
SELECT *
FROM   orders
WHERE  order_time >= SYSTIMESTAMP - NUMTODSINTERVAL(150 * 60, 'SECOND');

예방 방법

1. 입력 데이터 유효성 검사 함수 공통화

시간 문자열을 Oracle 함수에 전달하기 전에 반드시 공통 유효성 검사 함수를 거치도록 표준화합니다. 아래와 같이 PL/SQL 패키지 수준의 유틸리티 함수를 만들어 프로젝트 전반에 적용하면 유사한 에러를 원천 차단할 수 있습니다.

CREATE OR REPLACE FUNCTION is_valid_time_string(p_time_str IN VARCHAR2, 
                                                 p_format   IN VARCHAR2)
  RETURN VARCHAR2
IS
  v_result TIMESTAMP;
BEGIN
  v_result := TO_TIMESTAMP(p_time_str, p_format);
  RETURN 'VALID';
EXCEPTION
  WHEN OTHERS THEN
    RETURN 'INVALID: ' || SQLERRM;
END;
/

-- 사용 예
SELECT is_valid_time_string('2024-01-15 10:75:00', 'YYYY-MM-DD HH24:MI:SS') AS result FROM DUAL;
-- 결과: INVALID: ORA-01851: minutes must be between 0 and 59

2. INTERVAL 생성 시 반드시 NUMTODSINTERVAL 함수 사용 표준화

개발 코딩 가이드라인에 INTERVAL 리터럴 직접 사용을 지양하고, NUMTODSINTERVAL 또는 NUMTOYMINTERVAL 함수를 통해 생성하도록 명시합니다. 이 함수들은 입력값을 자동으로 정규화해 주기 때문에 분 범위 초과 에러를 원천적으로 방지할 수 있으며, 코드의 가독성도 높아집니다.

-- 코딩 가이드: 아래와 같이 NUMTODSINTERVAL 사용을 표준으로 채택
-- 나쁜 예
SELECT SYSDATE + INTERVAL '00:90:00' HOUR TO SECOND FROM DUAL; -- 에러 위험

-- 좋은 예
SELECT SYSDATE + NUMTODSINTERVAL(90, 'MINUTE') FROM DUAL; -- 안전하고 명확
SELECT SYSDATE + NUMTODSINTERVAL(5400, 'SECOND') FROM DUAL; -- 초 단위도 안전