ORA-01438 Value Larger Than Specified Precision - Complete Resolution Guide

ORA-01438: Value Larger Than Specified Precision Allowed for This Column

Error Overview

Error Text: ORA-01438: value larger than specified precision allowed for this column

The ORA-01438 error occurs when you attempt to insert or update a numeric value that exceeds the defined precision of a NUMBER column. This error is specific to the integer portion (precision minus scale) being too large, not the decimal places.

Understanding NUMBER Precision

-- NUMBER(precision, scale)
-- precision = total number of digits
-- scale = digits after decimal point
-- integer digits allowed = precision - scale

NUMBER(5,2)   -- Max value: 999.99 (3 integer digits, 2 decimal)
NUMBER(7,2)   -- Max value: 99999.99 (5 integer digits, 2 decimal)
NUMBER(10,0)  -- Max value: 9999999999 (10 integer digits, 0 decimal)
NUMBER(3)     -- Max value: 999 (equivalent to NUMBER(3,0))

Common Causes

1. Data Exceeds Column Definition

• Inserting values larger than column allows
• Calculations producing larger results
• Data migration from larger precision source

2. Application/ETL Issues

• Source system has larger precision
• Currency conversion creating larger values
• Aggregate functions exceeding precision

3. Schema Design Problems

• Insufficient precision during design
• Business growth exceeding original estimates
• Currency precision miscalculations

Error Examples and Solutions

Example 1: Basic Precision Overflow

-- Table definition
CREATE TABLE products (
  product_id NUMBER(5),
  price NUMBER(5,2)  -- Max: 999.99
);

-- ERROR: Value too large for price column
INSERT INTO products VALUES (1, 1234.56);
-- ORA-01438: value larger than specified precision allowed for this column

-- The value 1234.56 needs 4 integer digits, but NUMBER(5,2) only allows 3

-- SOLUTION 1: Use appropriate value
INSERT INTO products VALUES (1, 999.99);

-- SOLUTION 2: Modify column precision
ALTER TABLE products MODIFY price NUMBER(7,2);  -- Now allows 99999.99
INSERT INTO products VALUES (1, 1234.56);  -- Works now

Example 2: Calculation Overflow

-- Column can hold individual values but not calculations
CREATE TABLE orders (
  order_id NUMBER(6),
  quantity NUMBER(3),      -- Max: 999
  unit_price NUMBER(5,2),  -- Max: 999.99
  total NUMBER(6,2)        -- Max: 9999.99
);

-- This might fail:
INSERT INTO orders VALUES (1, 100, 999.99, 100 * 999.99);
-- ORA-01438 because 100 * 999.99 = 99999.00 needs NUMBER(7,2)

-- SOLUTION: Increase precision
ALTER TABLE orders MODIFY total NUMBER(10,2);

Example 3: Update Causing Overflow

-- Existing data is fine
SELECT salary FROM employees WHERE employee_id = 100;
-- Returns: 24000

-- Column definition: salary NUMBER(8,2)  -- Max: 999999.99

-- ERROR: Update exceeds precision
UPDATE employees SET salary = salary * 100 WHERE employee_id = 100;
-- ORA-01438: 24000 * 100 = 2400000 exceeds 6 integer digits

-- SOLUTION 1: Check before update
UPDATE employees
SET salary = LEAST(salary * 100, 999999.99)
WHERE employee_id = 100;

-- SOLUTION 2: Modify column first
ALTER TABLE employees MODIFY salary NUMBER(12,2);
UPDATE employees SET salary = salary * 100 WHERE employee_id = 100;

Example 4: Bulk Insert Failures

-- Find which rows would cause the error
-- Before inserting from source table:
SELECT *
FROM source_table
WHERE amount > 999.99  -- Exceeds NUMBER(5,2) target
   OR amount < -999.99;

-- Or check integer digits specifically
SELECT *
FROM source_table
WHERE TRUNC(ABS(amount)) >= 1000;  -- More than 3 integer digits

