ORA-01722: Invalid Number

ORA-01722: Invalid Number

Error Description

ORA-01722 occurs when Oracle attempts to convert a character string to a number, but the string contains invalid numeric data. This is one of the most common Oracle errors encountered in applications and data loading operations.

Complete Error Message

ORA-01722: invalid number

Severity Level

🟡 MEDIUM - Application error that prevents query execution but doesn’t affect database operations.

Common Causes

1. Data Type Mismatches

• Comparing VARCHAR2 fields containing non-numeric data to numbers
• Implicit conversion failures in WHERE clauses
• Invalid data in numeric columns

2. Invalid Character Data

• Leading/trailing spaces in numeric strings
• Special characters (commas, currency symbols, etc.)
• Non-printable characters in data

3. Application Logic Issues

• Incorrect parameter binding
• User input validation failures
• Data import/export problems

4. SQL Query Issues

• Incorrect use of functions expecting numeric input
• ORDER BY clauses with mixed data types
• Mathematical operations on invalid data

Common Scenarios and Solutions

Scenario 1: WHERE Clause Comparisons

Problem Example

-- This will fail if EMPLOYEE_ID contains non-numeric data
SELECT * FROM employees
WHERE employee_id > 100;

-- If EMPLOYEE_ID is VARCHAR2 and contains 'N/A', 'TBD', etc.

Solutions

-- Solution 1: Use proper validation
SELECT * FROM employees
WHERE REGEXP_LIKE(employee_id, '^[0-9]+$')
  AND TO_NUMBER(employee_id) > 100;

-- Solution 2: Use exception handling
SELECT * FROM employees
WHERE CASE
        WHEN REGEXP_LIKE(employee_id, '^[0-9]+(\.[0-9]+)?$')
        THEN TO_NUMBER(employee_id)
        ELSE NULL
      END > 100;

-- Solution 3: Use safe conversion function
CREATE OR REPLACE FUNCTION safe_to_number(p_string VARCHAR2)
RETURN NUMBER IS
BEGIN
    RETURN TO_NUMBER(p_string);
EXCEPTION
    WHEN VALUE_ERROR THEN
        RETURN NULL;
END;
/

SELECT * FROM employees
WHERE safe_to_number(employee_id) > 100;

Scenario 2: Data with Special Characters

Problem Example

-- Salary field contains values like '$50,000' or '50,000.00'
SELECT * FROM employees
WHERE salary > 50000;

Solutions

-- Remove currency symbols and commas
SELECT * FROM employees
WHERE TO_NUMBER(TRANSLATE(salary, '$,', '  ')) > 50000;

-- More comprehensive cleaning
SELECT * FROM employees
WHERE TO_NUMBER(
    REGEXP_REPLACE(
        REGEXP_REPLACE(salary, '[^0-9.-]', ''),
        '^\.', '0.'
    )
) > 50000;

-- Handle multiple formats
SELECT employee_id,
       CASE
         WHEN REGEXP_LIKE(salary, '^\$?[0-9,]+(\.[0-9]{2})?$') THEN
           TO_NUMBER(REPLACE(REPLACE(salary, '$', ''), ',', ''))
         ELSE NULL
       END as clean_salary
FROM employees;

Scenario 3: ORDER BY Issues

Problem Example

-- Mixed data types in ORDER BY
SELECT product_id, product_code
FROM products
ORDER BY product_code;
-- Fails if product_code has both numbers and text

Solutions

-- Solution 1: Separate numeric and non-numeric
SELECT product_id, product_code
FROM products
ORDER BY
  CASE WHEN REGEXP_LIKE(product_code, '^[0-9]+$') THEN 0 ELSE 1 END,
  CASE WHEN REGEXP_LIKE(product_code, '^[0-9]+$') THEN TO_NUMBER(product_code) ELSE NULL END,
  product_code;

-- Solution 2: Natural sort (alphanumeric)
SELECT product_id, product_code
FROM products
ORDER BY NLSSORT(product_code, 'NLS_SORT=BINARY_AI');

-- Solution 3: Pad numbers for proper sorting
SELECT product_id, product_code
FROM products
ORDER BY LPAD(product_code, 20, '0');

Diagnostic Queries

1. Find Invalid Numeric Data

-- Check for non-numeric data in a supposedly numeric column
SELECT column_name, COUNT(*) as invalid_count
FROM (
  SELECT your_column as column_name
  FROM your_table
  WHERE NOT REGEXP_LIKE(your_column, '^[+-]?([0-9]*[.])?[0-9]+$')
    AND your_column IS NOT NULL
    AND TRIM(your_column) != ''
)
GROUP BY column_name;

-- Find specific invalid values
SELECT DISTINCT your_column,
       ASCII(SUBSTR(your_column, 1, 1)) as first_char_ascii,
       LENGTH(your_column) as length,
       DUMP(your_column) as hex_dump
FROM your_table
WHERE NOT REGEXP_LIKE(TRIM(your_column), '^[+-]?([0-9]*[.])?[0-9]+$')
  AND your_column IS NOT NULL
  AND TRIM(your_column) != '';

2. Test Numeric Conversion Safety

-- Create a test function to validate conversion
CREATE OR REPLACE FUNCTION is_number(p_string VARCHAR2)
RETURN VARCHAR2 IS
    v_number NUMBER;
BEGIN
    v_number := TO_NUMBER(p_string);
    RETURN 'VALID';
EXCEPTION
    WHEN VALUE_ERROR THEN
        RETURN 'INVALID';
END;
/

-- Test your data
SELECT your_column,
       is_number(your_column) as validity,
       CASE
         WHEN is_number(your_column) = 'VALID' THEN TO_NUMBER(your_column)
         ELSE NULL
       END as converted_value
FROM your_table
WHERE your_column IS NOT NULL;

3. Analyze Character Distribution

-- Find unusual characters in your data
SELECT
    SUBSTR(column_name, pos, 1) as character,
    ASCII(SUBSTR(column_name, pos, 1)) as ascii_code,
    COUNT(*) as occurrence_count
FROM (
    SELECT column_name, LEVEL as pos
    FROM your_table,
         (SELECT LEVEL FROM dual CONNECT BY LEVEL <= (
             SELECT MAX(LENGTH(column_name)) FROM your_table
         ))
    WHERE LEVEL <= LENGTH(column_name)
      AND column_name IS NOT NULL
)
GROUP BY SUBSTR(column_name, pos, 1), ASCII(SUBSTR(column_name, pos, 1))
ORDER BY ascii_code;

Data Cleaning Solutions

1. Clean Invalid Data in Bulk

-- Update table to clean numeric fields
UPDATE your_table
SET numeric_column =
  CASE
    WHEN REGEXP_LIKE(TRIM(numeric_column), '^[+-]?([0-9]*[.])?[0-9]+$')
    THEN TRIM(numeric_column)
    ELSE NULL
  END
WHERE numeric_column IS NOT NULL;

-- Remove non-numeric characters
UPDATE your_table
SET amount_column = REGEXP_REPLACE(amount_column, '[^0-9.-]', '')
WHERE amount_column IS NOT NULL
  AND NOT REGEXP_LIKE(amount_column, '^[+-]?([0-9]*[.])?[0-9]+$');

2. Create Data Validation Rules

-- Add check constraint to prevent invalid data
ALTER TABLE your_table
ADD CONSTRAINT check_numeric_column
CHECK (REGEXP_LIKE(numeric_column, '^[+-]?([0-9]*[.])?[0-9]+$')
       OR numeric_column IS NULL);

-- Create a validation trigger
CREATE OR REPLACE TRIGGER validate_numeric_data
  BEFORE INSERT OR UPDATE ON your_table
  FOR EACH ROW
BEGIN
  IF :NEW.numeric_column IS NOT NULL THEN
    -- Try to convert to validate
    DECLARE
      v_test NUMBER;
    BEGIN
      v_test := TO_NUMBER(:NEW.numeric_column);
    EXCEPTION
      WHEN VALUE_ERROR THEN
        RAISE_APPLICATION_ERROR(-20001,
          'Invalid numeric value: ' || :NEW.numeric_column);
    END;
  END IF;
END;
/

Prevention Strategies

1. Input Validation in Applications

-- Create a validation package
CREATE OR REPLACE PACKAGE data_validator AS
  FUNCTION is_valid_number(p_input VARCHAR2) RETURN BOOLEAN;
  FUNCTION safe_to_number(p_input VARCHAR2) RETURN NUMBER;
  FUNCTION clean_numeric_string(p_input VARCHAR2) RETURN VARCHAR2;
END;
/

