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

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

이 글에서 다루는 내용

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

ORA-01840 input value not long enough for date format 는?

ORA-01840 에러는 Oracle에서 문자열을 날짜(DATE) 또는 타임스탬프(TIMESTAMP) 타입으로 변환할 때, 입력된 문자열의 길이가 지정된 날짜 포맷 마스크보다 짧을 경우 발생합니다. 예를 들어, TO_DATE('2024', 'YYYY-MM-DD') 처럼 포맷은 10자리를 요구하는데 실제 입력값이 그보다 짧을 때 Oracle이 이 에러를 던집니다. 이 에러는 ETL 작업, 데이터 마이그레이션, 또는 사용자 입력 처리 중에 특히 자주 발생하며, 데이터 품질 문제와 긴밀하게 연결되어 있습니다.

주요 발생 원인

1. TO_DATE / TO_TIMESTAMP 함수에서 포맷 마스크와 입력값 불일치

가장 흔한 원인으로, 개발자가 포맷 마스크를 정확히 지정했지만 실제 데이터가 그 포맷보다 짧게 들어오는 경우입니다. 예를 들어, 외부 시스템에서 받은 날짜 문자열이 시분초 없이 날짜만 포함되어 있는데, 포맷에는 시분초까지 포함되어 있는 경우에 이 에러가 발생합니다. 특히 배치 프로그램이나 인터페이스 처리 시 외부 데이터의 포맷이 일정하지 않을 때 빈번하게 나타납니다.

2. 빈 문자열(”)이나 NULL이 아닌 공백 또는 불완전한 날짜 문자열 전달

입력값이 NULL이 아니라 공백(‘ ‘)이거나 ‘2024-01’ 처럼 불완전한 날짜 문자열일 때도 이 에러가 발생합니다. NULL은 Oracle이 자동으로 처리하지만, 빈 문자열처럼 보이는 값이나 일부만 입력된 날짜 문자열은 포맷 파싱 단계에서 실패하게 됩니다. 데이터 소스가 다양한 레거시 시스템이거나 사용자가 직접 입력하는 경우 이런 상황이 자주 발생합니다.

3. 동적 SQL 또는 바인드 변수 처리 시 데이터 타입 혼용

동적 SQL을 구성하거나 Java, Python 등 외부 언어에서 바인드 변수를 통해 날짜값을 전달할 때, 문자열 타입으로 날짜를 전달하면서 포맷 마스크와 실제 값이 맞지 않는 경우가 발생합니다. 특히 프로그램 로직에서 날짜 포맷을 동적으로 결정하거나, 환경에 따라 NLS_DATE_FORMAT 설정이 다를 때 이 문제가 더욱 심화됩니다.

해결 방법

원인 1 해결: 포맷 마스크를 입력값에 맞게 수정

입력값의 실제 형식을 먼저 파악하고, 그에 맞는 포맷 마스크를 사용하는 것이 기본입니다.

-- 잘못된 예: 포맷은 시분초를 포함하지만 입력값은 날짜만 있음
SELECT TO_DATE('2024-07-15', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;
-- ORA-01840 발생

-- 올바른 예: 입력값에 맞는 포맷 마스크 사용
SELECT TO_DATE('2024-07-15', 'YYYY-MM-DD') FROM DUAL;

-- 시분초까지 포함된 경우
SELECT TO_DATE('2024-07-15 13:45:30', 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

-- TO_TIMESTAMP 사용 시에도 동일하게 적용
SELECT TO_TIMESTAMP('2024-07-15 13:45:30.123', 'YYYY-MM-DD HH24:MI:SS.FF3') FROM DUAL;

원인 2 해결: 입력값 유효성 검사 및 예외 처리 적용

입력값이 불완전하거나 비어있을 경우를 대비해 CASE 문이나 REGEXP_LIKE를 활용한 유효성 검사를 적용합니다.

-- CASE 문을 이용한 방어적 처리
SELECT 
    CASE 
        WHEN REGEXP_LIKE(date_str, '^\d{4}-\d{2}-\d{2}$') 
            THEN TO_DATE(date_str, 'YYYY-MM-DD')
        WHEN REGEXP_LIKE(date_str, '^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$') 
            THEN TO_DATE(date_str, 'YYYY-MM-DD HH24:MI:SS')
        ELSE NULL 
    END AS parsed_date
FROM your_table;

-- LENGTH 함수를 이용한 사전 필터링
SELECT TO_DATE(date_str, 'YYYY-MM-DD')
FROM your_table
WHERE LENGTH(TRIM(date_str)) = 10;  -- 'YYYY-MM-DD' 형식은 반드시 10자

-- PL/SQL에서 예외 처리를 통한 안전한 변환
DECLARE
    v_date DATE;
    v_str  VARCHAR2(50) := '2024-07';  -- 불완전한 날짜
BEGIN
    BEGIN
        v_date := TO_DATE(v_str, 'YYYY-MM-DD');
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('날짜 변환 실패: ' || v_str || ' / 에러: ' || SQLERRM);
            v_date := NULL;
    END;
    DBMS_OUTPUT.PUT_LINE('변환 결과: ' || TO_CHAR(v_date, 'YYYY-MM-DD'));
END;
/

원인 3 해결: NLS_DATE_FORMAT 확인 및 명시적 포맷 지정

세션 또는 시스템 레벨의 NLS 설정을 확인하고, 항상 명시적인 포맷 마스크를 사용합니다.

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

-- 세션 레벨에서 NLS_DATE_FORMAT 설정
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

-- 절대 암묵적 변환에 의존하지 말고 항상 명시적 포맷 사용
-- 나쁜 예 (NLS 설정에 의존)
INSERT INTO orders (order_date) VALUES ('2024-07-15');

-- 좋은 예 (명시적 포맷 지정)
INSERT INTO orders (order_date) VALUES (TO_DATE('2024-07-15', 'YYYY-MM-DD'));

-- 다양한 포맷 처리가 필요할 때 DEFAULT 절 활용 (Oracle 12c 이상)
SELECT TO_DATE(date_str DEFAULT NULL ON CONVERSION ERROR, 'YYYY-MM-DD') AS safe_date
FROM your_table;

예방 방법

1. 명시적 포맷 마스크 사용 정책 수립 (Coding Standard 적용)

팀 전체의 개발 표준으로 날짜 변환 시 절대 암묵적 변환을 사용하지 않도록 코딩 규칙을 정립하세요. 모든 TO_DATE, TO_TIMESTAMP 함수 호출에는 반드시 포맷 마스크를 명시하고, 코드 리뷰 체크리스트에 이 항목을 반드시 포함시킵니다. 또한 Oracle 12c R2 이상에서는 DEFAULT ... ON CONVERSION ERROR 구문을 활용하여 변환 실패 시에도 프로그램이 중단되지 않도록 방어적 코딩 패턴을 적용하는 것이 좋습니다.

2. 입력 데이터 사전 검증 프로세스 구축

ETL 또는 데이터 적재 프로세스에서 날짜 컬럼에 해당하는 원시 데이터를 적재 전에 반드시 정규식(REGEXP_LIKE)이나 LENGTH 체크로 검증하는 단계를 추가합니다. 검증에 실패한 레코드는 별도의 오류 테이블(예: ERR_DATE_FORMAT_LOG)에 기록하여 운영팀이 추적하고 수정할 수 있는 체계를 만듭니다. 이 방식은 대용량 배치 처리에서 단 한 건의 잘못된 날짜 때문에 전체 작업이 실패하는 상황을 방지하는 데 매우 효과적입니다.