ORA-01481 Invalid number format model

My code worked perfectly fine in Oracle 9i, but after our upgrade to 11g all of a sudden it was failing with ORA-01481 “Invalid number format model”.

The cause? A mixed-up number format model – I’d accidentally mixed locale-specific (“.“) and locale-safe (“G“) codes in the number format. I don’t know if this is due to a bug in 9i or an enhancement in 11g.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0
- 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application
Testing options
SQL> select to_char(12345.678,'99G990.0') from dual;
select to_char(12345.678,'99G990.0') from dual
*
ERROR at line 1:
ORA-01481: invalid number format model
SQL> select to_char(12345.678,'99,990.0') from dual;
TO_CHAR(1
---------
12,345.7
SQL> select to_char(12345.678,'99G990D0') from dual;
TO_CHAR(1
---------
12,345.7

“Action: Consult the manual… in retrospect, not of much help in this instance, unfortunately – however, these sorts of problems may usually be worked out by experimentation.

One insert or multiple inserts?
Two sides of the same fence?

Comments

  1. Hi Jeff –

    Appreciate your post as this came up in a practise exam for 11g / 1Z0-051.

    The question was looking for valid formats within TO_CHAR that will display $1890.55 .

    One of the possible answers was:
    SELECT TO_CHAR ( 1890.55, ‘$99,999D99’) FROM DUAL;

    Until i tested it, i believed it would work – but as you state above these types of combinations create errors.

    I have not found any documentation yet in Oracle that states that ‘D’ cannot be used with and ‘G’ cannot be used with , but the inclusion of this question in a practice test perhaps implies that this is an ‘official’ restriction.

    As I am a newbie, can you please explain to me the difference between locale-specific and locale-safe?

    Thanks, Paul

    • Hi Paul,

      People of different languages and cultures write down numbers and dates in different ways – for example, in America, UK and Australia a decimal point (.) is used to separate the integer portion of a number from the decimal portion, and a comma (,) is used to group digits of the integer portion into triplets for ease of reading; whereas in Europe, the opposite applies.

      So, to format a number for people in US/Aus, I might use the following:

      TO_CHAR(1234.5, ‘9,999,990.00’)
      1,234.50

      whereas in Europe, I might use this instead:

      TO_CHAR(1234.5, ‘9.999.990,00’)
      1.234,50

      This, however, is not good practice, because it locks down my application to one scheme, and if I want to make the application useful for people with a different convention for writing down numbers, I’d have to make significant changes to my code.

      Instead, Oracle allows me to use just one number format throughout my application, and then allows the user to specify their locale; and Oracle will, at run time, determine which format to use for each user – and as a programmer, I don’t have to know what their locale is.

      The format specifier for the decimal separator is D, grouping character is G, and currency symbol is L. This allows me to specify the format as follows:

      TO_CHAR(1234.5, ‘L9G999G990D00’)
      $1,234.50

      The above output is what I get in my locale. Specifying all number formats in this method is considered best practice.

      So, the “.” and “,” are what I’d consider to be “locale-specific” – i.e. they are special to some locales but not to others, so they’re not “safe” for use in an application that may be used by people from different locales. “D”, “G” and “L” are then “locale-safe” – they will cause numbers to be formatted correctly no matter what locale the user uses.

      More info:
      http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/functions199.htm#i79330

Leave a Reply

Your email address will not be published / Required fields are marked *