CREATE OR REPLACE PACKAGE BODY data_validator AS
  FUNCTION is_valid_number(p_input VARCHAR2) RETURN BOOLEAN IS
    v_number NUMBER;
  BEGIN
    IF p_input IS NULL OR TRIM(p_input) = '' THEN
      RETURN TRUE; -- Allow NULL values
    END IF;

    v_number := TO_NUMBER(TRIM(p_input));
    RETURN TRUE;
  EXCEPTION
    WHEN VALUE_ERROR THEN
      RETURN FALSE;
  END;

  FUNCTION safe_to_number(p_input VARCHAR2) RETURN NUMBER IS
  BEGIN
    RETURN TO_NUMBER(TRIM(p_input));
  EXCEPTION
    WHEN VALUE_ERROR THEN
      RETURN NULL;
  END;

  FUNCTION clean_numeric_string(p_input VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    -- Remove common non-numeric characters
    RETURN REGEXP_REPLACE(
      REPLACE(REPLACE(REPLACE(p_input, ',', ''), '$', ''), '%', ''),
      '[^0-9.-]', ''
    );
  END;
END;
/

2. Use Proper Data Types

-- Use NUMBER columns instead of VARCHAR2 for numeric data
CREATE TABLE employees_correct (
  employee_id    NUMBER PRIMARY KEY,
  employee_name  VARCHAR2(100),
  salary         NUMBER(10,2),
  hire_date      DATE,
  commission_pct NUMBER(3,2)
);

-- If you must use VARCHAR2, add validation
CREATE TABLE temp_data (
  id           NUMBER,
  numeric_text VARCHAR2(50),
  CONSTRAINT check_numeric_text
    CHECK (numeric_text IS NULL OR
           REGEXP_LIKE(TRIM(numeric_text), '^[+-]?([0-9]*[.])?[0-9]+$'))
);

Advanced Solutions

1. Custom Conversion Functions

-- Flexible number conversion with format handling
CREATE OR REPLACE FUNCTION flexible_to_number(
  p_input VARCHAR2,
  p_decimal_separator VARCHAR2 DEFAULT '.',
  p_thousand_separator VARCHAR2 DEFAULT ','
) RETURN NUMBER IS
  v_cleaned VARCHAR2(4000);
  v_result NUMBER;
BEGIN
  IF p_input IS NULL THEN
    RETURN NULL;
  END IF;

  -- Clean the input
  v_cleaned := TRIM(p_input);

  -- Remove thousand separators
  v_cleaned := REPLACE(v_cleaned, p_thousand_separator, '');

  -- Handle different decimal separators
  IF p_decimal_separator != '.' THEN
    v_cleaned := REPLACE(v_cleaned, p_decimal_separator, '.');
  END IF;

  -- Remove currency symbols and other characters
  v_cleaned := REGEXP_REPLACE(v_cleaned, '[^0-9.-]', '');

  -- Convert to number
  v_result := TO_NUMBER(v_cleaned);

  RETURN v_result;
EXCEPTION
  WHEN VALUE_ERROR THEN
    RETURN NULL;
END;
/

-- Usage examples
SELECT
  flexible_to_number('$1,234.56') as usd_amount,
  flexible_to_number('1.234,56', ',', '.') as eur_amount,
  flexible_to_number('₹1,23,456.78') as inr_amount
FROM dual;

2. Error Logging and Recovery

-- Create error logging table
CREATE TABLE number_conversion_errors (
  error_id     NUMBER GENERATED ALWAYS AS IDENTITY,
  table_name   VARCHAR2(128),
  column_name  VARCHAR2(128),
  row_id       ROWID,
  invalid_value VARCHAR2(4000),
  error_date   DATE DEFAULT SYSDATE,
  resolved     CHAR(1) DEFAULT 'N'
);

-- Procedure to safely convert and log errors
CREATE OR REPLACE PROCEDURE safe_update_numeric_column(
  p_table_name VARCHAR2,
  p_column_name VARCHAR2,
  p_where_clause VARCHAR2 DEFAULT NULL
) IS
  v_sql VARCHAR2(32000);
  v_count NUMBER := 0;
BEGIN
  -- Log conversion errors first
  v_sql := 'INSERT INTO number_conversion_errors
            (table_name, column_name, row_id, invalid_value)
            SELECT ''' || p_table_name || ''',
                   ''' || p_column_name || ''',
                   ROWID,
                   ' || p_column_name || '
            FROM ' || p_table_name || '
            WHERE ' || p_column_name || ' IS NOT NULL
              AND NOT REGEXP_LIKE(TRIM(' || p_column_name || '), ''^[+-]?([0-9]*[.])?[0-9]+$'')';

  IF p_where_clause IS NOT NULL THEN
    v_sql := v_sql || ' AND ' || p_where_clause;
  END IF;

  EXECUTE IMMEDIATE v_sql;
  v_count := SQL%ROWCOUNT;

  DBMS_OUTPUT.PUT_LINE('Logged ' || v_count || ' conversion errors');

  -- Update valid values
  v_sql := 'UPDATE ' || p_table_name || '
            SET ' || p_column_name || ' =
              CASE WHEN REGEXP_LIKE(TRIM(' || p_column_name || '), ''^[+-]?([0-9]*[.])?[0-9]+$'')
                   THEN TRIM(' || p_column_name || ')
                   ELSE NULL
              END
            WHERE ' || p_column_name || ' IS NOT NULL';

  IF p_where_clause IS NOT NULL THEN
    v_sql := v_sql || ' AND ' || p_where_clause;
  END IF;

  EXECUTE IMMEDIATE v_sql;
  v_count := SQL%ROWCOUNT;

  DBMS_OUTPUT.PUT_LINE('Updated ' || v_count || ' rows');
  COMMIT;
END;
/

Related Oracle Errors

• ORA-01426: Numeric Overflow - Number too large
• ORA-06502: PL/SQL Numeric or Value Error - PL/SQL conversion error
• ORA-01858: Non-numeric Character Found - Date conversion issues

Best Practices

1. Always validate input data before database operations
2. Use appropriate data types from the beginning
3. Implement data cleaning procedures for imported data
4. Add database constraints to prevent invalid data entry
5. Use safe conversion functions in application code
6. Log and monitor conversion errors for data quality improvement

Quick Troubleshooting Checklist

1. ✅ Identify the problematic query or operation
2. ✅ Check for non-numeric characters in supposedly numeric fields
3. ✅ Validate data using REGEXP_LIKE patterns
4. ✅ Use safe conversion functions with exception handling
5. ✅ Clean data using string manipulation functions
6. ✅ Add proper validation constraints to prevent future issues

This error is often a symptom of data quality issues and can be prevented with proper input validation and data type design.