:

ORA-01722: invalid number

ora-01722 Invalid Number

There are quite a few reasons why you might be hitting the ORA-01722 error message. First of all I’d like to go through the standard ways and then show you an error that I came across while upgrading my database from 10g to 11g.

The issue primarily occurs when you are attempting to convert a character string into a number. Here is a list of items to check:

  • Numbers can only contain 0 through 9
  • May contain 1 decimal point character (could be a . or , depending on the character set)
  • There might be a + or – sign at the beginning or end of the string
  • An E or e is permitted if a floating point number in scientific notation

Here are some examples of when you might encounter the ORA-01722 error message:

SQL> select to_number('abc') from dual;
select to_number('abc') from dual
 *
ERROR at line 1:
ORA-01722: invalid number

SQL> select to_number('12,345') from dual;
select to_number('12,345') from dual
 *
ERROR at line 1:
ORA-01722: invalid number

By default the database does not accept the use of a comma when using the TO_NUMBER function, as above, but you can get around this by explicitly telling the database what format you have used:

SQL> select to_number('12,345','99,999') from dual;
TO_NUMBER('12,345','99,999')
----------------------------
12345

The example above where I have specified the ‘99,999’ format is a better way to do it because you don’t rely on any default format in the database – defaults can always differ. This way you are explicitly telling the database what format the number is in so it won’t get it wrong or error, regardless of the default of the database.

How to Resolve the ORA 01722 invalid number error

  • Check all numbers are valid in your statement – break down the statement into simpler parts and run individually if necessary to find the cause of the problem
  • Ensure that you have your values in the correct order within your statement when compared to the column order in the table
  • Use INSERT INTO table_name (Col1,Col2,Col3) VALUES (Val1,Val2,Val3); so that you specify which columns you are inserting into. Using this method you can decide into which columns you will be inserting data
  • If you are using a sub query you should break it all down and check it is all working, as above
  • Ensure that you are using the correct format mask

The Problem

While upgrading my Oracle database from 10.2.0.4 to 11g (11.2.0.3) I received an ORA-01722 error message while running in the catupgrd.sql script.

SELECT TO_NUMBER('MUST_BE_11_2_0_3')
FROM v$instance
*
ERROR at line 1: ORA-01722: invalid number

The Cause

In my haste to upgrade the DB, I had forgotten to run in the pre upgrade utility into the 10g DB. This script, called utlu112i.sql and found in the RDBMS\ADMIN folder of the ORACLE_HOME, should be run in prior to any upgrade work. Among other things, it checks that there is adequate space  available, what invalid objects there are and which parameters need to be changed with the upgrade.

The Solution

You need to restart the DB using a 10g service and run in the above script to prevent getting the ORA-01722: invalid number error.

Like it, share it...

Category: 11g


Related Posts

Leave a Reply

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