Литерал не соответствует формату строки оракула

Обновлено: 28.06.2024

У нас есть задание приложения, которое можно запускать на разовой основе. Задание вызывает процедуру pl/sql через сценарий оболочки unix, который, в свою очередь, запускает несколько других процедур.

Одна из этих процедур завершается с ошибкой
Я думаю, это происходит при обновлении столбца даты.

Теперь наступает поразительная часть - когда я шаг за шагом отлаживаю ту же процедуру из Toad. Я не получаю никаких ошибок

Что не так? это как-то связано с nls_date_format?

если нужно, я вставлю сюда код. Пожалуйста, сообщите.

Ответы

Почти всегда значение VARCHAR2 должно быть явно преобразовано в дату, но это не так: оно либо неявно преобразовано, либо преобразовано без строки надлежащего формата.

Здравствуйте,
Проблема связана с функцией to_date() в вашей процедуре.
Формат функции to_date() должен точно совпадать с форматом, в котором хранятся ваши даты.
Например, если ваши даты хранятся в формате «ГГГГ/ММ/ДД», и если вы попытаетесь to_date(column_name), произойдет ошибка.
Вместо этого следует указать to_date(column_name, 'ГГГГ/ММ/ДД').
Также проверьте тип данных, в котором вы храните даты.

Я бы посоветовал вам увеличить размер обновляемого столбца.

Спасибо за ответы.

Как определить, в каком формате хранится дата в моей таблице?

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

Не обращайте внимания на этих двух парней. Столбцы даты не хранятся в определенном формате; внутренне они хранятся как числа и отображаются в формате, заданном NLS_DATE_FORMAT, который, вероятно, переопределяется в TOAD.

Вы должны быть уверены, что сравниваете даты с датами в предложении where

например. ГДЕ date_inserted > '11/01/2008' неверно, ГДЕ date_inserted > to_date('11/01/2008', 'ДД/ММ/ГГГГ') верно.

или если вы используете переменные связывания (лучший вариант)

ГДЕ date_inserted > to_date('&дата','ДД/ММ/ГГГГ')

аналогично при установке значений столбца:

обновить набор mytable date_inserted = to_date('&date','DD/MM/YYYY')

вставить в mytable (date_inserted) VALUES ( to_date('&date','DD/MM/YYYY') )

Мы все были там раньше. Наконец-то вы готовы выполнить оператор для таблицы, содержащей 20 000 точек данных, и появляется ужасное сообщение ORA. Еще до того, как мыслить рационально, накатывает чувство непреодолимой безотлагательности. Какую ошибку вы могли совершить? Как вы его найдете? Что вы можете сделать?

К счастью, в базах данных Oracle немного проще ориентироваться, а определенный подраздел ошибок Oracle будет настолько просто устранить, что, как только вы восстановите самообладание, ошибка будет исправлена за долю секунды. ORA-01861 — одна из тех простых ошибок, которые замедляют ваше сердцебиение до приемлемого уровня. Все, что вам нужно, — это краткий обзор форматирования Oracle, который мы предоставим вам прямо здесь.

Проблема

В ORA-01861 указывается результат, когда «литерал не соответствует строке формата». Если вы не знакомы с этими терминами, это может показаться немного неясным. Однако, как только вы познакомитесь с двумя терминами, все остальное встанет на свои места.

Литерал в Oracle — это фиксированная конкретная точка данных. Например, в списке имен у вас могут быть такие литералы, как «BRAD» или «CHERIE», известные как символьные литералы. Они будут записаны в одинарных кавычках для идентификации (запомните это позже). Вы также можете использовать числовые литералы; возможно, уникальное количество отпусков по болезни, оставшихся на столе для каждого сотрудника вашей компании, выраженное в общем количестве часов.

И наконец, часто источником ORA-01861 являются литералы Datetime. Они относятся к календарным датам или меткам времени и следуют определенной заданной строке формата. Существует четыре типа литералов даты и времени: DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE и TIMESTAMP WITH LOCAL TIME ZONE. Отметка даты и времени использует григорианский календарь и соответствует формату «ГГГГ-ММ-ДД». TIMESTAMP добавляет к дате выражение времени, следующее за «HH:MM:SS.FFF», где F представляет доли секунды. TIMESTAMP WITH TIME ZONE добавляет «+HH:MM», а TIMESTAMP WITH LOCAL TIME ZONE просто сохраняет данные в часовом поясе локализованной базы данных. Полная строка формата даты/времени/часового пояса будет выглядеть так:

TIMESTAMP ‘ГГГГ-ММ-ДД ЧЧ:ММ:СС.ФФФ +ЧЧ:ММ’

