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

ORA-01728 numeric scale specifier is out of range 는?

ORA-01728 에러는 Oracle에서 숫자형 데이터 타입(NUMBER)을 선언하거나 사용할 때 소수점 이하 자릿수(Scale) 값이 허용 범위를 벗어났을 때 발생하는 에러입니다. Oracle의 NUMBER 타입에서 Scale은 -84부터 127 사이의 값만 허용되며, 이 범위를 초과하거나 미만인 값을 지정하면 즉시 해당 에러가 발생합니다. 주로 테이블 생성(CREATE TABLE), 컬럼 변경(ALTER TABLE), 또는 명시적 형변환(CAST, TO_NUMBER) 구문에서 잘못된 Scale 값을 입력했을 때 실무 현장에서 빈번하게 나타납니다.

---

주요 발생 원인

1. NUMBER 타입 선언 시 Scale 범위 초과

가장 흔한 원인으로, 테이블 DDL 작성 시 NUMBER(precision, scale) 형식에서 scale 값을 128 이상 또는 -85 이하로 잘못 입력하는 경우입니다. 예를 들어 NUMBER(10, 130)처럼 127을 초과하는 값을 입력하거나, NUMBER(10, -90)처럼 -84 미만의 음수 scale을 지정하면 에러가 발생합니다. 개발자가 precision과 scale의 의미를 혼동하거나, 외부 시스템의 스키마를 마이그레이션하는 과정에서 데이터 타입 정의가 잘못 변환되어 발생하는 경우가 많습니다.

2. CAST 또는 TO_NUMBER 함수에서 잘못된 포맷 마스크 사용

동적 SQL 또는 ETL 작업 중 CAST(value AS NUMBER(p, s)) 또는 TO_NUMBER(value, format) 함수를 사용할 때, 프로그램 로직에서 동적으로 생성된 scale 값이 허용 범위를 벗어나는 경우입니다. 특히 외부 데이터 소스에서 메타데이터를 읽어와 동적으로 타입을 결정하는 방식의 코드에서 유효성 검사 없이 그대로 사용하면 런타임에 ORA-01728이 발생할 수 있습니다. 이는 운영 중인 배치 작업에서 예기치 않게 터지는 경우가 많아 장애로 이어지기 쉽습니다.

3. ALTER TABLE 컬럼 수정 시 잘못된 타입 변경

운영 중인 테이블의 컬럼 타입을 변경하는 ALTER TABLE ... MODIFY 구문을 실행할 때, 기존 데이터와의 호환성을 고려하지 않고 scale 값을 잘못 지정하는 경우입니다. 특히 대규모 운영 환경에서 DBA나 개발자가 스크립트를 빠르게 작성하는 과정에서 오타나 논리 오류로 허용 범위를 벗어난 scale 값을 입력하게 됩니다. 이 경우 DDL이 롤백되어 데이터 변경은 없지만, 운영 스크립트가 중단되어 전체 배포에 영향을 줄 수 있습니다.

---

해결 방법

원인 1 해결: NUMBER 타입 선언 수정

Scale 값을 Oracle 허용 범위인 -84 ~ 127 이내로 수정합니다.

-- 잘못된 예시 (에러 발생)
CREATE TABLE order_detail (
    order_id    NUMBER(10),
    unit_price  NUMBER(15, 130),   -- Scale 130은 허용 범위 초과!
    discount    NUMBER(5, -90)     -- Scale -90은 허용 범위 미만!
);

-- 올바른 예시 (수정 후)
CREATE TABLE order_detail (
    order_id    NUMBER(10),
    unit_price  NUMBER(15, 4),     -- 소수점 4자리
    discount    NUMBER(5, 2)       -- 소수점 2자리 (퍼센트)
);

원인 2 해결: CAST / TO_NUMBER 함수 수정

동적으로 생성되는 scale 값에 대해 반드시 유효성 검사를 추가합니다.

-- 잘못된 예시 (동적 scale이 범위를 벗어날 경우 에러 발생)
DECLARE
    v_scale   NUMBER := 130;  -- 외부에서 잘못 받아온 값
    v_value   VARCHAR2(50) := '12345.678';
    v_result  NUMBER;
BEGIN
    -- 아래 구문은 ORA-01728 발생
    EXECUTE IMMEDIATE 'SELECT CAST(:1 AS NUMBER(15,' || v_scale || ')) FROM DUAL'
        INTO v_result USING v_value;
END;
/

-- 올바른 예시 (유효성 검사 추가)
DECLARE
    v_scale   NUMBER := 130;
    v_value   VARCHAR2(50) := '12345.678';
    v_result  NUMBER;
BEGIN
    -- Scale 유효성 검사
    IF v_scale < -84 OR v_scale > 127 THEN
        DBMS_OUTPUT.PUT_LINE('ERROR: Scale 값이 허용 범위(-84 ~ 127)를 벗어났습니다. 기본값(4)으로 대체합니다.');
        v_scale := 4;  -- 기본값으로 대체
    END IF;

    EXECUTE IMMEDIATE 'SELECT CAST(:1 AS NUMBER(15,' || v_scale || ')) FROM DUAL'
        INTO v_result USING v_value;

    DBMS_OUTPUT.PUT_LINE('변환 결과: ' || v_result);
END;
/

-- TO_NUMBER 사용 시 올바른 예시
SELECT TO_NUMBER('12345.6789', '99999.9999') AS converted_value FROM DUAL;

원인 3 해결: ALTER TABLE 수정

컬럼 변경 전 반드시 현재 타입을 확인하고 올바른 scale 값을 지정합니다.

-- 현재 컬럼 타입 확인
SELECT column_name, data_type, data_precision, data_scale
FROM   user_tab_columns
WHERE  table_name = 'ORDER_DETAIL'
ORDER BY column_id;

-- 잘못된 예시 (에러 발생)
ALTER TABLE order_detail MODIFY (unit_price NUMBER(15, 200));  -- ORA-01728

-- 올바른 예시 (수정 후)
ALTER TABLE order_detail MODIFY (unit_price NUMBER(15, 6));    -- 소수점 6자리로 변경

-- 변경 확인
SELECT column_name, data_type, data_precision, data_scale
FROM   user_tab_columns
WHERE  table_name  = 'ORDER_DETAIL'
AND    column_name = 'UNIT_PRICE';

---

예방 방법

1. DDL 스크립트 사전 검토 및 명명 규칙 표준화

모든 DDL 스크립트는 운영 환경 반영 전 반드시 개발/검증 환경에서 사전 테스트를 수행해야 합니다. 사내 데이터 표준 정의서에 NUMBER 타입 사용 가이드라인(예: 금액 컬럼은 NUMBER(15,2), 비율 컬럼은 NUMBER(7,4))을 명시하여 팀 전체가 공유하고 준수하도록 합니다. 또한 CI/CD 파이프라인에 SQL 린팅(Linting) 도구를 통합하여 잘못된 데이터 타입 선언을 자동으로 감지하는 체계를 구축하는 것이 좋습니다.

2. 동적 SQL에서 타입 관련 파라미터 반드시 검증

프로그램 코드에서 NUMBER의 precision이나 scale을 외부 입력값이나 설정 파일에서 읽어와 동적으로 사용하는 경우, 반드시 사용 전에 허용 범위 검증 로직을 추가해야 합니다. 아래와 같이 공통 검증 함수를 만들어 재사용하는 방식을 권장합니다.

-- Scale 유효성 검사 공통 함수 생성
CREATE OR REPLACE FUNCTION validate_number_scale(p_scale IN NUMBER)
RETURN NUMBER
IS
BEGIN
    IF p_scale IS NULL OR p_scale < -84 OR p_scale > 127 THEN
        -- 로그 기록 또는 기본값 반환
        RETURN 2;  -- 기본 scale 값
    END IF;
    RETURN p_scale;
END validate_number_scale;
/

-- 사용 예시
DECLARE
    v_safe_scale NUMBER;
BEGIN
    v_safe_scale := validate_number_scale(130);  -- 잘못된 값 입력
    DBMS_OUTPUT.PUT_LINE('안전한 Scale 값: ' || v_safe_scale);  -- 출력: 2
END;
/

---

관련 에러

• ORA-01727: numeric precision specifier is out of range (1 to 38) — NUMBER 타입의 precision 값이 1~38 범위를 벗어났을 때 발생하며, ORA-01728과 함께 자주 짝을 이루어 나타납니다. Precision과 Scale 둘 다 유효성을 함께 체크해야 합니다.
• ORA-01426: numeric overflow — 실제 데이터가 컬럼에 정의된 NUMBER 타입의 범위를 초과할 때 발생합니다. ORA-01728이 타입 선언 단계의 문제라면, ORA-01426은 데이터 삽입/연산 단계의 문제입니다.
• ORA-06502: PL/SQL: numeric or value error — PL/SQL 블록 내에서 NUMBER 변수에 허용 범위를 초과하는 값을 할당하려 할 때 발생하며, 잘못된 타입 선언과 연계되어 나타나는 경우가 많습니다.

---

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