DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01481 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01481: Invalid Number Format Model — Causes, Fixes & Prevention

ORA-01481 is thrown by Oracle when an invalid format model string is passed to numeric conversion functions such as TO_CHAR() or TO_NUMBER(). Oracle strictly enforces a set of recognized format elements (e.g., 9, 0, $, MI, PR, S, EEEE), and any unrecognized or improperly combined element immediately triggers this error. It is especially common during cross-database migrations or when developers confuse date format models with number format models.

Top 3 Causes

1. Using Unrecognized Format Characters

The most frequent cause is supplying format strings that Oracle simply does not recognize — such as MySQL-style #,###.## or accidentally passing a date format like YYYY-MM-DD to a numeric conversion.

-- WRONG: MySQL-style format (ORA-01481)
SELECT TO_CHAR(9876543.21, '#,###.##') FROM DUAL;

-- WRONG: Date format used for number (ORA-01481)
SELECT TO_CHAR(20240101, 'YYYY-MM-DD') FROM DUAL;

-- CORRECT: Oracle number format elements
SELECT TO_CHAR(9876543.21, '9,999,999.99') FROM DUAL;
-- Result: 9,876,543.21

SELECT TO_CHAR(9876543.21, 'FM9,999,999.99') FROM DUAL;
-- Result: 9,876,543.21  (no leading/trailing spaces)
Enter fullscreen mode Exit fullscreen mode

2. Violating Format Element Combination Rules

Oracle has strict positional rules for sign-related format elements. S (sign) must appear at the very beginning or end of the format string. MI (minus indicator) must appear only at the end. PR (angle-bracket negative display) cannot be combined with S.

-- WRONG: S in the middle (ORA-01481)
SELECT TO_CHAR(-1234.56, '9,999S.99') FROM DUAL;

-- WRONG: PR and S together (ORA-01481)
SELECT TO_CHAR(-1234.56, 'S9,999.99PR') FROM DUAL;

-- CORRECT: S at the front
SELECT TO_CHAR(-1234.56, 'S9,999.99') FROM DUAL;
-- Result: -1,234.56

-- CORRECT: MI at the end
SELECT TO_CHAR(-1234.56, '9,999.99MI') FROM DUAL;
-- Result: 1,234.56-

-- CORRECT: PR at the end only
SELECT TO_CHAR(-1234.56, '9,999.99PR') FROM DUAL;
-- Result: <1,234.56>
Enter fullscreen mode Exit fullscreen mode

3. Dynamically Generated Format Strings at Runtime

In stored procedures or application code, format strings are sometimes built dynamically from user input or configuration files. If the runtime value doesn't conform to Oracle's format model rules, ORA-01481 is raised — and it can be difficult to reproduce in a non-production environment.

-- Safe wrapper function to handle dynamic format strings
CREATE OR REPLACE FUNCTION safe_to_char(
    p_number IN NUMBER,
    p_format IN VARCHAR2
) RETURN VARCHAR2 IS
    v_result VARCHAR2(200);
BEGIN
    IF p_format IS NULL THEN
        RETURN TO_CHAR(p_number);
    END IF;
    RETURN TO_CHAR(p_number, p_format);
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE = -1481 THEN
            -- Fallback to default conversion on bad format model
            RETURN TO_CHAR(p_number);
        ELSE
            RAISE;
        END IF;
END safe_to_char;
/

-- Usage
SELECT safe_to_char(1234567.89, '9,999,999.99') FROM DUAL; -- Normal
SELECT safe_to_char(1234567.89, 'BAD_FORMAT')   FROM DUAL; -- Fallback
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Always verify your format string against the official Oracle number format element list before using it in production. Here is a quick reference of the most commonly used valid elements:

-- Common valid Oracle number format elements
SELECT TO_CHAR(1234567.89, '9,999,999.99')   FROM DUAL; -- Digit placeholder
SELECT TO_CHAR(1234567.89, '0,000,000.00')   FROM DUAL; -- Zero-padded
SELECT TO_CHAR(1234567.89, '$9,999,999.99')  FROM DUAL; -- Dollar sign
SELECT TO_CHAR(1234567.89, 'L9,999,999.99')  FROM DUAL; -- Local currency
SELECT TO_CHAR(0.00012345, '9.9999EEEE')     FROM DUAL; -- Scientific notation
SELECT TO_NUMBER('1,234.56', '9,999.99')     FROM DUAL; -- String to number
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Standardize format strings in a shared constants package.
Define all numeric format strings in a single PL/SQL package as constants, so every developer uses the same validated strings rather than writing ad-hoc format models.

CREATE OR REPLACE PACKAGE fmt_constants AS
    c_number_std    CONSTANT VARCHAR2(30) := 'FM9,999,999,999.99';
    c_currency_usd  CONSTANT VARCHAR2(30) := 'FML9,999,999,999.99';
    c_scientific    CONSTANT VARCHAR2(30) := 'FM9.9999EEEE';
END fmt_constants;
/
Enter fullscreen mode Exit fullscreen mode

2. Always test new format models against DUAL first.
Before embedding a new format string in production code, run a quick sanity check on DUAL. Add format string validation to your code review checklist, especially when onboarding developers coming from MySQL or SQL Server backgrounds.

Related Errors

  • ORA-01722 — invalid number: The format model is valid, but the data itself cannot be converted to a number.
  • ORA-01821 — date format not recognized: The date equivalent of ORA-01481, triggered by invalid format models in TO_DATE or TO_CHAR for dates.
  • ORA-06502 — PL/SQL: numeric or value error: Can cascade alongside ORA-01481 when conversion results are assigned to PL/SQL variables with incompatible types.

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