Как использовать переменные связывания для литерала DATE?

Question

Есть такой запрос:

SELECT * FROM employee WHERE dob = DATE '1980-05-15'

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

SELECT * FROM employee WHERE dob = DATE :dateOfBirth

Однако этот запрос не работает:

ORA-00936: missing expression

Перепробовал все возможные форматы (1980-05-15, 15-MAY-80, 15-MAY-1980) для переменной :dateOfBirth, но безуспешно, всегда получаю одну и ту же ошибку.

PS1: Знаю о функции to_date(), которая может решить эту проблему, но я не могу ее использовать, потому что запрос формируется в другой системe, в которой нет возможности что-то изменить.

PS2: Формат даты nls_date_format=DD-MON-RR в БД, как указано в параметрах nls_session_parameters и nls_database_parameters.

_{Свободный перевод вопроса How to use bind variables for date literals? от участника @cdoe}

Answer 1

Правильное решение:

SELECT * FROM employee WHERE dob = :dateOfBirth

Причина ошибки:

Литерал даты DATE '1980-05-15'это законченая синтаксическая конструкция, которую нельзя поделить на части, то есть она не может быть результатом выражения из нескольких операндов. Такое работать не будет:

select date ''||'1980-05-15' from dual
/
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

Парсер SQL, получая текст запроса, видит в этом фрагменте DATE :dateOfBirth два токена:

DATE, после которго он ожидает символьный литерал, отсутствие которого и приводит к ошибке
:dateOfBirth, переменные связывания он пропускает, что в них будет на этапе выполнения, ни тип данных, ни значение - ему неизвестно.

Что должно быть в переменной связывания, раз литерал даты с ней не возможен?

Определение и присвоение значения переменной связывания - задача клиентского приложения. Идеально использовать типы даты храняшие значение в бинарном виде. В любом ЯП есть соответствующие типы данных. Например, в JDBC переменная типа java.time будет передана в том же виде, в котором SQL DATE хранит своё значение:

select rawtohex (date'1980-05-15') hexvalue from dual
/
HEXVALUE
----------------
BC07050F00000000

Если по каким либо причинам возможен только символьный тип для переменной связывания, то обязательно надо изменить запрос на использование to_date() с явным указанием формата:

SELECT * FROM employee WHERE dob = to_date (:dateOfBirth, 'dd.mm.yyyy');

БЛОГ НА HUSL

Как использовать переменные связывания для литерала DATE?

Ответы (1 шт):