ORA-01722
Пробное преобразование символьной строки в числовую провалилось, потому что символьная строка не была правильной числовой литерой. Только числовые поля или символьные поля содержащие числовые данные могут быть использованы в арифметических функциях или выражениях. Только числовые поля могут быть добавлены или вычтены из данных.
Действие:
Проверьте символьные строки в функции или в выражении; убедитесь в том, что они содержат только числа, знаки, десятичные точки, и символ «E» или «e», затем повторите операцию.
How to Resolve ORA-01722: invalid number
ORA-01722 means that the arithmetic operation in the statement failed to calculate because one of operands cannot be converted to a valid number implicitly.
Let’s see some error patterns.
A. Type Conversion
We created a simple table containing only one column with NUMBER type.
SQL> create table salaries (salary number);
When we tried to insert a row into the table containing NUMBER column, we got ORA-01722.
SQL> insert into salaries (salary) values ( ‘200,000’ );
insert into salaries (salary) values (‘200,000’)
*
ERROR at line 1:
ORA-01722: invalid number
1. Make it Easier to Convert
This is because the value '200,000' of column SALARY cannot be converted into a valid number. We should make it easier to be converted, so we remove the comma separator.
SQL> insert into salaries (salary) values ( ‘200000’ );
Unsurprisingly, string '200000' can be converted to a number and inserted into the table.
2. Use VARCHAR2
You cannot always depend on unpredictable implicit conversion, sometimes, you should change the column from NUMBER into VARCHAR2 . For example, phone numbers of customers may not be perfectly formatted as NUMBER .
SQL> insert into customers (cust_id, phone_number) values (100, ‘0254 539 2413’);
insert into customers (cust_id, phone_number) values (100, ‘0254 539 2413’)
*
ERROR at line 1:
ORA-01722: invalid number
As we can see, the value of phone number cannot be converted into a NUMBER , so we should use VARCHAR2 instead.
SQL> alter table customers modify phone_number varchar2 (13);
SQL> insert into customers (cust_id, phone_number) values (100, ‘0254 539 2413’);
B. String Concatenation
Using a plus (add) sign can not concatenate strings.
SQL> set heading off;
SQL> select ‘Today is ‘ + sysdate from dual;
select ‘Today is ‘ + sysdate from dual
*
ERROR at line 1:
ORA-01722: invalid number
SQL parser thought your statement was trying to do an arithmetic operation, but the string ‘Today is ‘ failed to be converted into number. The correct way to concatenate strings is to use || , not a plus sign + .
SQL> select ‘Today is ‘ || sysdate from dual;
Today is 26-DEC-19
As a result, the output is perfect in concatenating a string and a date without ORA-01722.
A very similar error that you might see in your PL/SQL codes is ORA-06502: PL/SQL: numeric or value error, which is also related to conversion issues of numeric values.
SQL error "ORA-01722: invalid number"
An ORA-01722 error occurs when an attempt is made to convert a character string into a number, and the string cannot be converted into a number.
Without seeing your table definition, it looks like you’re trying to convert the numeric sequence at the end of your values list to a number, and the spaces that delimit it are throwing this error. But based on the information you’ve given us, it could be happening on any field (other than the first one).
Suppose tel_number is defined as NUMBER — then the blank spaces in this provided value cannot be converted into a number:
The above gives you a
Here’s one way to solve it. Remove non-numeric characters then cast it as a number.
Well it also can be :
where for concatenation in oracle is used the operator || not + .
In this case you get : ORA-01722: invalid number .
This is because:
You executed an SQL statement that tried to convert a string to a number, but it was unsuccessful.
As explained in:
To resolve this error:
Only numeric fields or character fields that contain numeric values can be used in arithmetic operations. Make sure that all expressions evaluate to numbers.
As this error comes when you are trying to insert non-numeric value into a numeric column in db it seems that your last field might be numeric and you are trying to send it as a string in database. check your last value.
Oracle does automatic String2number conversion, for String column values! However, for the textual comparisons in SQL, the input must be delimited as a String explicitly: The opposite conversion number2String is not performed automatically, not on the SQL-query level.
I had this query:
select max(acc_num) from ACCOUNTS where acc_num between 1001000 and 1001999;
That one presented a problem: Error: ORA-01722: invalid number
I have just surrounded the "numerical" values, to make them ‘Strings’, just making them explicitly delimited:
select max(acc_num) from ACCOUNTS where acc_num between ‘1001000’ and ‘1001999’;
. and voilà: It returns the expected result.
edit: And indeed: the col acc_num in my table is defined as String . Although not numerical, the invalid number was reported. And the explicit delimiting of the string-numbers resolved the problem.
On the other hand, Oracle can treat Strings as numbers. So the numerical operations/functions can be applied on the Strings, and these queries work:
select max(string_column) from TABLE;
select string_column from TABLE where string_column between ‘2’ and ‘z’;
select string_column from TABLE where string_column > ‘1’;
select string_column from TABLE where string_column <= ‘b’;
In my case the conversion error was in functional based index, that I had created for the table.
The data being inserted was OK. It took me a while to figure out that the actual error came from the buggy index.
Would be nice, if Oracle could have gave more precise error message in this case.
If you do an insert into. select * from. statement, it’s easy to get the ‘Invalid Number’ error as well.
Let’s say you have a table called FUND_ACCOUNT that has two columns:
And let’s say that you want to modify the OFFICE_ID to be numeric, but that there are existing rows in the table, and even worse, some of those rows have an OFFICE_ID value of ‘ ‘ (blank). In Oracle, you can’t modify the datatype of a column if the table has data, and it requires a little trickery to convert a ‘ ‘ to a 0. So here’s how to do it:
- Create a duplicate table: CREATE TABLE FUND_ACCOUNT2 AS SELECT * FROM FUND_ACCOUNT;
- Delete all the rows from the original table: DELETE FROM FUND_ACCOUNT;
Once there’s no data in the original table, alter the data type of its OFFICE_ID column: ALTER TABLE FUND_ACCOUNT MODIFY (OFFICE_ID number);
But then here’s the tricky part. Because some rows contain blank OFFICE_ID values, if you do a simple INSERT INTO FUND_ACCOUNT SELECT * FROM FUND_ACCOUNT2 , you’ll get the «ORA-01722 Invalid Number» error. In order to convert the ‘ ‘ (blank) OFFICE_IDs into 0’s, your insert statement will have to look like this:
INSERT INTO FUND_ACCOUNT (AID_YEAR, OFFICE_ID) SELECT AID_YEAR, decode(OFFICE_ID,’ ‘,0,OFFICE_ID) FROM FUND_ACCOUNT2;
4 Easy Ways To Fix ORA-01722 Invalid Number Error
Using Oracle database is very common but sometimes due to some uncertain reasons, you may get ORA-01722 invalid number error. Do you want to know what is ORA-01722 invalid number error and how to resolve ora-01722 invalid number error? If you want to know so then I must say that you have come to the right place. I am saying so because here I am going to mention some best ways to fix ORA-01722 error in Oracle.
Now, let’s get started with the introduction of this error, causes and then the ways to fix ora-01722 invalid number error and so on…..
What Is ORA_01722 Invalid Number Error?
ORA-01722 invalid number error is actually a fairly typical error in Oracle database. It is an invalid number error that occurs during a failure when you convert a character string to a valid number. It is an error that occurs due to arithmetic operation in the statement failed to calculate just because a single operand cannot be implicitly converted to a valid number. This error can take place due to several reasons which are further mentioned in this blog, so do not skip going through this blog.
Causes Of Occurring ORA-01722 Invalid Number Error
Several causes are that can lead you to face ORA-01722 invalid number error. However, some of the major causes are as follows:
Cause #1: Error During String Concatenation
If you use a plus (add) sign then it cannot concatenate strings. So, if you are using a wrong sign to concatenate two strings then you can get ORA-01722 invalid number error. Below is the code you might have tried to concatenate two strings:
SQL> set heading off;
SQL> select ‘Today is ‘ + sysdate from dual;
select ‘Today is ‘ + sysdate from dual
*
ERROR at line 1:
ORA-01722: invalid number
Here, SQL parser thought the statement tried to make arithmetic operation, however, it failed to continue. The right ways to concatenate two strings are as follows:
SQL> select ‘Today is ‘ || sysdate from dual;
Today is 26-DEC-19
The output of this code is perfect in concatenating two strings.
Cause #2: Error During Type Conversion
When you create a simple table that contains only one column with NUMBER type with the below code:
SQL> create table salaries (salary number);
Table created.
If you try to insert a row into the table that contains NUMBER column, you may get ORA_01722 error with the below code:
SQL> insert into salaries (salary) values (‘200,000’);
insert into salaries (salary) values (‘200,000’)
*
ERROR at line 1:
ORA-01722: invalid number
You may get this error because the value ‘200,000’ of column SALARY can’t be converted into a valid number. You can make it easier by just removing the comma separator:
SQL> insert into salaries (salary) values (‘200000’);
1 row created.
How To Fix ORA-01722 Invalid Number Error?
When it comes to fixing ORA_01722 invalid number error, you can try several ways but the best options you can try are further mentioned here. All these solutions are very easy to try and are the most effective and working ways:
Fix #1: Insert Or Update Using a Subquery
If you are inserting or updating values in a table by using a subquery then you can get this error. This error can be quite difficult to detect because you are not explicitly stating the values to be inserted. You get this error because sometimes even one of the values found in the subquery is trying to be inserted into some numeric column. Also, the value is not a number.
Well, to find the major causes of this error, you can try running the subquery by itself and you can add a WHERE condition as mentioned below:
Here, you can replace the column with the column you found has the bad data. Here, the UPPER and the LOWER functions will return the different values from character strings and then you will be left with the rows that have strings values.
If you want to dig further then you can follow the below steps:
- You can run the subquery by itself to check the results.
- You can look at the values in the column you are expecting to be numeric to identify any that look like the same characters.
- You can also perform a TO_NUMBER on the columns to find the error.
- Also, you can get a DISTINCT list of each column in the subquery and then you can perform a TO_NUMBER.
- Apart from this, you can use a WHERE clause to restrict the results of the subquery and so you are just looking at a small data set at a time.
After you found the value that causes this error, you can either update the bad data or you can update the query to properly handle this data.
Fix #2: Mismatch Of Data Types In An Insert Query
If you are trying to INSERT data into a table with the use of INSERT INTO VALUES?
If yes then you can check that the columns are aligned to the values you want. It means that you have to make sure that the position of the columns that used to contain numbers that you are trying to insert.
The below query will then produce ORA-01722 invalid number error:
INSERT INTO student_results (student_id, subject_name, score, comments)
VALUES (1, ‘Science’, ‘Pretty good’, 95);
After this query, you will get the columns around the wrong way and in order to correct the query, you have to move the score value of 95 to in between the comments and the subject name.
INSERT INTO student_results (student_id, subject_name, score, comments)
VALUES (1, ‘Science’, 95, ‘Pretty good’);
Fix#3: Convert Implicitly In a Select Statement
If you are getting the error ‘ora-01722 invalid number’ when you are running a SELECT statement there would be two possible reasons:
- Invalid format mask while using TO_NUMBER
- Convert implicitly in WHERE clause
In some cases, this error takes place due to implicit conversion in a WHERE clause. An implicit conversion is where a value is being converted by Oracle but you do not specify it.
However, in order to fix this issue, you have to check for a numeric column that is being compared to a character column. As for example:
This code insertion will result in an implicit conversion of the VARCHAR column to a number which may also cause the invalid number error.
However, if you are using the TO_NUMBER function in the query, you have to make sure that the format mask includes acceptable characters.
Fix #4: Some Common Possible Fixes
Apart from the above fixes, you can try these other possible ways to fix ORA-01722 invalid number error. Here are the other possible fixes you can try:
- The database formats for numbers are mismatched between these two databases. As for example, European numeric data uses 12.345,67 where US format is 12,345.67. You can review the NLS_LANG settings to make sure that it is not causing any problem.
- Fields that used to contain spaces cannot be easily converted, so it is important to make sure that you TRIM this data. After that, you convert it to NULL or also can convert it to ZERO.
- It is possible that a function-based index is causing ORA_01722 invalid number error. You can review the table to see if there are any function-based indexes that could be converting the data.
Ultimate Solution: Oracle File Repair Tool To Fix ORA-01722 Invalid Number Error
Even after trying all the above ways, you are still unable to resolve ora-01722 invalid number then you can try Oracle File Repair Tool. This tool has the best features that can definitely let you know how to resolve ora-01722 invalid number error? You can just try this tool and fix ora-01722 invalid number error due to its great features. All you have to do is to download and install Oracle File Repair Tool to fix ora-01722 invalid number error.
Below, you will get the step by step guide to know how to resolve ora-01722 invalid number error with this best-featured tool.
Steps To Fix ORA-01722 Invalid Number Error
Step 1: Search the Initial screen of Stellar Phoenix Oracle Repair & Recovery with a pop-up window showing options to select or search corrupt Oracle databases on 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.