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

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

이 글에서 다루는 내용

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

ORA-01849 hour must be between 0 and 23 는?

ORA-01849는 날짜 또는 타임스탬프 값을 변환하거나 입력할 때 시간(hour) 값이 유효한 범위인 0~23을 벗어났을 때 발생하는 에러입니다. Oracle은 24시간제를 기준으로 시간 값을 처리하므로, 24 이상의 값이 입력되면 즉시 이 에러를 반환합니다. 주로 TO_DATE, TO_TIMESTAMP 함수를 사용하거나 날짜 리터럴을 직접 삽입할 때 빈번하게 발생합니다.

주요 발생 원인

1. TO_DATE / TO_TIMESTAMP 함수에 잘못된 시간 값 입력

가장 흔한 원인으로, 문자열을 날짜로 변환할 때 시간 부분에 24~99 사이의 값이 포함된 경우 발생합니다. 예를 들어 외부 시스템으로부터 받은 데이터에 ‘2024-01-15 25:30:00’과 같이 잘못된 시간 값이 포함되어 있을 때 이 에러가 트리거됩니다. 배치 작업이나 ETL 파이프라인에서 데이터 정제 없이 그대로 Oracle에 넣으려 할 때 특히 자주 발생합니다.

2. 잘못된 날짜 포맷 마스크 사용 (HH vs HH24)

Oracle의 날짜 포맷에서 HH는 12시간제(1~12), HH24는 24시간제(0~23)를 의미합니다. 개발자가 오후 시간(예: 15시)을 입력하면서 HH 포맷을 사용하면 13~23 범위의 값이 12시간제를 초과하여 에러가 발생합니다. 반대로 HH24 포맷을 써야 할 자리에 HH를 잘못 사용하면 이 에러로 이어집니다.

3. 애플리케이션 또는 인터페이스에서 잘못된 시간 데이터 전달

Java, Python 등 외부 애플리케이션에서 PreparedStatement나 바인드 변수를 통해 날짜 값을 넘길 때, 시간대(timezone) 변환 오류나 로직 버그로 인해 유효하지 않은 시간이 전달될 수 있습니다. 특히 자정(midnight) 처리 로직에서 24:00:00을 다음 날 00:00:00으로 변환하지 않고 그대로 넘기는 경우가 대표적입니다. 이 경우 에러 메시지만 보고는 원인을 찾기 어려우므로 애플리케이션 로그와 함께 확인이 필요합니다.

해결 방법

원인 1 해결: 잘못된 시간 값 수정

입력 데이터 자체에 문제가 있는 경우, 먼저 해당 데이터를 확인하고 수정해야 합니다.

-- 에러 발생 예시
SELECT TO_DATE('2024-01-15 25:30:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
-- ORA-01849: hour must be between 0 and 23

-- 해결: 올바른 시간 값으로 수정
SELECT TO_DATE('2024-01-15 23:30:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- 배치/ETL 환경에서 비정상 데이터 사전 필터링
SELECT raw_datetime
FROM staging_table
WHERE REGEXP_LIKE(raw_datetime, '^\d{4}-\d{2}-\d{2} (2[4-9]|[3-9]\d):\d{2}:\d{2}$');
-- 위 쿼리로 24시 이상의 시간 데이터를 사전에 식별

-- CASE 문으로 24:00:00을 다음날 00:00:00으로 자동 변환
SELECT 
  CASE 
    WHEN SUBSTR(raw_datetime, 12, 2) = '24' 
    THEN TO_DATE(SUBSTR(raw_datetime, 1, 10), 'YYYY-MM-DD') + 1
    ELSE TO_DATE(raw_datetime, 'YYYY-MM-DD HH24:MI:SS')
  END AS corrected_datetime
FROM staging_table;

원인 2 해결: 포맷 마스크 수정 (HH → HH24)

-- 에러 발생 예시: HH 포맷으로 13시 이상 입력
SELECT TO_DATE('2024-01-15 15:30:00', 'YYYY-MM-DD HH:MI:SS') FROM DUAL;
-- ORA-01849 발생

-- 해결 방법 1: HH24 포맷 사용 (24시간제)
SELECT TO_DATE('2024-01-15 15:30:00', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- 해결 방법 2: 12시간제 유지 시 AM/PM 명시
SELECT TO_DATE('2024-01-15 03:30:00 PM', 'YYYY-MM-DD HH:MI:SS AM') FROM DUAL;

-- TO_TIMESTAMP에서도 동일하게 적용
SELECT TO_TIMESTAMP('2024-01-15 15:30:00.000000', 'YYYY-MM-DD HH24:MI:SS.FF') FROM DUAL;

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

-- 세션 레벨에서 안전한 포맷으로 변경
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

원인 3 해결: 애플리케이션 레벨 데이터 검증

-- 테이블에 CHECK 제약 조건 추가로 잘못된 데이터 원천 차단
ALTER TABLE orders 
ADD CONSTRAINT chk_order_time 
CHECK (order_time >= TO_DATE('00:00:00','HH24:MI:SS') 
       AND order_time < TO_DATE('23:59:59','HH24:MI:SS'));

-- PL/SQL에서 예외 처리로 에러 포착 및 로깅
DECLARE
  v_date DATE;
  v_raw_str VARCHAR2(30) := '2024-01-15 25:00:00';
BEGIN
  BEGIN
    v_date := TO_DATE(v_raw_str, 'YYYY-MM-DD HH24:MI:SS');
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE = -1849 THEN
        DBMS_OUTPUT.PUT_LINE('잘못된 시간 값 감지: ' || v_raw_str);
        -- 에러 로그 테이블에 기록
        INSERT INTO error_log (error_code, error_msg, raw_data, log_time)
        VALUES (SQLCODE, SQLERRM, v_raw_str, SYSDATE);
        COMMIT;
      ELSE
        RAISE;
      END IF;
  END;
END;
/

-- 대량 데이터 처리 시 유효성 검사 함수 활용
CREATE OR REPLACE FUNCTION is_valid_datetime(p_str IN VARCHAR2, p_fmt IN VARCHAR2)
RETURN NUMBER IS
  v_date DATE;
BEGIN
  v_date := TO_DATE(p_str, p_fmt);
  RETURN 1; -- 유효
EXCEPTION
  WHEN OTHERS THEN
    RETURN 0; -- 무효
END;
/

-- 사용 예시: 유효한 날짜만 처리
SELECT raw_datetime
FROM staging_table
WHERE is_valid_datetime(raw_datetime, 'YYYY-MM-DD HH24:MI:SS') = 1;

예방 방법

1. 날짜 포맷 표준화 및 코딩 컨벤션 수립

프로젝트 전체에서 날짜 관련 포맷을 YYYY-MM-DD HH24:MI:SS로 통일하고, 이를 개발 가이드라인에 명문화해야 합니다. DDL 작성 시 컬럼의 기본값 설정, 뷰 정의, 프로시저 내부의 날짜 변환에도 일관되게 HH24 포맷을 사용하도록 팀 전체가 공유해야 합니다. 코드 리뷰 단계에서 HH 포맷 단독 사용 여부를 체크리스트에 포함시키면 사전 예방 효과가 큽니다.

-- 세션/시스템 레벨에서 안전한 NLS 설정 (DBA 권한 필요)
ALTER SYSTEM SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' SCOPE=SPFILE;

-- 또는 로그인 트리거로 세션 자동 설정
CREATE OR REPLACE TRIGGER set_nls_on_logon
AFTER LOGON ON DATABASE
BEGIN
  EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS''';
END;
/

2. 입력 데이터 유효성 검사 레이어 구축

외부 데이터가 Oracle에 유입되기 전 단계에서 시간 값의 유효성을 반드시 검사하는 레이어를 구축해야 합니다. ETL 툴, API Gateway, 또는 Oracle의 BEFORE INSERT 트리거를 활용하여 0~23 범위를 벗어난 시간 값이 테이블에 저장되지 않도록 원천 차단하는 것이 가장 효과적인 예방책입니다.

-- BEFORE INSERT/UPDATE 트리거로 시간 값 사전 검증
CREATE OR REPLACE TRIGGER trg_validate_hour
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW
BEGIN
  IF TO_NUMBER(TO_CHAR(:NEW.order_time, 'HH24')) NOT BETWEEN 0 AND 23 THEN
    RAISE_APPLICATION_ERROR(-20001, 
      '유효하지 않은 시간 값입니다. 0~23 사이여야 합니다: ' 
      || TO_CHAR(:NEW.order_time, 'HH24'));
  END IF;
END;
/