Итак, судя по этому, должно быть довольно ясно, что часто ORA-01861 запускается, когда был введен литерал, который не соответствует строке формата.Возможно, вы ввели литерал даты как «26.06.2015» в таблицу со строкой формата даты «ГГГГ-ММ-ДД». Естественно, ошибка будет выдана соответствующим образом.

Решение

Основной способ борьбы с ошибкой ORA-01861 довольно прост. Когда возникает ошибка, вернитесь к строке, которую Oracle указал как точку ошибки, и отредактируйте литерал так, чтобы он соответствовал строке формата, помня, что символьные литералы требуют кавычек, а литералы даты и времени требуют определенных строк формата. Ниже приведен пример этого решения в действии.

Пример ошибки ORA-01861

ВЫБРАТЬ TO_DATE (‘20140722’, ‘гггг-мм-дд’)
FROM dual;
ОШИБКА ORA-01861: литерал не соответствует строке формата

Пример решения ORA-01861

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

ВЫБЕРИТЕ TO_DATE («2014-07-22», «гггг-мм-дд»)

Это позволит оператору работать гладко и без ошибок. Чем лучше вы разбираетесь в форматировании, тем реже вы будете видеть эту ошибку.

С надеждой

Как вы видите, ошибка ORA-01861 настолько проста, насколько это возможно. Никакого кодирования, никакого глубокого исследования самых темных глубин ваших таблиц, чтобы выследить проблему. Просто убедитесь, что вы соблюдаете единый формат, и все остальное должно стать на свои места. При этом всегда есть исключительные случаи с любой программной системой. Если вы столкнулись с такой ситуацией или у вас есть несколько общих вопросов по более широким темам, таким как строки форматирования, никогда не помешает обратиться к лицензированному консультанту Oracle за дополнительной информацией.

В извлеченном файле CSV будет больше столбцов, но формат значения даты должен быть таким, как указано выше ('ГГГГ-ММ-ДД"T"ЧЧ24:MI:SS.FFTZH:TZM').

Я пытался сделать что-то вроде ниже и получил ошибку:

выберите TO_TIMESTAMP_TZ(last_update_date,'YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM') из test_table;

ORA-01861: литерал не соответствует строке формата

01861. 00000 - "литерал не соответствует строке формата"

*Причина: литералы во входных данных должны быть той же длины, что и литералы во входных данных

строка формата (за исключением начальных пробелов). Если

Включен модификатор "FX", литерал должен точно соответствовать,

без лишних пробелов.

*Действие: Исправьте строку формата, чтобы она соответствовала литералам.

Лучший ответ

Вы можете добавить +02:00, как упомянул Фрэнк, но тогда на основе CURRENT_TIMESTAMP вы можете получить неожиданные результаты. CURRENT_TIMESTAMP — это отметка времени на стороне клиента с часовым поясом, поэтому такой выбор будет отображать разные значения для двух клиентов в разных часовых поясах, вставляя строку одновременно:

Как видите, разница во времени составляет 5 часов, несмотря на то, что две строки были вставлены почти в одно и то же время. Единственный способ получить консистентные результаты — либо изменить тип данных на метку времени с [местным] часовым поясом, либо убедиться, что метка времени всегда вставляется на основе одного и того же часового пояса. Например, создав триггер с помощью

И используйте FROM_TZ + AT TIME ZONE:

Как видите, срабатывает синхронизация часового пояса.

P.S. Очевидно, что триггер не будет обрабатывать уже существующие строки.

Ответы

Всякий раз, когда у вас есть вопрос, опубликуйте небольшой пример данных в удобной форме (например, операторы CREATE TABLE и INSERT), чтобы люди, которые хотят помочь вам, воссоздали проблему и проверили свои идеи. Также опубликуйте точные результаты, которые вы хотите получить из предоставленных данных, и объяснение (в общих чертах), как вы получаете эти результаты из этих данных.

Всегда говорите, какую версию Oracle вы используете (например, 12.2.0.1.0).

Какой тип данных у last_update_date? Если это TIMESTAMP (или DATE), то вам не следует передавать его tp TO_TIMESTAMP_TZ; предполагается, что аргументы TO_TIMESTAMP_TZ являются строками. Вызовите TO_CHAR, чтобы сгенерировать отформатированную строку с учетом TIMESTAMP.

создать таблицу test_table (LAST_UPDATED_DATE TIMESTAMP(6) );

вставить в test_table ( LAST_UPDATED_DATE) значения (CURRENT_TIMESTAMP);

выберите LAST_UPDATED_DATE из таблицы test_table;

Желаемый результат:

Пробовал так:

