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

ORA-30076 invalid extract field for extract source 는?

ORA-30076 에러는 Oracle의 EXTRACT 함수를 사용할 때, 추출하려는 필드(YEAR, MONTH, DAY, HOUR, MINUTE, SECOND 등)가 해당 데이터 소스 타입에서 지원되지 않을 때 발생합니다. 예를 들어, DATE 타입이나 TIMESTAMP 타입에서 지원하지 않는 필드를 추출하려 하거나, INTERVAL 타입에서 허용되지 않는 필드를 지정할 경우 이 에러가 트리거됩니다. 30년간 DBA를 하면서 이 에러는 주로 개발자들이 EXTRACT 함수의 소스 타입별 지원 필드를 혼동하거나, 잘못된 데이터 타입의 컬럼에 무리하게 적용하려 할 때 빈번하게 발생하는 것을 경험했습니다.

---

주요 발생 원인

1. DATE/TIMESTAMP 타입에서 지원되지 않는 필드 추출 시도

가장 흔한 원인입니다. Oracle의 DATE 타입은 YEAR, MONTH, DAY, HOUR, MINUTE, SECOND 필드를 모두 지원하지만, INTERVAL YEAR TO MONTH 타입에서는 DAY, HOUR, MINUTE, SECOND를 추출할 수 없습니다. 반대로 INTERVAL DAY TO SECOND 타입에서는 YEAR, MONTH를 추출하려 하면 바로 ORA-30076이 발생합니다. 개발자들이 INTERVAL 타입의 특성을 정확히 파악하지 못한 채 쿼리를 작성하는 경우가 실무에서 매우 잦습니다.

2. TIMEZONE 관련 필드를 일반 DATE 타입에 사용

TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION, TIMEZONE_ABBR 필드는 오직 TIMESTAMP WITH TIME ZONE 또는 TIMESTAMP WITH LOCAL TIME ZONE 타입에서만 추출 가능합니다. 일반 DATE 타입이나 TIMESTAMP 타입의 컬럼에 이 필드들을 사용하려 하면 반드시 ORA-30076이 발생합니다. 시스템 간 데이터 이관 작업이나 글로벌 서비스 개발 중에 특히 자주 만나게 되는 패턴입니다.

3. 문자열(VARCHAR2) 또는 숫자 타입 컬럼에 EXTRACT 함수 적용

EXTRACT 함수는 날짜/시간/인터벌 계열 데이터 타입에만 적용 가능합니다. VARCHAR2 형태로 저장된 날짜 문자열이나 숫자 타입 컬럼에 직접 EXTRACT를 사용하면 ORA-30076 또는 ORA-00932 같은 에러가 함께 발생할 수 있습니다. 레거시 시스템에서는 날짜를 문자열로 저장하는 경우가 많아, 마이그레이션이나 레포팅 쿼리 작성 시 이런 실수가 빈번합니다.

---

해결 방법

원인 1 해결: INTERVAL 타입별 지원 필드 확인 후 수정

INTERVAL YEAR TO MONTH에서는 YEAR 또는 MONTH만, INTERVAL DAY TO SECOND에서는 DAY, HOUR, MINUTE, SECOND만 추출 가능합니다.

-- 잘못된 예: INTERVAL YEAR TO MONTH 에서 DAY 추출 시도 → ORA-30076 발생
SELECT EXTRACT(DAY FROM INTERVAL '3-5' YEAR TO MONTH) FROM DUAL;

-- 올바른 예: INTERVAL YEAR TO MONTH 에서 YEAR, MONTH만 추출
SELECT EXTRACT(YEAR FROM INTERVAL '3-5' YEAR TO MONTH) AS 년,
       EXTRACT(MONTH FROM INTERVAL '3-5' YEAR TO MONTH) AS 월
FROM DUAL;

