DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01728 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01728: numeric scale specifier is out of range

ORA-01728 is thrown by Oracle when the scale value specified for a NUMBER data type falls outside the permitted range of -84 to 127. This error can occur during DDL statements (CREATE TABLE, ALTER TABLE), explicit type conversions (CAST, TO_NUMBER), or in dynamic SQL where scale values are constructed at runtime. Understanding the valid boundaries of Oracle's NUMBER type is the fastest way to resolve this error.

Top 3 Causes and Fixes

1. Invalid Scale in Table DDL

Specifying a scale value greater than 127 or less than -84 in a CREATE TABLE or ALTER TABLE statement is the most common cause.

-- ERROR: Scale 130 exceeds maximum of 127
CREATE TABLE products (
    price NUMBER(15, 130)   -- ORA-01728 triggered here
);

-- FIX: Use a valid scale within -84 to 127
CREATE TABLE products (
    price NUMBER(15, 4)     -- 4 decimal places
);

-- Verify column definition after creation
SELECT column_name, data_type, data_precision, data_scale
FROM   user_tab_columns
WHERE  table_name = 'PRODUCTS';
Enter fullscreen mode Exit fullscreen mode

2. Out-of-Range Scale in Dynamic SQL or CAST

When scale values are generated dynamically (e.g., read from a config file or external system) without validation, runtime execution of CAST or dynamic SQL can trigger ORA-01728.

-- ERROR: Dynamic scale value is out of range
DECLARE
    v_scale  NUMBER := 200;  -- Invalid value from external source
    v_result NUMBER;
BEGIN
    EXECUTE IMMEDIATE
        'SELECT CAST(9999.99 AS NUMBER(10,' || v_scale || ')) FROM DUAL'
        INTO v_result;
END;
/

-- FIX: Validate scale before use
DECLARE
    v_scale  NUMBER := 200;
    v_result NUMBER;
BEGIN
    -- Clamp scale to valid Oracle range
    IF v_scale < -84 THEN v_scale := -84;
    ELSIF v_scale > 127 THEN v_scale := 127;
    END IF;

    EXECUTE IMMEDIATE
        'SELECT CAST(9999.99 AS NUMBER(10,' || v_scale || ')) FROM DUAL'
        INTO v_result;

    DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
END;
/
Enter fullscreen mode Exit fullscreen mode

3. Incorrect Scale During ALTER TABLE MODIFY

Changing a column's data type with ALTER TABLE ... MODIFY using an invalid scale value will fail immediately, halting deployment scripts.

-- Check current column definition first
SELECT column_name, data_precision, data_scale
FROM   user_tab_columns
WHERE  table_name  = 'PRODUCTS'
AND    column_name = 'PRICE';

-- ERROR: Scale value 200 is out of range
ALTER TABLE products MODIFY (price NUMBER(15, 200));  -- ORA-01728

-- FIX: Use a valid scale value
ALTER TABLE products MODIFY (price NUMBER(15, 6));

-- Confirm the change
SELECT column_name, data_precision, data_scale
FROM   user_tab_columns
WHERE  table_name  = 'PRODUCTS'
AND    column_name = 'PRICE';
Enter fullscreen mode Exit fullscreen mode

Quick Fix Summary

Situation Invalid Example Correct Example
CREATE TABLE NUMBER(10, 130) NUMBER(10, 4)
ALTER TABLE MODIFY NUMBER(15, -90) NUMBER(15, 2)
CAST expression CAST(x AS NUMBER(10,200)) CAST(x AS NUMBER(10,6))

Prevention Tips

1. Standardize NUMBER type definitions across your team.
Create a data type standards document that maps business domains to approved NUMBER formats (e.g., monetary amounts → NUMBER(15,2), rates → NUMBER(7,4)). Integrate a SQL linting tool into your CI/CD pipeline to catch invalid type declarations before they reach production.

2. Always validate dynamic scale and precision values.
Any code that constructs NUMBER type parameters dynamically must include a boundary check before execution. A simple reusable function like the one below eliminates the risk entirely:

CREATE OR REPLACE FUNCTION safe_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;  -- Default fallback scale
    END IF;
    RETURN p_scale;
END safe_scale;
/
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • ORA-01727 — Scale's sibling error: fires when the precision value is outside the range of 1 to 38.
  • ORA-01426 — numeric overflow: occurs when actual data exceeds the defined NUMBER range at runtime.
  • ORA-06502 — PL/SQL: numeric or value error: often accompanies type definition mistakes inside PL/SQL blocks.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)