Ошибка неверное число sql - Не ошибается лишь тот, кто ничего не делает!

A very easy one for someone,
The following insert is giving me the

ORA-01722: invalid number

why?

INSERT INTO CUSTOMER VALUES (1,'MALADY','Claire','27 Smith St Caulfield','0419 853 694');
INSERT INTO CUSTOMER VALUES (2,'GIBSON','Jake','27 Smith St Caulfield','0415 713 598');
INSERT INTO CUSTOMER VALUES (3,'LUU','Barry','5  Jones St Malvern','0413 591 341');
INSERT INTO CUSTOMER VALUES (4,'JONES','Michael','7  Smith St Caulfield','0419 853 694');
INSERT INTO CUSTOMER VALUES (5,'MALADY','Betty','27 Smith St Knox','0418 418 347');

asked Sep 23, 2012 at 1:24

Phillip GibsonPhillip Gibson

1,4872 gold badges10 silver badges5 bronze badges

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

answered Sep 23, 2012 at 1:32

Suppose tel_number is defined as NUMBER — then the blank spaces in this provided value cannot be converted into a number:

create table telephone_number (tel_number number);
insert into telephone_number values ('0419 853 694');

The above gives you a

ORA-01722: invalid number

cellepo

3,8512 gold badges36 silver badges57 bronze badges

answered Sep 23, 2012 at 8:37

holhol

8,2355 gold badges32 silver badges59 bronze badges

Here’s one way to solve it. Remove non-numeric characters then cast it as a number.

cast(regexp_replace('0419 853 694', '[^0-9]+', '') as number)

answered Dec 27, 2013 at 15:35

gmlacrossegmlacrosse

3622 silver badges8 bronze badges

Well it also can be :

SELECT t.col1, t.col2, ('test' + t.col3) as test_col3 
FROM table t;

where for concatenation in oracle is used the operator || not +.

In this case you get : ORA-01722: invalid number ...

answered Aug 8, 2016 at 12:35

Lazar LazarovLazar Lazarov

2,4124 gold badges25 silver badges35 bronze badges

This is because:

You executed an SQL statement that tried to convert a string to a
number, but it was unsuccessful.

As explained in:

Oracle/PLSQL: ORA-01722 Error.

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.

answered Sep 23, 2012 at 1:31

Mahmoud GamalMahmoud Gamal

77.8k17 gold badges139 silver badges164 bronze badges

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.

answered Sep 23, 2012 at 3:10

FreelancerFreelancer

8,9837 gold badges42 silver badges81 bronze badges

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’;

answered Nov 15, 2017 at 12:08

FrantaFranta

98810 silver badges17 bronze badges

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.

answered Sep 2, 2014 at 14:28

iTakeiTake

4,0123 gold badges32 silver badges26 bronze badges

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:

AID_YEAR  char(4)
OFFICE_ID char(5)

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;

answered Sep 23, 2015 at 22:23

I have found that the order of your SQL statement parameters is also important and the order they are instantiated in your code, this worked in my case when using «Oracle Data Provider for .NET, Managed Driver».

var sql = "INSERT INTO table (param1, param2) VALUES (:param1, :param2)";
...
cmd.Parameters.Add(new OracleParameter("param2", Convert.ToInt32("100")));
cmd.Parameters.Add(new OracleParameter("param1", "alpha")); // This should be instantiated above param1.

Param1 was alpha and param2 was numeric, hence the «ORA-01722: invalid number» error message. Although the names clearly shows which parameter it is in the instantiation, the order is important. Make sure you instantiate in the order the SQL is defined.

Dharman♦

30.4k22 gold badges84 silver badges132 bronze badges

answered Mar 16, 2022 at 13:38

JayKayOf4JayKayOf4

1,2021 gold badge12 silver badges15 bronze badges

For me this error was a bit complicated issue.

I was passing a collection of numbers (type t_numbers is table of number index by pls_integer;) to a stored procedure. In the stored proc there was a bug where numbers in this collection were compared to a varchar column

select ... where ... (exists (select null from table (i_coll) ic where ic.column_value = varchar_column))

Oracle should see that ic.column_value is integer so shouldn’t be compared directly to varchar but it didn’t (or there is trust for conversion routines).

Further complication is that the stored proc has debugging output, but this error came up before sp was executed (no debug output at all).