-- 올바른 예: INTERVAL DAY TO SECOND 에서 DAY, HOUR, MINUTE, SECOND 추출
SELECT EXTRACT(DAY    FROM INTERVAL '2 10:30:15' DAY TO SECOND) AS 일,
       EXTRACT(HOUR   FROM INTERVAL '2 10:30:15' DAY TO SECOND) AS 시,
       EXTRACT(MINUTE FROM INTERVAL '2 10:30:15' DAY TO SECOND) AS 분,
       EXTRACT(SECOND FROM INTERVAL '2 10:30:15' DAY TO SECOND) AS 초
FROM DUAL;

-- 실무 예: 두 날짜 사이의 차이를 INTERVAL로 계산 후 필드 추출
SELECT EXTRACT(DAY FROM (SYSDATE - TO_DATE('2024-01-01','YYYY-MM-DD'))
       DAY TO SECOND) AS 경과일수
FROM DUAL;

원인 2 해결: TIMEZONE 필드는 반드시 TIMESTAMP WITH TIME ZONE 타입 사용

-- 잘못된 예: 일반 TIMESTAMP에서 TIMEZONE_HOUR 추출 → ORA-30076 발생
SELECT EXTRACT(TIMEZONE_HOUR FROM SYSTIMESTAMP) FROM DUAL;
-- ※ SYSTIMESTAMP는 TIMESTAMP WITH TIME ZONE 타입이므로 위는 실제 정상 동작
--   하지만 아래처럼 일반 TIMESTAMP 변수나 컬럼에선 에러 발생

-- 잘못된 예: 일반 DATE 타입에서 TIMEZONE_HOUR 추출
SELECT EXTRACT(TIMEZONE_HOUR FROM SYSDATE) FROM DUAL;
-- ORA-30076: invalid extract field for extract source

-- 올바른 해결: CAST를 이용해 TIMESTAMP WITH TIME ZONE으로 변환 후 추출
SELECT EXTRACT(TIMEZONE_HOUR FROM CAST(SYSDATE AS TIMESTAMP WITH TIME ZONE)) AS tz_hour
FROM DUAL;

-- TIMESTAMP WITH TIME ZONE 컬럼에서 정상 추출
SELECT EXTRACT(TIMEZONE_HOUR   FROM col_tstz) AS tz_hour,
       EXTRACT(TIMEZONE_MINUTE FROM col_tstz) AS tz_minute,
       EXTRACT(TIMEZONE_REGION FROM col_tstz) AS tz_region
FROM   your_table;

-- AT TIME ZONE 절 활용
SELECT EXTRACT(TIMEZONE_HOUR FROM SYSTIMESTAMP AT TIME ZONE 'Asia/Seoul') AS seoul_tz_hour
FROM DUAL;

원인 3 해결: 문자열 및 숫자 타입을 날짜 타입으로 변환 후 EXTRACT 적용

-- 잘못된 예: 문자열 컬럼에 직접 EXTRACT 사용 → 에러 발생
SELECT EXTRACT(YEAR FROM '2024-07-15') FROM DUAL;  -- ORA-30076 또는 ORA-00932

-- 올바른 해결: TO_DATE 또는 TO_TIMESTAMP로 변환 후 EXTRACT 사용
SELECT EXTRACT(YEAR  FROM TO_DATE('2024-07-15', 'YYYY-MM-DD')) AS 년도,
       EXTRACT(MONTH FROM TO_DATE('2024-07-15', 'YYYY-MM-DD')) AS 월,
       EXTRACT(DAY   FROM TO_DATE('2024-07-15', 'YYYY-MM-DD')) AS 일
FROM DUAL;

-- 실무 예: 테이블의 VARCHAR2 날짜 컬럼 처리
SELECT employee_id,
       EXTRACT(YEAR  FROM TO_DATE(hire_date_str, 'YYYY-MM-DD')) AS 입사년도,
       EXTRACT(MONTH FROM TO_DATE(hire_date_str, 'YYYY-MM-DD')) AS 입사월
FROM   employees_legacy
WHERE  hire_date_str IS NOT NULL;

-- 숫자형 날짜(YYYYMMDD) 처리
SELECT EXTRACT(YEAR FROM TO_DATE(TO_CHAR(20240715), 'YYYYMMDD')) AS 년도
FROM DUAL;

-- 안전한 변환: CASE + REGEXP를 활용한 방어적 쿼리
SELECT employee_id,
       CASE
         WHEN REGEXP_LIKE(hire_date_str, '^\d{4}-\d{2}-\d{2}$')
         THEN EXTRACT(YEAR FROM TO_DATE(hire_date_str, 'YYYY-MM-DD'))
         ELSE NULL
       END AS 입사년도
FROM employees_legacy;

---

예방 방법

1. EXTRACT 함수 사용 전 소스 데이터 타입과 지원 필드 매핑 테이블 숙지

아래 매핑을 팀 내 코딩 가이드라인에 반드시 포함시키세요. 타입별로 추출 가능한 필드가 명확히 다르므로, 개발 초기 단계에서 데이터 타입을 정확히 정의하고 문서화하는 것이 핵심입니다.

| 소스 타입 | 추출 가능 필드 |

|—|—|

| DATE | YEAR, MONTH, DAY, HOUR, MINUTE, SECOND |

| TIMESTAMP | YEAR, MONTH, DAY, HOUR, MINUTE, SECOND |

| TIMESTAMP WITH TIME ZONE | YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, TIMEZONE_HOUR, TIMEZONE_MINUTE, TIMEZONE_REGION, TIMEZONE_ABBR |

| INTERVAL YEAR TO MONTH | YEAR, MONTH |

| INTERVAL DAY TO SECOND | DAY, HOUR, MINUTE, SECOND |

-- 컬럼 데이터 타입 사전 확인 쿼리 (필수 습관화)
SELECT column_name, data_type, data_length, data_precision
FROM   all_tab_columns
WHERE  table_name  = 'YOUR_TABLE'
  AND  column_name IN ('HIRE_DATE', 'CREATED_AT', 'UPDATED_AT');

2. 개발 및 QA 환경에서 EXTRACT 관련 단위 테스트 자동화

CI/CD 파이프라인 또는 정기 배치 검증 단계에 EXTRACT 함수 관련 단위 테스트를 포함시켜, 운영 환경 배포 전에 오류를 사전에 차단하세요. 특히 날짜 타입이 변경될 수 있는 마이그레이션 프로젝트에서는 회귀 테스트가 필수입니다.

-- 단위 테스트용 검증 쿼리 예시
BEGIN
  DECLARE
    v_year  NUMBER;
    v_month NUMBER;
  BEGIN
    SELECT EXTRACT(YEAR  FROM SYSDATE),
           EXTRACT(MONTH FROM SYSDATE)
    INTO   v_year, v_month
    FROM   DUAL;
    DBMS_OUTPUT.PUT_LINE('테스트 통과: YEAR=' || v_year || ', MONTH=' || v_month);
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('테스트 실패: ' || SQLERRM);
  END;
END;
/

---

관련 에러

• ORA-00932: 데이터 타입이 완전히 맞지 않아 묵시적 변환조차 불가할 때 발생. EXTRACT 소스로 NUMBER나 VARCHAR2를 그대로 넣을 경우 ORA-30076과 함께 또는 대신 발생할 수 있습니다.
• ORA-01830: 날짜 형식 변환 시 형식 문자열 불일치로 발생. TO_DATE 변환 과정에서 함께 만날 수 있습니다.
• ORA-01847: 월(MONTH) 값이 유효 범위를 벗어날 때 발생. EXTRACT 후 결과값을 다시 날짜 구성에 사용할 때 주의해야 합니다.
• ORA-30088: DATETIME/INTERVAL 값이 정밀도 범위를 초과할 때 발생. INTERVAL 타입 연산과 함께 EXTRACT를 사용할 때 연관되어 나타날 수 있습니다.

---

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