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

ORA-01852 seconds must be between 0 and 59 는?

ORA-01852는 Oracle 데이터베이스에서 날짜 또는 시간 값을 처리할 때 초(seconds) 값이 유효한 범위인 0~59를 벗어났을 경우 발생하는 에러입니다. 주로 TO_DATE, TO_TIMESTAMP 함수를 사용하거나 날짜 리터럴을 입력할 때, 초 값으로 60 이상이거나 음수를 입력하면 Oracle이 해당 값을 유효하지 않은 시간 구성요소로 판단하여 이 에러를 던집니다. 실무에서는 외부 시스템에서 전달된 잘못된 데이터를 그대로 Oracle에 삽입하거나, 사용자가 직접 날짜 문자열을 입력하는 인터페이스에서 유효성 검사 없이 DB에 전달될 때 빈번하게 발생합니다.

---

주요 발생 원인

1. TO_DATE / TO_TIMESTAMP 함수에 잘못된 초 값 직접 입력

가장 흔한 원인으로, 개발자나 사용자가 날짜 변환 함수를 사용할 때 초 값으로 0~59 범위를 벗어난 숫자를 입력하는 경우입니다. 예를 들어 TO_DATE('2024-01-15 10:30:60', 'YYYY-MM-DD HH24:MI:SS')처럼 초 값이 60인 경우, Oracle은 이를 처리하지 못하고 즉시 ORA-01852를 발생시킵니다. 배치 프로그램이나 ETL 작업에서 원천 데이터의 유효성 검증 없이 그대로 변환 함수에 전달하는 경우 특히 자주 발생합니다.

2. 외부 시스템 또는 레거시 데이터의 비정상 날짜 문자열 유입

ERP, CRM 등 외부 시스템 또는 레거시 데이터베이스에서 마이그레이션한 데이터에 잘못된 시간 값이 포함된 경우입니다. 특히 타 DBMS(MySQL, MSSQL 등)에서 Oracle로 데이터를 이관할 때, 각 DBMS가 내부적으로 날짜를 처리하는 방식 차이로 인해 초 값이 60 이상으로 저장된 레코드가 Oracle에 삽입될 때 에러가 발생합니다. 이런 경우는 단순한 쿼리 수정이 아닌 원천 데이터 자체의 정제(cleansing) 작업이 필요하므로 해결에 더 많은 시간이 소요됩니다.

3. 동적 SQL 또는 사용자 입력값의 불충분한 유효성 검사

웹 애플리케이션이나 배치 프로그램에서 사용자로부터 입력받은 날짜 문자열 또는 동적으로 생성된 날짜 문자열을 Oracle에 전달할 때, 초 값에 대한 범위 검사를 생략한 경우입니다. 특히 초 값이 코드 연산의 결과로 계산되는 경우(예: 타임스탬프 차이를 잘못 계산한 경우), 예상치 못하게 60 이상의 값이 생성될 수 있습니다. 애플리케이션 레이어에서의 입력값 검증이 미흡할수록 이 에러의 발생 빈도는 높아집니다.

---

해결 방법

원인 1 해결: TO_DATE / TO_TIMESTAMP 수정

초 값을 올바른 범위(0~59)로 수정합니다. 만약 데이터 자체가 잘못 생성된 것이라면 로직을 재검토해야 합니다.