выберите to_char(LAST_UPDATED_DATE , 'ГГГГ-ММ-ДД"T"ЧЧ24:MI:SS.FFTZH:TZM') из таблицы test_table;

Поразмышляйте над типом данных TIMESTAMP и TIMESTAMP WITH TIME ZONE:

Есть ли способ получить значение в нужном формате без изменения типа данных?

Сообщение об ошибке: "ORA-00942: таблица или представление не существует"; это не имеет ничего общего с форматированием столбца TIMESTAMP. Это может означать, что таблица не существует (включая случаи, когда вам нужно уточнить имя таблицы с помощью имени схемы) или что у вас нет прав доступа к таблице.

Прочитайте ответ Соломона выше. Данные TIMESTAMP (не TIMESTAMP WITH TIME ZONE) не содержат информации о часовом поясе, поэтому с ними можно использовать спецификаторы формата TZH и TZM. Если вы хотите отображать все свои временные метки с «+02:00» в качестве часового пояса, вы можете жестко закодировать «+02:00» в выходных данных, точно так же, как вы жестко кодируете «T». Например:

И используйте FROM_TZ + AT TIME ZONE:

Как видите, срабатывает синхронизация часового пояса.

P.S. Очевидно, что триггер не будет обрабатывать уже существующие строки.

current_timestamp — это временная метка с часовым поясом. Вы конвертируете его в метку времени, теряя информацию о часовом поясе при сохранении в своей таблице.

Что нормально, если часовой пояс всегда один и тот же. Для вывода вы говорите, что хотите показать +02:00. Вы уверены, что это правильный часовой пояс для всех дат в вашем регионе? Вы находитесь в месте, где не используется летнее время? +02:00, вероятно, означает где-то в Центральной Европе или Восточной Африке; если это Центральная Европа, то почти наверняка наблюдается летнее время, поэтому current_timestamp может иметь часовой пояс +02:00 сейчас (летом), но +01:00 зимой.

Я бы предпочел сделать это следующим образом, предполагая, что ваш часовой пояс — Европа/Париж, например:

Если вы не знаете, какой регион часового пояса использовать, вы можете попробовать выполнить этот небольшой запрос (прежде чем делать что-либо еще — просто запишите, что он сообщает):

Не пытайтесь поместить значение VARCHAR2 в переменную DATE.

Что вы хотите, чтобы эта процедура делала?

Что бы это ни было, держу пари, двойной стол вам не понадобится. Dual не так уж нужен в PL/SQL.

Еще одна вещь, которая не особо нужна в PL/SQL, — это обработчик EXCEPTION, особенно при разработке и отладке. Все, что делает ваш обработчик EXCEPTION, — это скрывает информацию, которая может помочь вам решить проблемы.

Привет!
мы имеем нижеприведенную процедуру, и когда мы пытаемся получить дату в качестве внешнего параметра, мы получаем ошибку "ORA-01861: literaldoesnotmatchformatstring",
CREATEORREPLACEPROCEDUREtest_proc(p_dateOUTDATE)
КАК
НАЧАТЬ
ВЫБРАТЬ
TO_CHAR(
SYSDATE,
'гггг-мм-дд'
)
ДО
дата_даты
ОТ
двойной;
dbms_output.put_line('Дата:'||p_date);
/*ИСКЛЮЧЕНИЕ
КОГДА НИЧЕГО
p_date:=NULL;*/
КОНЕЦ;
Ошибка ниже,
ЗАЯВИТЬ
p_dateDATE;
НАЧАТЬ
test_proc(p_date);
КОНЕЦ;
Отчет об ошибке-
ORA-01861:литерал не соответствует строке формата
ORA-06512:at"XXCCT.TEST_PROC",строка 4
ORA-06512:atline4
01861.00000-"литерал не соответствует строке формата"
*Причина: литералы во входных данных должны иметь ту же длину, что и литералы во входных данных.
строка формата (за исключением начального пробела). Если
Модификатор "FX" был включен, литерал должен точно совпадать,
без лишних пробелов.
*Действие: исправьте строку формата в соответствии с литералом.
Пожалуйста, предложите.
Спасибо.

Вы конвертируете sysdate (дату) в строку символов с явной маской формата, а затем неявно конвертируете ее обратно в дату (с использованием неизвестной маски формата).

Вам не нужно делать никаких преобразований, sysdate уже является датой. Если вы хотите отобразить его в виде строки в определенном формате, сделайте это в строке dbms_output.

Я предлагаю вам полностью удалить эту обработку исключений, а закомментировать ее просто означает, что вы вернете ее в какой-то момент. ЭТО ОШИБКА.

Читайте также: