Ошибка неверное число oracle
Microsoft SQL Server 2008 с пакетом обновления 2 (SP2) или Microsoft SQL Server 2008 R2 исправляет ошибки в одном загружаемом файле. Поскольку исправления являются кумулятивными, каждый новый выпуск содержит все исправления и все исправления для системы безопасности, которые вошли в состав предыдущего исправления для SQL Server 2008 с пакетом обновления 2 (SP2) или SQL Server 2008 R2.
5 Answers 5
The key reason is about java.sql.SQLException: ORA-01722: invalid number .
May be the field last_case_status_history_id type is number, but your parameter is null
Some times it happens when we are using IN clause and passing the values as IN ('1, 2, 3, 4') instead of passing it in following way IN ('1','2','3','4')
We had a similar issue. Our hibernate based java code issued a prepared statement to fill in user info and a 'reason for change' for all save operations by using aspectJ.
In 3 separate database environments (Oracle 10G) this worked without any issues, but on the production database this sometimes failed with the ORA-01722 error. This only happens when the cpu load on the database server is close to 100%.
On another forum I found a suggestion about not passing along Long objects but doing the unboxing explicitly to a long primitive. It seems the jdbc driver or the database itself has issues when doing this unboxing on high load (even if that sounds pretty crazy). Various jdbc drivers were tested, such as the 1.4 and 1.6 versions.
The code that sometimes failed was:
Now we changed the code to make an explicit preparedStatement like this:
This issue has not happened since, even while the environment stayed the same with the same software and high load on the database.
I've heard from a colleague that one of the DBA's could see in the logging that the prepared statement was accepted by the database, but no cpu was assigned to it. (Makes sense since all cpu's would be busy on such a high load.) It might be that simply the wrong error is thrown, a 'database overload' error of some sorts should be thrown or something. Even better would be to never create such an error anyway.
I guess it's primarily the database load though, it's not smart at all to let the cpu load reach 100% all the time.
The telephone numbers are the only thing which might reasonably be a defined as a numeric which your data doesn't represent as a numeric (spaces aren't numeric). So: check your table definition and compare with your input statements.
Why would people down vote this question. For people who are new to databases, this is a weird error. I can see how enclosing the values with quotes might make it look like it's a string. It just depends on what the database is setup as. It might all be strings or numbers just depends on the fields. Maybe it was an error when the database was created.
"Back in o`twelve, I pulled a book off the shelve, and answered an Oracle question. At 'Stack I'm still adept, up with knowledge I've kept, but I still don't have an accept."
Причина:
Пробное преобразование символьной строки в числовую провалилось, потому что символьная строка не была правильной числовой литерой. Только числовые поля или символьные поля содержащие числовые данные могут быть использованы в арифметических функциях или выражениях. Только числовые поля могут быть добавлены или вычтены из данных.
Проблемы
Рассмотрим следующий сценарий.
Вы создаете таблицу со столбцом типа данных varchar (max) в Microsoft sql Server 2008 или Microsoft sql Server 2008 R2.
Вы пытаетесь реплицировать таблицу на подписчик сервера Oracle, и вы выбираете публикацию транзакций в качестве типа публикации.Примечание. Начальная синхронизация выполнена успешно.
Вы вставляете или обновляете запись с длинным значением в столбце varchar (max) таблицы. Например, длина значения превышает 8200 знаков.
Решение
Действие:
Проверьте символьные строки в функции или в выражении; убедитесь в том, что они содержат только числа, знаки, десятичные точки, и символ «E» или «e», затем повторите операцию.
Итак . для чего это определение таблицы CUSTOMER ? Вы предоставили только половину необходимой информации.
Телефонные номера - единственное, что может быть определено как число, которое ваши данные не представляют как число (пробелы не являются числами). Итак: проверьте определение вашей таблицы и сравните с вашими операторами ввода.
Почему люди проголосовали против этого вопроса. Для людей, которые плохо знакомы с базами данных, это странная ошибка. Я вижу, как заключение значений в кавычки может сделать его похожим на строку. Это просто зависит от того, как настроена база данных. Это могут быть все строки или числа, в зависимости от полей. Возможно, это была ошибка при создании базы данных.
Пример вышеописанной ситуации: вставить в table_1 (rollNumber) значения ('123'); где rollNumber - столбец типа «число».
Ошибка ORA-01722 возникает, когда делается попытка преобразовать строку символов в число, и строка не может быть преобразована в число.
Не видя определения вашей таблицы, похоже, что вы пытаетесь преобразовать числовую последовательность в конце списка значений в число, а пробелы, которые ее разделяют, вызывают эту ошибку. Но, судя по информации, которую вы нам предоставили, это может происходить в любом поле (кроме первого).
Также обратите внимание, что заполнение поля вручную с помощью «(null)» даст вам эту ошибку. Если значение по умолчанию равно нулю, и вы не завершите его, оно будет автоматически заполнено (null), но когда вы его вводите, это не то же самое.
Эх, «строку нельзя преобразовать в число» - не в этом дело. Строку МОЖНО преобразовать в число. Дело здесь в обратном преобразовании числа в столбец String, но Oracle не делает этого автоматически: вы должны сами сделать его String, явно пометив значение в своем выражении SQL, чтобы окружить его "OR" " .
Предположим, номер телефона определен, так как NUMBER тогда пробелы не могут быть преобразованы в число:
Вышеупомянутое дает вам
Вот один из способов решить эту проблему. Удалите нечисловые символы, а затем преобразуйте их в число.
это относится к исходному OP - ваш столбец таблицы не может иметь тип "number", если вы хотите хранить значения, такие как '0419 853 694', потому что число не может иметь начальных нулей. Однако в моем случае это именно то, что мне нужно, ty gmlacrosse!
Ну это тоже может быть:
где для конкатенации в oracle используется оператор || not + .
В этом случае вы получите: ORA-01722: invalid number .
Этот подходит. Другая ядовитая судьба может быть такой. Вы закомментировали поле в списке полей SELECT, например - t.fieldname. Позже вы собираетесь удалить этот комментарий начала строки, но случайно остался один закомментированный символ. Таким образом, существует: - t.fieldname. Это случилось со мной, когда я работал над очень большим запросом, где мне приходилось комментировать / раскомментировать сотни столбцов, реорганизуя зверя.
Вы выполнили инструкцию SQL, которая пыталась преобразовать строку в число, но безуспешно.
Как объяснено в:
Чтобы устранить эту ошибку:
В арифметических операциях можно использовать только числовые поля или символьные поля, содержащие числовые значения. Убедитесь, что все выражения вычисляются как числа.
Oracle выполняет автоматическое преобразование String2number для значений столбца String ! Однако для текстовых сравнений в SQL входные данные должны быть явно разделены как String: обратное преобразование number2String не выполняется автоматически, ни на уровне SQL-запроса.
У меня был такой запрос:
select max(acc_num) from ACCOUNTS where acc_num between 1001000 and 1001999;
Это представляло проблему: Error: ORA-01722: invalid number
Я просто обвел "числовые" значения, чтобы сделать их "строками" , просто сделав их явно ограниченными :
select max(acc_num) from ACCOUNTS where acc_num between '1001000' and '1001999';
. и вуаля: он возвращает ожидаемый результат.
edit: И действительно: столбец acc_num в моей таблице определяется как String . invalid number Сообщалось, хотя и не числовым . И явное разделение числовых строк решило проблему.
С другой стороны, Oracle может рассматривать строки как числа. Таким образом, числовые операции / функции могут применяться к строкам, и эти запросы работают:
выберите max (string_column) из ТАБЛИЦЫ;
выберите string_column из ТАБЛИЦЫ, где string_column между '2' и 'z';
выберите string_column из ТАБЛИЦЫ, где string_column > '1';
выберите string_column из ТАБЛИЦЫ, где string_column 'b';
я столкнулся с подобной ситуацией. ora-01722 неверный номер был инициирован оператором выбора, а не оператором вставки. Я проверил определение таблицы и обнаружил, что столбец представляет собой varchar вместо числа, поэтому я добавил одинарные кавычки вокруг числа
В моем случае ошибка преобразования была в функциональном индексе , который я создал для таблицы.
Вставленные данные были в порядке. Мне потребовалось время, чтобы понять, что на самом деле ошибка связана с ошибочным индексом.
Если вы сделаете insert into. select * from. заявление, также легко получить ошибку «Неверный номер».
Допустим, у вас есть таблица FUND_ACCOUNT с двумя столбцами:
Допустим, вы хотите изменить значение OFFICE_ID на числовое, но в таблице уже есть строки, и, что еще хуже, некоторые из этих строк имеют значение OFFICE_ID, равное '' (пусто). В Oracle вы не можете изменить тип данных столбца, если в таблице есть данные, и требуется небольшая хитрость для преобразования '' в 0. Итак, вот как это сделать:
- Создайте копию таблицы: CREATE TABLE FUND_ACCOUNT2 AS SELECT * FROM FUND_ACCOUNT;
- Удалите все строки из исходной таблицы: DELETE FROM FUND_ACCOUNT;
Когда в исходной таблице нет данных, измените тип данных ее столбца OFFICE_ID: ALTER TABLE FUND_ACCOUNT MODIFY (OFFICE_ID number);
Но тогда вот сложная часть. Поскольку некоторые строки содержат пустые значения OFFICE_ID, если вы сделаете простое INSERT INTO FUND_ACCOUNT SELECT * FROM FUND_ACCOUNT2 , вы получите ошибку «ORA-01722 Invalid Number». Чтобы преобразовать '' (пустые) OFFICE_ID в 0, ваш оператор вставки должен выглядеть следующим образом:
INSERT INTO FUND_ACCOUNT (AID_YEAR, OFFICE_ID) SELECT AID_YEAR, decode(OFFICE_ID,' ',0,OFFICE_ID) FROM FUND_ACCOUNT2;
Это очень сложный пример случая, когда может произойти ошибка + объяснение, как решить этот конкретный случай, который может вообще не применяться.
Это случилось и со мной, но проблема была в другом: в кодировке файлов .
Файл был правильным, но кодировка файла неправильная. Он был сгенерирован утилитой экспорта SQL Server, и я сохранил его как Unicode.
Сам файл выглядел хорошо в текстовом редакторе, но когда я открыл *.bad файл, созданный загрузчиком SQL * с отклоненными строками, я увидел, что между каждым исходным символом были плохие символы. Тогда я задумался о кодировке.
Я открыл исходный файл с помощью Notepad ++ и преобразовал его в ANSI, и все загрузилось правильно.
Ошибка ORA-01722 довольно проста. По словам Тома Кайта :
Мы пытались явно или неявно преобразовать символьную строку в число, и это не удалось.
пятница, 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/[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 плавучесть, и это тоже не сработает. Единственный экземпляр, где он выполняется, — это без SIN и с одинарными кавычками вокруг плавающего значения справа от условия.
Я в недоумении относительно того, почему я не могу заставить запрос работать без каких-либо ошибок, хотя он отлично работает в Oracle SQL Developer. Любая помощь будет оценена по достоинству.
Таким образом, кажется, что LATITUDE хранится как число, а некоторые из значений не являются числовыми.
Возможно, такой запрос поможет вам разобраться в проблеме:
Это не идеальный тест, но он часто работает.
Лучший метод использует регулярные выражения:
Следуя следствию в комментариях, это, по-видимому, является разницей в настройке NLS между прямым SQL-клиентом и ADODB-соединением. Поскольку вы храните число в виде строки (которая всегда является плохим идеей), а число имеет десятичный разделитель, разные настройки NLS_NUMERIC_CHARACTERS вызывают ошибку в одном месте, но не в другом.
Oracle использует этот параметр NLS при неявном преобразовании из строки в число или если у вас есть явный to_number() без маски формата. Поэтому в любой среде одно из этих операторов будет работать, а другое получит ошибку ORA-01722:
Который не работает, зависит от настройки NLS. Вы можете использовать явную маску формата, чтобы избежать этого, и третий параметр to_number() чтобы сказать, как его интерпретировать:
Это использует маркер разделителя D дециметров, который соответствует первому символу в переопределении NLS — обратите внимание, что переключатель периода и запятой между этими двумя операторами — и что переопределение NLS должно быть установлено в соответствии с вашими фактическими данными. Вам также необходимо использовать соответствующие цифры цифр до и после десятичной точки.
В предположении, что вы имеете дело с десятичными градусами, вы должны иметь возможность использовать:
Вам действительно нужно только два девятки перед десятичной точкой для широты, но вам понадобятся три по долготе, поэтому вы также можете быть последовательными.
Но вам также нужно сделать это явное преобразование везде, где вы ссылаетесь на него, например:
Этого можно было бы избежать, если бы вы использовали правильный тип данных в первую очередь. Если вы не можете изменить тип столбца, вы можете подумать о добавлении виртуального столбца, который делает для вас такое же преобразование, или если этому не разрешено представление, которое изменяет тип.
Более ранний ответ дал вариацию Гордону Линоффу, но оказался красной селедкой.
14 Answers 14
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).
Also notice that manually complete a field with "(null)" will give you that error. If the defaul is null and you don't complete it will auto-complete with (null) but it is not the same when you type it.
Eh, "the string cannot be converted into a number" - It is not the point. The String CAN be converted to number. The point here is the opposite conversion of a number to a String column, but Oracle does not do that automatically: You have to make it a String yourself, marking the value explicitly in your SQL expression, to surround it by ' ' OR " ".
To help troubleshoot (to narrow down which particular value in your SQL statement it is that the error refers to), it can be helpful to note the character position (if that is also outputted in the error message). For example, Position: 100 means the problematic value is 100 characters from the beginning of the attempted SQL statement.
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.
this goes to original OP - your table column cant be of type "number" if you want to store values like '0419 853 694' because number cannot have leading zeroes. in my case however this is just what i needed, ty gmlacrosse!
Well it also can be :
where for concatenation in oracle is used the operator || not + .
In this case you get : ORA-01722: invalid number .
Thats a good one. Another poisonous destiny may be this. You commented out a field in a SELECT Fieldlist like -- t.fieldname. Later you intend to remove that line start comment, but one commented character is left by accident. Thus there is: - t.fieldname. This happened to me, when I was working on a very large query, where I have to comment/uncommment hundreds of colums, while reorganizing the beast.
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.
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.
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';
i encountered a similar situation. ora-01722 invalid-number was triggered at a select statement, not an insert statement. I checked the table definition and found that the column was a varchar instead of number, so i added single quotes around the number
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;
Статус
Корпорация Майкрософт подтверждает наличие этой проблемы в своих продуктах, которые перечислены в разделе "Применяется к".
Сведения о накопительном пакете обновления
SQL Server 2008 R2
Исправление для этой проблемы впервые выпущено в накопительном обновлении 9. Для получения дополнительных сведений о том, как получить этот накопительный пакет обновления для SQL Server 2008 R2, щелкните следующий номер статьи базы знаний Майкрософт:
2567713 Накопительный пакет обновления 9 для SQL Server 2008 R2 Примечание. Поскольку сборки являются кумулятивными, каждый новый выпуск исправлений содержит все исправления и все исправления безопасности, которые были включены в предыдущий выпуск исправлений для SQL Server 2008 R2. Рекомендуется установить последнюю версию исправления, которая включает это исправление. Дополнительные сведения см. в следующей статье базы знаний Майкрософт:
981356 Сборки SQL Server 2008 R2, выпущенные после выпуска SQL Server 2008 R2
SQL Server 2008 R2 с пакетом обновления 1 (SP1)
Исправление для этой проблемы впервые выпущено в накопительном обновлении 2 для SQL Server 2008 R2 с пакетом обновления 1 (SP1). Для получения дополнительных сведений о том, как получить этот накопительный пакет обновления, щелкните следующий номер статьи базы знаний Майкрософт:
2567714 Накопительный пакет обновления 2 для SQL Server 2008 R2 с пакетом обновления 1 (SP1)Примечание. Поскольку сборки являются кумулятивными, каждый новый выпуск исправлений содержит все исправления и все исправления безопасности, которые были включены в предыдущий выпуск исправлений для SQL Server 2008 R2. Рекомендуется установить последнюю версию исправления, которая включает это исправление. Дополнительные сведения см. в следующей статье базы знаний Майкрософт:
2567616 Сборки SQL Server 2008 R2, выпущенные после выпуска SQL Server 2008 R2 с пакетом обновления 1 (SP1)
SQL Server 2008 с пакетом обновления 2
Исправление для этой проблемы впервые выпущено в накопительном обновлении 4. Для получения дополнительных сведений о том, как получить этот накопительный пакет обновления для SQL Server 2008 с пакетом обновления 2 (SP2), щелкните следующий номер статьи базы знаний Майкрософт:
2527180 Накопительный пакет обновления 4 для SQL Server 2008 с пакетом обновления 2 (SP2)Примечание. Поскольку сборки являются кумулятивными, каждый новый выпуск исправлений содержит все исправления и все исправления безопасности, которые были включены в предыдущий выпуск исправлений для SQL Server 2008 с пакетом обновления 2 (SP2). Рекомендуется установить последнюю версию исправления, которая включает это исправление. Дополнительные сведения см. в следующей статье базы знаний Майкрософт:
2402659 Сборки SQL Server 2008, выпущенные после выпуска SQL Server 2008 с пакетом обновления 2 (SP2)
Базы данных
ORA-01722: неправильный номер
Дополнительная информация
Дополнительные сведения о публикации Oracle можно найти на веб-сайте MSDN по следующему адресу:
Общие сведения о публикации OracleДополнительные сведения о подписчиках Oracle можно найти на веб-сайте MSDN по следующему адресу:
Общие сведения о подписчиках OracleДополнительные сведения о репликации моментальных снимков можно найти на веб-сайте MSDN по следующему адресу:
I have an issue that is generated randomly (one time between thousandth of calls). The error ORA-01722: invalid number is generated in a random way while executing sql update in a prepared statement Oracle database. The case details are as below:
and the value in log is like that :
by tracing the query parameters at DB all parameters are transferred correctly through JDBC driver except for the parameter 23410984 it was replaced by the value "^X* U" (note this value contains carriage return before char 'u' !). I don't know why
What is the type of the object that you're trying to insert? The toString() returns 23410984, but is it an Integer, Long or BigDecimal?
I faced the same issue with a delete with a IN() clause. Doing a subquery and avoiding putting string values in the query avoid this problem
Usually this happens when we are using IN clause and passing the values as IN ('1, 2, 3, 4, . ') the values should be passed in following way IN ('1','2','3','4'. )
Читайте также: