DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01724 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01724: Floating Point Precision Is Out of Range

ORA-01724 is thrown by Oracle Database when you define a FLOAT column with a binary precision value outside the allowed range of 1 to 126. This error most commonly surfaces during table creation, column modification, or when migrating DDL scripts from other database systems such as MySQL or SQL Server.

Top 3 Causes

1. Precision Value Exceeds the Allowed Range (1–126)

Oracle's FLOAT(p) type uses binary precision, not decimal. Specifying a value greater than 126 or less than 1 immediately triggers ORA-01724.

-- ERROR: precision 130 exceeds maximum of 126
CREATE TABLE financial_data (
    id      NUMBER(10),
    amount  FLOAT(130)   -- ORA-01724 raised here
);

-- FIX: clamp precision to the valid range
CREATE TABLE financial_data (
    id      NUMBER(10),
    amount  FLOAT(126)   -- maximum binary precision allowed
);

-- BETTER FIX: use NUMBER for predictable decimal precision
CREATE TABLE financial_data (
    id      NUMBER(10),
    amount  NUMBER(38, 10)
);
Enter fullscreen mode Exit fullscreen mode

2. DDL Scripts Migrated from Other Databases Without Type Conversion

MySQL's FLOAT(53) means double-precision in decimal terms, which maps to a value Oracle cannot accept for its own FLOAT type without conversion.

-- MySQL original (FLOAT(53) = double precision in MySQL)
-- CREATE TABLE orders (order_id INT, price FLOAT(53));

-- Oracle equivalent — use BINARY_DOUBLE or NUMBER instead
CREATE TABLE orders (
    order_id  NUMBER(10),
    price     BINARY_DOUBLE    -- maps cleanly to IEEE 754 double
);

-- Alternatively, use NUMBER for exact decimal storage
CREATE TABLE orders (
    order_id  NUMBER(10),
    price     NUMBER(20, 6)
);

-- Quick reference: FLOAT binary-to-decimal precision mapping
-- FLOAT(23)  ≈ 7  decimal digits  (single precision)
-- FLOAT(53)  ≈ 15 decimal digits  (double precision)
-- FLOAT(126) = 38 decimal digits  (Oracle maximum)
Enter fullscreen mode Exit fullscreen mode

3. Dynamic DDL Generation Passing Unvalidated Precision Values

When DDL is built programmatically, an unchecked variable can silently pass an out-of-range precision value at runtime.

-- Safe dynamic DDL with PL/SQL validation
DECLARE
    v_prec  PLS_INTEGER := 200;   -- simulated bad input
    v_sql   VARCHAR2(500);
BEGIN
    -- Validate before use
    IF v_prec NOT BETWEEN 1 AND 126 THEN
        DBMS_OUTPUT.PUT_LINE('WARNING: invalid precision ' || v_prec ||
                             '. Defaulting to 126.');
        v_prec := 126;
    END IF;

    v_sql := 'CREATE TABLE dyn_test (id NUMBER(10), val FLOAT('
             || v_prec || '))';
    EXECUTE IMMEDIATE v_sql;
    DBMS_OUTPUT.PUT_LINE('Table created successfully.');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions 

-- Option A: Fix the precision to a valid value
ALTER TABLE financial_data MODIFY (amount FLOAT(53));

-- Option B: Switch to NUMBER (recommended for financial data)
ALTER TABLE financial_data MODIFY (amount NUMBER(38, 10));

-- Option C: Switch to BINARY_DOUBLE (IEEE 754, best performance)
-- Must recreate column if data exists
ALTER TABLE financial_data ADD (amount_new BINARY_DOUBLE);
UPDATE financial_data SET amount_new = amount;
ALTER TABLE financial_data DROP COLUMN amount;
ALTER TABLE financial_data RENAME COLUMN amount_new TO amount;

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

Prevention Tips

Standardize on NUMBER or BINARY_DOUBLE instead of FLOAT.
Oracle's FLOAT type is kept mainly for ANSI compatibility. For financial or scientific data, NUMBER(p, s) gives exact decimal storage while BINARY_DOUBLE delivers IEEE 754 performance. Documenting this in your team's DDL coding standards eliminates an entire class of precision-related errors.

Always validate DDL scripts before applying to production.
Add a lightweight lint step to your CI/CD pipeline that parses DDL files and flags any FLOAT(p) where p < 1 or p > 126. For migration projects, maintain an explicit type-mapping document (source type → Oracle type) and require peer review on all schema change scripts.

Related Errors

Error Code Description
ORA-01727 Numeric precision out of range (1–38) for NUMBER type
ORA-01728 Numeric scale out of range (-84 to 127) for NUMBER type
ORA-00902 Invalid datatype name — common in cross-DBMS migrations
ORA-01439 Column must be empty to change datatype during ALTER TABLE

📖 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)