FIX: Error Message “ORA-01722: invalid number”

Published by admin on October 8, 2015

The error message “ORA-01722: invalid number” generally arises,

• While, you are attempting to convert a string into a number and it cannot be converted into valid number as it includes the digits through “0” to “9”.
• A numeric column may be the object of an “Update” or “Insert” statement or it may appears as a part of a “Where” clause.
• It might also be appearing in the statement, if there is no numeric columns appear explicitly.
• You try to replicate the table to an Oracle server subscriber and you select “Transactional publication” as the publication type.
• You create a table that has “Varchar (max)” data type column in Microsoft SQL Server 2008 R2 or 2008.

Here are some of the examples of the Oracle error ORA-01722.

1. SQL> select ‘abc’ – 124 from dual;

ERROR:

ORA-01722: invalid number

No rows selected

You encounter this error when you are using arithmetic function on Strings.

1. SQL> select to_number(‘3434,3333.000’) from dual:

ERROR:

ORA-01722: invalid number

No rows selected

The above error occurs because it has found a character. It usually happens when a default format for to_number does not contain a comma.

How to fix this error code?

When doing Select, rather than Update or Insert

In this case there is an implied change happening between predicate in the Where clause. Check for a numeric column being compared to a character column. While using the to_number, ensures that the format mask fits in all possible character strings in the table. Make sure that all rows are being executed in the Where clause if you are not sure that column contain both character strings and valid numbers. If there is no where then just “Select*From Table” and you got the error then set proper locate in your “exeSQL” method.

When doing an INSERT INTO….VALUES (…)

If all the numbers appear to be valid then you may have perhaps your columns out of order and an item in the Values clause is being inserted into a Number column instead of the Varchar2 column. It usually occurs when a table has columns removed or added. In order to fix this change the data or identify the row that has the non-numeric string or you can also add something to the sub query.

If the above mentioned methods does not help you to fix this error then you can use Oracle File Repair Tool. It is a professional tool that is capable in fixing any kind of Oracle database error.  This application is  saved the recovered data as Transact-SQL script and also very user friendly.

Steps to fix ORA-01722 error

Step 1: Search Initial screen of Stellar Phoenix Oracle Recovery with a pop-up window showing options to select or search corrupt Oracle databases in your computer.

Step 2: Click Scan File to initiate the scan process after selecting the oracle database. The recoverable database objects get listed in left-side pane.

Step 3: Click an object to see its preview.

Step 4: : Click Start Repair in the icon bar to start the repair process. A pop-up window is displayed which show the steps needed to perform further. Click next and continue.

Step 5: Give the user name, password and path of the blank database where you want to save the repaired database objects.

Step 6: Repairing and restoring various database objects after establishing a connection with blank oracle database.

625 Total Views 3 Views Today