Diagnostic Queries

Find Column Precision

-- Check precision of specific columns
SELECT
  column_name,
  data_type,
  data_precision,
  data_scale,
  data_precision - NVL(data_scale, 0) as integer_digits,
  CASE
    WHEN data_scale IS NOT NULL THEN
      RPAD('9', data_precision - data_scale, '9') || '.' || RPAD('9', data_scale, '9')
    ELSE
      RPAD('9', NVL(data_precision, 38), '9')
  END as max_value
FROM all_tab_columns
WHERE owner = 'SCHEMA_NAME'
  AND table_name = 'TABLE_NAME'
  AND data_type = 'NUMBER'
ORDER BY column_id;

Find Problem Data Before Insert

-- Check source data against target column precision
-- Target: NUMBER(7,2) allows max 99999.99

SELECT
  source_column,
  LENGTH(TRUNC(ABS(source_column))) as integer_digits,
  CASE
    WHEN LENGTH(TRUNC(ABS(source_column))) > 5 THEN 'WILL FAIL'
    ELSE 'OK'
  END as status
FROM source_table
WHERE LENGTH(TRUNC(ABS(source_column))) > 5;

Analyze Table for Maximum Values

-- Find actual max values in existing data
SELECT
  'current_max' as metric,
  MAX(amount) as max_val,
  MIN(amount) as min_val,
  MAX(LENGTH(TRUNC(ABS(amount)))) as max_integer_digits
FROM your_table;

-- Check if column needs resizing
SELECT
  column_name,
  data_precision,
  data_scale,
  data_precision - NVL(data_scale, 0) as defined_integer_digits
FROM all_tab_columns
WHERE table_name = 'YOUR_TABLE'
  AND column_name = 'AMOUNT';

Resolution Steps

1. Modify Column Precision

-- Increase precision (no data loss)
ALTER TABLE table_name MODIFY column_name NUMBER(new_precision, scale);

-- Example: Increase from NUMBER(5,2) to NUMBER(10,2)
ALTER TABLE products MODIFY price NUMBER(10,2);

-- Note: You can increase precision without data loss
-- Decreasing precision requires all data to fit in new size

2. Create New Column and Migrate

-- For more complex changes
-- Step 1: Add new column
ALTER TABLE products ADD price_new NUMBER(10,2);

-- Step 2: Copy data
UPDATE products SET price_new = price;

-- Step 3: Drop old column
ALTER TABLE products DROP COLUMN price;

-- Step 4: Rename new column
ALTER TABLE products RENAME COLUMN price_new TO price;

3. Handle Data During Insert

-- Truncate/round values that are too large
INSERT INTO target_table (id, amount)
SELECT
  id,
  CASE
    WHEN amount > 999.99 THEN 999.99
    WHEN amount < -999.99 THEN -999.99
    ELSE amount
  END
FROM source_table;

-- Or use LEAST/GREATEST
INSERT INTO target_table (id, amount)
SELECT
  id,
  GREATEST(LEAST(amount, 999.99), -999.99)
FROM source_table;

4. Log Overflow Records

-- Insert valid records, log invalid ones
DECLARE
  v_count NUMBER := 0;
BEGIN
  FOR rec IN (SELECT * FROM source_table) LOOP
    BEGIN
      INSERT INTO target_table VALUES (rec.id, rec.amount);
      v_count := v_count + 1;
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE = -1438 THEN
          INSERT INTO overflow_log (id, amount, error_date)
          VALUES (rec.id, rec.amount, SYSDATE);
        ELSE
          RAISE;
        END IF;
    END;
  END LOOP;
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('Inserted: ' || v_count || ' records');
END;
/

Prevention Strategies

1. Design with Growth in Mind

-- Common precision recommendations
-- IDs:        NUMBER(12) or larger for growth
-- Currency:   NUMBER(19,4) for most currencies
-- Quantities: NUMBER(10) for typical quantities
-- Rates:      NUMBER(10,6) for exchange rates
-- Percentages: NUMBER(5,2) for 0-100%

-- Example: Future-proof schema
CREATE TABLE orders (
  order_id NUMBER(12),
  quantity NUMBER(10),
  unit_price NUMBER(19,4),
  total_amount NUMBER(19,4),
  tax_rate NUMBER(5,4)
);

2. Add Check Constraints

-- Prevent application-level errors
ALTER TABLE products ADD CONSTRAINT chk_price_range
CHECK (price >= 0 AND price <= 99999.99);

-- With custom error message (via trigger)
CREATE OR REPLACE TRIGGER trg_products_price
BEFORE INSERT OR UPDATE ON products
FOR EACH ROW
BEGIN
  IF :NEW.price > 99999.99 THEN
    RAISE_APPLICATION_ERROR(-20001,
      'Price ' || :NEW.price || ' exceeds maximum allowed value of 99999.99');
  END IF;
END;
/

3. Validate Before Insert

-- Application-level validation function
CREATE OR REPLACE FUNCTION validate_number(
  p_value IN NUMBER,
  p_precision IN NUMBER,
  p_scale IN NUMBER DEFAULT 0
) RETURN NUMBER IS
  v_max_integer_digits NUMBER := p_precision - p_scale;
  v_integer_part NUMBER := TRUNC(ABS(p_value));
BEGIN
  IF LENGTH(v_integer_part) > v_max_integer_digits THEN
    RETURN NULL;  -- Or raise error
  END IF;
  RETURN ROUND(p_value, p_scale);
END;
/

-- Usage
INSERT INTO products (id, price)
VALUES (1, validate_number(1234.567, 7, 2));

Common Scenarios

Scenario: Currency Conversion

-- Original: USD amounts fit in NUMBER(10,2)
-- After conversion to KRW (Korean Won), values are ~1000x larger

-- Before conversion, check target capacity:
SELECT
  MAX(amount_usd * 1300) as max_krw_amount,
  LENGTH(TRUNC(MAX(amount_usd * 1300))) as integer_digits_needed
FROM transactions;

-- Resize column before conversion
ALTER TABLE transactions MODIFY amount NUMBER(15,2);

Scenario: Data Migration

-- Check source data before migration
SELECT
  'Source' as system,
  MAX(amount) as max_amount,
  MIN(amount) as min_amount,
  MAX(LENGTH(TRUNC(ABS(amount)))) as max_integer_digits
FROM source_system.transactions
UNION ALL
SELECT
  'Target Capacity' as system,
  POWER(10, data_precision - data_scale) - 1 as max_amount,
  -(POWER(10, data_precision - data_scale) - 1) as min_amount,
  data_precision - data_scale as max_integer_digits
FROM target_system.all_tab_columns
WHERE column_name = 'AMOUNT';

Quick Reference

NUMBER Definition	Max Integer Digits	Max Value	Common Use
NUMBER(5,2)	3	999.99	Small prices
NUMBER(8,2)	6	999999.99	Standard currency
NUMBER(12,2)	10	9999999999.99	Large amounts
NUMBER(19,4)	15	~10^15	Financial systems
NUMBER(10)	10	9999999999	IDs, counts
NUMBER	38	~10^38	Unlimited

Related Errors

• ORA-01722 - Invalid number
• ORA-06502 - PL/SQL numeric or value error
• ORA-12899 - Value too large for column (VARCHAR2)
• ORA-01476 - Divisor is equal to zero

Summary

1. Understand NUMBER(p,s) - precision minus scale equals integer digits
2. Check column definition before inserting calculated values
3. Design with growth - use generous precision for financial data
4. Validate source data before migration
5. Use NUMBER without precision when size limits aren’t needed
6. ALTER TABLE MODIFY can increase precision without data loss