Содержание
Сервер переехал на новый сайт 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: неправильный номер
Причина:
Пробное преобразование символьной строки в числовую провалилось, потому что символьная строка не была правильной числовой литерой. Только числовые поля или символьные поля содержащие числовые данные могут быть использованы в арифметических функциях или выражениях. Только числовые поля могут быть добавлены или вычтены из данных.
Действие:
Проверьте символьные строки в функции или в выражении; убедитесь в том, что они содержат только числа, знаки, десятичные точки, и символ «E» или «e», затем повторите операцию.