Furthermore, collections [<empty>] and [0] didn’t give the error, but for example [1] errored out.

answered Sep 5, 2022 at 12:43

Pasi SavolainenPasi Savolainen

2,4401 gold badge22 silver badges35 bronze badges

The ORA-01722 error is pretty straightforward. According to Tom Kyte:

We’ve attempted to either explicity or implicity convert a character string to a number and it is failing.

However, where the problem is is often not apparent at first. This page helped me to troubleshoot, find, and fix my problem. Hint: look for places where you are explicitly or implicitly converting a string to a number. (I had NVL(number_field, 'string') in my code.)

answered May 11, 2016 at 23:01

BaodadBaodad

2,3852 gold badges36 silver badges39 bronze badges

This happened to me too, but the problem was actually different: file encoding.

The file was correct, but the file encoding was wrong. It was generated by the export utility of SQL Server and I saved it as Unicode.

The file itself looked good in the text editor, but when I opened the *.bad file that the SQL*loader generated with the rejected lines, I saw it had bad characters between every original character. Then I though about the encoding.

I opened the original file with Notepad++ and converted it to ANSI, and everything loaded properly.

answered Mar 12, 2020 at 14:54

cienciaciencia

4464 silver badges10 bronze badges

In my case it was an end of line problem, I fixed it with dos2unix command.

answered Oct 25, 2022 at 22:59

tsunlllytsunllly

1,5481 gold badge13 silver badges15 bronze badges

In my case I was trying to Execute below query, which caused the above error ( Note : cus_id is a NUMBER type column)

select * 
from customer a
where a.cus_id IN ('115,116')

As a solution to the caused error, below code fragment(regex) can be used which is added in side IN clause (This is not memory consuming as well)

select * 
from customer a
where a.cus_id IN (select regexp_substr (
       com_value,
       '[^,]+',
       1,
       level
     ) value
from (SELECT '115,116' com_value
            FROM dual)rws
connect by level <= 
length ( com_value ) - length ( replace ( com_value, ',' ) ) + 1)

answered Dec 23, 2022 at 3:21

try this as well, when you have a invalid number error

In this
a.emplid is number and b.emplid is an varchar2 so if you got to convert one of the sides

where to_char(a.emplid)=b.emplid

answered Jun 8, 2016 at 14:35

You can always use TO_NUMBER() function in order to remove this error.This can be included as INSERT INTO employees phone_number values(TO_NUMBER(‘0419 853 694’);

answered Oct 11, 2014 at 6:08

Источник

CREATE OR REPLACE FUNCTION evaluation (a DATE,m Number)
RETURN VARCHAR2
IS
BEGIN
RETURN CASE
WHEN (floor(sysdate - a) < m) THEN 'Yes'
ELSE 'No'
END;

Am i doing wrongly, when executing the following code if shows me an error like:

Error: ORA-01722: invalid number

asked Mar 25, 2013 at 9:23

The posted code is missing an END; (the first END ends the CASE, so a second is needed to end the procedure), but other than that it is working, provided you enter a number and not a string that cannot be converted to a number.

select evaluation(sysdate+3, 2) from dual;

YES

select evaluation(sysdate+3, '2') from dual;

YES

select evaluation(sysdate+3, 'a') from dual;

Error: ORA-01722: invalid number

answered Mar 25, 2013 at 9:32

Klas LindbäckKlas Lindbäck

33k5 gold badges57 silver badges82 bronze badges

Include another end @ last…..

CREATE OR REPLACE FUNCTION evaluation (a DATE,m Number)
RETURN VARCHAR2
IS
BEGIN
RETURN (CASE
WHEN (floor(sysdate - a) < m) THEN 'Yes'
ELSE 'No'
END);
END;

answered Mar 25, 2013 at 11:30

AspirantAspirant

2,2289 gold badges31 silver badges43 bronze badges

Источник

ORA-01722: неправильный номер

Причина:

Пробное преобразование символьной строки в числовую провалилось, потому что символьная строка не была правильной числовой литерой. Только числовые поля или символьные поля содержащие числовые данные могут быть использованы в арифметических функциях или выражениях. Только числовые поля могут быть добавлены или вычтены из данных.

Действие:

Проверьте символьные строки в функции или в выражении; убедитесь в том, что они содержат только числа, знаки, десятичные точки, и символ «E» или «e», затем повторите операцию.

Источник

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.

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:

WHERE UPPER(column) != LOWER(column)

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:

SELECT …

WHERE number_col = varchar_col;

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.

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.

Final Verdict

While using Oracle database, if you ever come across an error stated as ‘ORA-01722 Invalid Number’ then you should try these ways mentioned above. I have tried my best to provide you the working and the effective solution I can. However, if manual ways do not work in your case, then you can also try Oracle File Repair Tool. This tool has the capability to fix any kind of error related to Oracle database. So, know how to resolve ora-01722 invalid number error in Oracle database and recover the database easily in no time.

Jacob Martin is a technology enthusiast having experience of more than 4 years with great interest in database administration. He is expertise in related subjects like SQL database, Access, Oracle & others. Jacob has Master of Science (M.S) degree from the University of Dallas. He loves to write and provide solutions to people on database repair. Apart from this, he also loves to visit different countries in free time.

Источник

Have you gotten an “ORA-01722 invalid number” error? I’ll explain what this error is and how you can resolve it in this article.

ORA-01722 Cause

The ORA-01722 error is caused by trying to convert a string into a number. It could be because of an invalid numeric character in your expression, or you’re trying to add a text value into a number column.

You’ve run an SQL query (which can be SELECT, INSERT, UPDATE, for example), and you’ve got this error:

ORA-01722: invalid number

The reason for this error is that Oracle has attempted to convert a string into a number, but could not perform the conversion.

For example, converting the value of “Hello” to a number cannot be done.

A valid number contains these characters:

Digits 0 to 9
Possibly a decimal point
A + or – sign
An E or e character

The error can appear in the following queries:

SELECT TO_NUMBER('123,100') FROM dual;

SELECT 'DatabaseStar' - 2016 FROM dual;

There are a few ways to resolve this error, so let’s take a look.

The solution to the “invalid number” error message could be one of several things:

An incorrect character or typo in your query
Bad data in your database
An issue with the query logic

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:

1 – Mismatch of Data Types in an Insert Query

Are you trying to INSERT data into a table using INSERT INTO VALUES?

If so, check that your columns are aligned to your values. I mean, make sure that the position of the columns that contain numbers match the numbers you’re trying to insert.

This query will produce an error (assuming that score is a number):

INSERT INTO student_results (student_id, subject_name, score, comments)
VALUES (1, 'Science', 'Pretty good', 95);

I’ve got the columns around the wrong way here. To correct the query, I need to move the score value of 95 to in between the subject name and comments.

INSERT INTO student_results (student_id, subject_name, score, comments)
VALUES (1, 'Science', 95, 'Pretty good');

2 – Inserting or Updating using a Subquery

Are you inserting or updating values in a table using a subquery?

This error can be harder to detect, because you’re not explicitly stating the values to be inserted.

The error occurs because one of the values found in the subquery is trying to be inserted into a numeric column, and the value is not a number.

To find the cause of the error, you can run the subquery by itself and add a WHERE condition:

WHERE UPPER(column) != LOWER(column)

Replace the “column” with the column you suspect has the bad data. The UPPER and LOWER functions will return different values from character strings, and you should be left with the rows that have string values.

If you want to dig further, or if that doesn’t work:

Run the subquery by itself to see the results
Look at the values in the columns you’re expecting to be numeric to identify any that look like obvious characters.
If you can’t easily tell, you can perform a TO_NUMBER on the columns to find the error.
If this doesn’t help, try getting a DISTINCT list of each column in the subquery, and then performing a TO_NUMBER.
You can also use a WHERE clause to restrict the results of the subquery, so you’re only looking at a small data set at a time.

Once you’ve found the value that causes the issue, you can either update the bad data, or update your query to handle this data.

3 – Implicit Conversion in a Select Statement

Are you getting this “ORA-01722 invalid number” error when running a SELECT statement?

There are a few reasons for this error:

Implicit conversion in WHERE clause
Invalid format mask when using TO_NUMBER

In most cases, it is due to an implicit conversion in a WHERE clause. An implicit conversion is where a value is being converted by Oracle but you didn’t specify it.

To resolve this, check for a numeric column that’s being compared to a character column.

For example:

SELECT ...
WHERE number_col = varchar_col;

This will result in an implicit conversion of the VARCHAR column to a number, which may cause the invalid number error.

If you’re using the TO_NUMBER function in your query, make sure that the format mask includes acceptable characters.

The earlier example would cause an error.

4 – Other Possible Solutions

There are some other solutions to this error:

Fields that contain spaces cannot be converted, so ensure that you TRIM this data, convert it to NULL or zero.
The database formats for numbers are mismatched between two databases. For example, European numeric data uses 12.345,67 where US format is 12,345.67. Review your NLS_LANG settings to ensure this isn’t causing any issues.
A function-based index on the table could be causing the error. Review your table to see if there are any function-based indexes that could be converting data.

So, that’s how you resolve the ORA-01722 invalid number error in Oracle!

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:

Источник

Сервер переехал на новый сайт http://www.oraclegis.com/blog. Здесь идет ретрансляция

пятница, 21 ноября 2008 г.

Ошибка ORA-01722: неверное число или ORA-01722: inval >

Обычно ошибка возникает при загрузки данных из текстового файла с помощью Oracle Sqlloader. Ошибка связана с тем, что в России разделитель целой и дробной часть запятая, а в США и Европе – точка.

Если попытаться загрузить в базу некорректно отформатированные данные, то Oracle их не поймет. Выйти из этого положения можно несколькими способами. Некоторые проще, некоторые сложне. Давайте рассмотрим их. Вначале простые, к концу более сложные и, возможно, чреватые проблемами.

1. Зайти в файл и заменить все точки на запятые. Например

1|New York|NY|7322564|1|2001|8307|-73.943849000|40.669800000|
заменяем на

Тут всеп просто. Никаких подводных камней, кроме того, что точки могут встречаться не только в числах и это надо отслеживать, иначе испортите данные.

Например в Linux это легко сделать с помощью sed (для Windows можно взять отсюда или использовать, скажем, perl). Команда не создает резервной копии.

sed -i ‘s/./,/g’ filename.txt

2. В локальном файле при загрузке данных установить соответствующие параметры NLS.

Например, если загрузка осуществляется следующим скриптом батником:

sqlldr scott/tiger@orcl control=us_citiesus_cities.ctl data=us_citiesus_cities.dat

то ошибки можно избежать с помощью выполнения предварительно в bat-файле вот такой команды:

set nls_lang=american_america.CL8MSWIN1251
sqlldr scott/tiger@orcl control=us_citiesus_cities.ctl data=us_citiesus_cities.dat

Я считаю этот вариант наиболее предпочтительным.

3. Если это Windows, то можно изменить глобальные настройки NLS. Сделать это можно в реестре в веточке

Нужно будет вместо значения RUSSIAN_RUSSIA.CL8MSWIN1251 прописать AMERICAN_AMERICA.CL8MSWIN1251.

Это решение может очень сильно повлиять на систему в целом. Действие этого параметра может повлечь неработоспособность другого приложения.

Я запустил sql-запрос в oracle sql-разработчике, и он работает отлично, но когда я использую оболочку, в данном случае adodb в excel vba, для выполнения того же запроса я получаю сообщение об ошибке. Я подозреваю, что adodb использует другую версию oracle, и оптимизация выполняется по-разному.

Я выполнил запрос: select (LATITUDE) from db where sin(LATITUDE)

Я получаю сообщение об ошибке:

ORA-01722: неверный номер

ORA-02063: предыдущая строка из db

Если я изменяю sin(LATITUDE) в запросе:

LATITUDE (он не работает)

LATITUDE (он работает)

sin(LATITUDE) (он не работает)

to_number(sin(LATITUDE)) (он не работает)

sin(to_number(LATITUDE)) (он не работает)

Я также попробовал придать LATITUDE плавучесть, и это тоже не сработает. Единственный экземпляр, где он выполняется, — это без SIN и с одинарными кавычками вокруг плавающего значения справа от условия.

Я в недоумении относительно того, почему я не могу заставить запрос работать без каких-либо ошибок, хотя он отлично работает в Oracle SQL Developer. Любая помощь будет оценена по достоинству.

Таким образом, кажется, что LATITUDE хранится как число, а некоторые из значений не являются числовыми.

Возможно, такой запрос поможет вам разобраться в проблеме:

Это не идеальный тест, но он часто работает.

Лучший метод использует регулярные выражения:

Следуя следствию в комментариях, это, по-видимому, является разницей в настройке NLS между прямым SQL-клиентом и ADODB-соединением. Поскольку вы храните число в виде строки (которая всегда является плохим идеей), а число имеет десятичный разделитель, разные настройки NLS_NUMERIC_CHARACTERS вызывают ошибку в одном месте, но не в другом.

Oracle использует этот параметр NLS при неявном преобразовании из строки в число или если у вас есть явный to_number() без маски формата. Поэтому в любой среде одно из этих операторов будет работать, а другое получит ошибку ORA-01722:

Который не работает, зависит от настройки NLS. Вы можете использовать явную маску формата, чтобы избежать этого, и третий параметр to_number() чтобы сказать, как его интерпретировать:

Это использует маркер разделителя D дециметров, который соответствует первому символу в переопределении NLS — обратите внимание, что переключатель периода и запятой между этими двумя операторами — и что переопределение NLS должно быть установлено в соответствии с вашими фактическими данными. Вам также необходимо использовать соответствующие цифры цифр до и после десятичной точки.

В предположении, что вы имеете дело с десятичными градусами, вы должны иметь возможность использовать:

Вам действительно нужно только два девятки перед десятичной точкой для широты, но вам понадобятся три по долготе, поэтому вы также можете быть последовательными.

Но вам также нужно сделать это явное преобразование везде, где вы ссылаетесь на него, например:

Этого можно было бы избежать, если бы вы использовали правильный тип данных в первую очередь. Если вы не можете изменить тип столбца, вы можете подумать о добавлении виртуального столбца, который делает для вас такое же преобразование, или если этому не разрешено представление, которое изменяет тип.

Более ранний ответ дал вариацию Гордону Линоффу, но оказался красной селедкой.

Базы данных

ORA-01722: неправильный номер

Причина:

Действие:

Источник

In my previous article i have given the brief introduction about different errors like ORA-12154: TNS listener error and ORA-00600 which is related to internal error codes.The third most searched Error for oracle is ORA-01722 Error which is Invalid Number error. This error is searched approximately 25 k times per month in google search engine.In this article i will try to explain you why this error will come and how this error will solve.You will get this error while converting non numeric string to number or while inserting string value or character value in to number column.

“ORA-01722 error will be searched approximately 25 k times per month on google.”

Why ORA-01722 error will come?

This is another common error the SQL and PL SQL developers and DBAs will face.This error will come in oracle for mismatching the datatype as number to string or trying to insert the string value in to number values.There should be different reasons for this error.I will mention some scenarios in which this error will come.

Situation 1 :

When user tries to convert string value to number value.

To explain this scenario i will take a simple example. I will try to convert the string in to number using TO_NUMBER function.

Query :

select to_number(‘XYZ’) from dual;

Output :

Error at line 1:

ORA-01722: invalid number

Situation 2:

When user tries to insert the string value in to number column.

Query:

create table Employee
(rollno number(10));

insert into Employee
values(1);
commit;

select * from Employee;

insert into Employee
values(‘amit’);
commit;

Output :

Error at line 1:

ORA-01722: invalid number

The ORA-01722 error will come when you try to convert the string value in to number.

Situation 3 :

Query :

create table Test111
(no1 varchar2(10));

insert into Test111
values(‘1′);
commit;

insert into Test111

values(’10’);
commit;

insert into Test111
values(‘a’);
commit;

select * from Test111 where no1 between 0 and 10;

Output :

Error at line 1:

ORA-01722: invalid number

NO TIME TO READ CLICK HERE TO GET THIS ARTICLE

Resolution of the Error :

I have given the basic scenarios of producing the ORA-1722 error.This error will come when we tries to convert the string value in to number using to_number function. The string can not be converted to number.So to resolve the error we need to use number value in to_number function.

Resolution 1 :

Use number value in to_number function.

Query :

select to_number(‘111’) from dual;

Output :

111

Always use number value (for varchar2 column) in to_number function to convert that value to number.

Resolution 2 :

Check description of table and change the column to varchar2 column if you want to insert the varchar2 value in the column.

Query :

Alter table Employee

modify rollno varchar2(10);

insert into Employee
values(‘amit’);
commit;

1 Record inserted

So to resolve this error we need to find out the number column from the table and change it to varchar2 column.After altering the column we will be able to insert varchar datatype values.

Источник

Содержание

Oracle SQL Error [1722] [42000]: ORA-01722: invalid number (runs well on SQL Developer) #6513
Comments
Footer
ORA-01722: invalid number | How to resolve ORA-01722 error?
ORA-01722: invalid number :
Why ORA-01722 error will come?
NO TIME TO READ CLICK HERE TO GET THIS ARTICLE
Resolution of the Error :
How to Resolve ORA-01722: invalid number
ORA-01722
A. Type Conversion
1. Make it Easier to Convert
2. Use VARCHAR2
B. String Concatenation
Ora 01722 неверное число
Сервер переехал на новый сайт http://www.oraclegis.com/blog. Здесь идет ретрансляция
пятница, 21 ноября 2008 г.
Ошибка ORA-01722: неверное число или ORA-01722: inval >
1. Зайти в файл и заменить все точки на запятые. Например
2. В локальном файле при загрузке данных установить соответствующие параметры NLS.
3. Если это Windows, то можно изменить глобальные настройки NLS. Сделать это можно в реестре в веточке
Базы данных
Причина:
Действие:

Oracle SQL Error [1722] [42000]: ORA-01722: invalid number (runs well on SQL Developer) #6513

System information:

Windows 10 pro (EN)
DBeaver 6.1.4
Without additional extensions

Connection specification:

Describe the problem you’re observing:
Running a query were I’m multiplying 2 varchar (one of them converted to_numeric) returns me the error «SQL Error [1722] [42000]: ORA-01722: invalid number». The same query runs well on SQL Developer.

Columns configuration:

Columns on query:

Execution of the same query in SQL Developer:

org.jkiss.dbeaver.model.sql.DBSQLException: SQL Error [1722] [42000]: ORA-01722: invalid number

Caused by: java.sql.SQLSyntaxErrorException: ORA-01722: invalid number

The text was updated successfully, but these errors were encountered:

Generally it works fine in DBeaver
SELECT to_number(‘123’) * ‘3’ FROM dual
shows 369 in results. So problem is definitely not in multiplying numbers. Perhaps some of varchars is not actually a number.

try to find
select option_3 from _table_ where not REGEXP_LIKE(option_3, ‘^[ ]*[+,-]*[0-9]*[.]?[0-9]*$’)

Thanks for your reply @cerebel and @serge-rider

I taked the suggestion from @cerebel but for field option_4. And I got all the table rows returning on query result. This happened because the numbers were saved with comma instead of dot for decimal separator.

I know that this is not a normal and «valid» operation (multiplying numerics with varchars), but why it works on Oracle SQL Developer and in DBeaver returns error? The Oracle Connection Driver is the same.

the difference might be in language settings. sqldeveloper changes many oracle session options that affect how numbers/dates are interpreted.

@jpfmeneses about dot/comma of decimal separator — checks
select * from v$nls_parameters where parameter =’NLS_NUMERIC_CHARACTERS’
oracle say v10 by example
NLS_NUMERIC_CHARACTERS =»decimal_character group_separator»
so, find which application changes nls_param.. ( try sqplus, sqlDevelopper, dbeaver)
In dbeaver:queryManger (enable all options in preferences/general/queryMangery:queryTypes, and open view of queryManger)- this param is not changed.

@cerebel thanks for your feedback.

SQL Developer:

I went into Dbeaver to run that query first, after that opened the SQL Developer and go back to Dbeaver and verified that the parameter stayed with the same values, but different from SQL Developer. There is any DBeaver option that I can consider to change this parameter?

@jpfmeneses will set [,] as decimal separator (at session level)
databaseNavigator->editConnection ->connectionSettings-> initialization->bootstrapQueries
ALTER SESSION SET NLS_NUMERIC_CHARACTERS=’,.’
or execute upper sql, in editor ..

src
In SQL Developer, Preferences->Database-> NLS, if you change the Decimal Separator setting, you also need to make sure that your new setting doesn’t conflict with the existing Group Separator setting.
In this case, you needed to change Decimal Separator to a period ., but also change the Group Separator setting from a period to a comma , to avoid a setting conflict.
or, in editor, run upper sql ..

Better to redefine which decimal separator will be used in yours option_X columns, because at database level — it is [.], not [,] .

You can’t perform that action at this time.

You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.

Источник

ORA-01722: invalid number | How to resolve ORA-01722 error?

ORA-01722: invalid number :

“ORA-01722 error will be searched approximately 25 k times per month on google.”

Why ORA-01722 error will come?

Situation 1 :

When user tries to convert string value to number value.

To explain this scenario i will take a simple example. I will try to convert the string in to number using TO_NUMBER function.

Query :

Output :

ORA-01722: invalid number

Situation 2:

When user tries to insert the string value in to number column.

Query:

insert into Employee
values(1);
commit;

select * from Employee;

insert into Employee
values(‘amit’);
commit;

Output :

ORA-01722: invalid number

The ORA-01722 error will come when you try to convert the string value in to number.

Situation 3 :

Query :

insert into Test111
values(‘1′);
commit;

insert into Test111

insert into Test111
values(‘a’);
commit;

select * from Test111 where no1 between 0 and 10;

Output :

ORA-01722: invalid number

NO TIME TO READ CLICK HERE TO GET THIS ARTICLE

Resolution of the Error :

Resolution 1 :

Use number value in to_number function.

Query :

Output :

Always use number value (for varchar2 column) in to_number function to convert that value to number.

Resolution 2 :

Check description of table and change the column to varchar2 column if you want to insert the varchar2 value in the column.

Query :

modify rollno varchar2(10);

insert into Employee
values(‘amit’);
commit;

Источник

How to Resolve ORA-01722: invalid number

ORA-01722

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.

Источник

Ora 01722 неверное число

Сервер переехал на новый сайт http://www.oraclegis.com/blog. Здесь идет ретрансляция

пятница, 21 ноября 2008 г.

Ошибка ORA-01722: неверное число или ORA-01722: inval >

1. Зайти в файл и заменить все точки на запятые. Например

1|New York|NY|7322564|1|2001|8307|-73.943849000|40.669800000|
заменяем на

sed -i ‘s/./,/g’ filename.txt

2. В локальном файле при загрузке данных установить соответствующие параметры NLS.

Например, если загрузка осуществляется следующим скриптом батником:

sqlldr scott/[email protected] control=us_citiesus_cities.ctl data=us_citiesus_cities.dat

то ошибки можно избежать с помощью выполнения предварительно в bat-файле вот такой команды:

set nls_lang=american_america.CL8MSWIN1251
sqlldr scott/[email protected] control=us_citiesus_cities.ctl data=us_citiesus_cities.dat

Я считаю этот вариант наиболее предпочтительным.

3. Если это Windows, то можно изменить глобальные настройки NLS. Сделать это можно в реестре в веточке

Нужно будет вместо значения RUSSIAN_RUSSIA.CL8MSWIN1251 прописать AMERICAN_AMERICA.CL8MSWIN1251.

Я выполнил запрос: select (LATITUDE) from db where sin(LATITUDE)

Я получаю сообщение об ошибке:

ORA-01722: неверный номер

ORA-02063: предыдущая строка из db

Если я изменяю sin(LATITUDE) в запросе:

LATITUDE (он не работает)

LATITUDE (он работает)

sin(LATITUDE) (он не работает)

to_number(sin(LATITUDE)) (он не работает)

sin(to_number(LATITUDE)) (он не работает)

Таким образом, кажется, что LATITUDE хранится как число, а некоторые из значений не являются числовыми.

Возможно, такой запрос поможет вам разобраться в проблеме:

Это не идеальный тест, но он часто работает.

Лучший метод использует регулярные выражения:

В предположении, что вы имеете дело с десятичными градусами, вы должны иметь возможность использовать:

Но вам также нужно сделать это явное преобразование везде, где вы ссылаетесь на него, например:

Более ранний ответ дал вариацию Гордону Линоффу, но оказался красной селедкой.

Базы данных

ORA-01722: неправильный номер

Причина:

Действие:

Источник

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?

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

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.

Fix #1: Insert Or Update Using a Subquery

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:

WHERE UPPER(column) != LOWER(column)

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?

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:

SELECT …

WHERE number_col = varchar_col;

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

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.

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.

Final Verdict

Источник

Learn the cause and how to resolve the ORA-01722 error message in Oracle.

Description

When you encounter an ORA-01722 error, the following error message will appear:

ORA-01722: invalid number

Cause

You executed a SQL statement that tried to convert a string to a number, but it was unsuccessful.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Only numeric fields or character fields that contain numeric values can be used in arithmetic operations. Make sure that all expressions evaluate to numbers.

Option #2

If you are adding or subtracting from dates, make sure that you added/substracted a numeric value from the date.

Источник