-- 에러 발생 쿼리 (초 값이 60)
SELECT TO_DATE('2024-01-15 10:30:60', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
-- ORA-01852: seconds must be between 0 and 59

-- 올바른 초 값으로 수정
SELECT TO_DATE('2024-01-15 10:30:59', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- TO_TIMESTAMP 사용 시도 동일
SELECT TO_TIMESTAMP('2024-01-15 10:30:60.000', 'YYYY-MM-DD HH24:MI:SS.FF3') FROM DUAL;
-- ORA-01852 발생

-- 올바른 사용
SELECT TO_TIMESTAMP('2024-01-15 10:30:59.000', 'YYYY-MM-DD HH24:MI:SS.FF3') FROM DUAL;

만약 초 값이 60이 되는 순간을 다음 분(minute)으로 올림 처리해야 한다면 아래와 같이 처리합니다.

-- 초 값이 60인 경우 → 1분 추가하고 초는 0으로 설정
SELECT TO_DATE('2024-01-15 10:30:00', 'YYYY-MM-DD HH24:MI:SS') 
       + INTERVAL '1' MINUTE AS ADJUSTED_DATE
FROM DUAL;

---

원인 2 해결: 외부 데이터 정제 및 CASE 처리

마이그레이션 또는 ETL 시 초 값이 범위를 벗어난 레코드를 미리 식별하고 보정합니다.

-- 비정상 초 값을 가진 문자열 데이터 식별 (초 값 추출 후 검증)
SELECT raw_date_str,
       SUBSTR(raw_date_str, 18, 2) AS seconds_part
FROM   staging_table
WHERE  TO_NUMBER(SUBSTR(raw_date_str, 18, 2)) NOT BETWEEN 0 AND 59;

-- CASE 문을 이용한 초 값 보정 후 삽입
INSERT INTO target_table (event_date)
SELECT 
    CASE 
        WHEN TO_NUMBER(SUBSTR(raw_date_str, 18, 2)) BETWEEN 0 AND 59
            THEN TO_DATE(raw_date_str, 'YYYY-MM-DD HH24:MI:SS')
        ELSE
            -- 초 값을 59로 클램핑하거나 0으로 초기화
            TO_DATE(SUBSTR(raw_date_str, 1, 16) || ':59', 'YYYY-MM-DD HH24:MI:SS')
    END AS event_date
FROM staging_table;

-- 또는 REGEXP를 활용한 유효성 체크
SELECT raw_date_str
FROM   staging_table
WHERE  NOT REGEXP_LIKE(raw_date_str, 
       '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:[0-5][0-9]$');

---

원인 3 해결: 애플리케이션 레이어 유효성 검사 + DB 레벨 방어 처리

DB 레벨에서도 동적 SQL 실행 전에 초 값을 검증하는 로직을 추가합니다.

-- PL/SQL을 이용한 안전한 날짜 변환 함수 작성
CREATE OR REPLACE FUNCTION safe_to_date(
    p_date_str  IN VARCHAR2,
    p_format    IN VARCHAR2 DEFAULT 'YYYY-MM-DD HH24:MI:SS'
) RETURN DATE IS
    v_result DATE;
    v_seconds NUMBER;
BEGIN
    -- 초 값 추출 및 범위 검증 (HH24:MI:SS 포맷 기준)
    v_seconds := TO_NUMBER(SUBSTR(p_date_str, 
                           INSTR(p_date_str, ':', 1, 2) + 1, 2));
    
    IF v_seconds NOT BETWEEN 0 AND 59 THEN
        -- 초 값 보정: 59로 클램핑
        DBMS_OUTPUT.PUT_LINE('WARNING: Invalid seconds value [' 
                             || v_seconds || ']. Clamped to 59.');
        v_result := TO_DATE(
            SUBSTR(p_date_str, 1, INSTR(p_date_str, ':', 1, 2)) || '59',
            p_format
        );
    ELSE
        v_result := TO_DATE(p_date_str, p_format);
    END IF;
    
    RETURN v_result;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error converting date: ' || SQLERRM);
        RETURN NULL;
END safe_to_date;
/

-- 사용 예시
SELECT safe_to_date('2024-01-15 10:30:60') AS safe_date FROM DUAL;
SELECT safe_to_date('2024-01-15 10:30:45') AS safe_date FROM DUAL;

---

예방 방법

1. 데이터 입력 전 CHECK CONSTRAINT 또는 BEFORE INSERT 트리거 설정

테이블 레벨에서 날짜 컬럼에 대한 트리거를 설정하여, 잘못된 초 값이 포함된 데이터가 삽입되는 것을 사전에 차단합니다. DATE 타입은 Oracle 내부에서 이미 검증되지만, VARCHAR2로 날짜를 저장하는 경우라면 CHECK CONSTRAINT나 트리거가 매우 효과적인 방어막이 됩니다.

-- VARCHAR2로 날짜를 저장하는 경우 CHECK CONSTRAINT 추가
ALTER TABLE event_log 
ADD CONSTRAINT chk_event_time_format 
CHECK (
    REGEXP_LIKE(event_time_str, 
    '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:[0-5][0-9]$')
);

-- BEFORE INSERT/UPDATE 트리거로 초 값 자동 보정
CREATE OR REPLACE TRIGGER trg_validate_event_date
BEFORE INSERT OR UPDATE ON event_log
FOR EACH ROW
DECLARE
    v_sec NUMBER;
BEGIN
    IF :NEW.event_time_str IS NOT NULL THEN
        v_sec := TO_NUMBER(SUBSTR(:NEW.event_time_str, 18, 2));
        IF v_sec NOT BETWEEN 0 AND 59 THEN
            RAISE_APPLICATION_ERROR(-20001, 
                'ORA-01852 방지: 초 값은 0~59 사이여야 합니다. 입력값: ' || v_sec);
        END IF;
    END IF;
END;
/

2. ETL 및 배치 프로그램에서 날짜 데이터 사전 검증 단계 의무화

운영 환경에 배포된 ETL 또는 배치 프로그램은 반드시 날짜 필드에 대한 사전 검증(Pre-validation) 단계를 포함해야 합니다. 원천 데이터에서 Oracle로 적재하기 전, 스테이징 테이블에서 초(SS), 분(MI), 시(HH) 등 모든 시간 구성요소의 유효 범위를 검사하는 쿼리를 배치 JOB의 첫 번째 스텝으로 포함시켜야 합니다. 이상 데이터가 발견되면 알림을 발송하고 적재를 중단하는 자동화 로직을 구현하는 것이 Best Practice입니다.

---

관련 에러

• ORA-01850: hour must be between 0 and 23 — 시(hour) 값이 0~23 범위를 벗어났을 때 발생하며, ORA-01852와 동일한 맥락의 시간 구성요소 유효성 에러입니다.
• ORA-01851: minutes must be between 0 and 59 — 분(minute) 값이 유효 범위를 벗어났을 때 발생하며, ORA-01852와 거의 동일한 원인과 해결 방법을 공유합니다.
• ORA-01843: not a valid month — 월(month) 값이 유효하지 않을 때 발생하는 에러로, 날짜 구성요소 전반에 걸친 유효성 문제를 다룰 때 함께 점검해야 합니다.
• ORA-01847: day of month must be between 1 and last day of month — 일(day) 값이 해당 월의 마지막 날을 초과할 때 발생합니다. 날짜 데이터 마이그레이션 시 ORA-01852와 함께 복합적으로 나타나는 경우가 많습니다.

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