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 22.214.171.124.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.