FoxClub Forums > Не фоксом единым > Oracle. Coalesce и bind-переменные ::

• Войти

Oracle. Coalesce и bind-переменные
Pekpytep Сообщений: 727 Откуда: Луганск Дата регистрации: 19.10.2010	Pekpytep 05.10.17 21:55:14 Всем привет. Отлаживал давеча запрос и наткнулся на странное поведение функции coalesce with tmp as ( select 5 as f1 from dual ) -- так работает select coalesce(f1, 1, &v_parameter) from tmp; -- а вот так - несовпадение типов select coalesce(f1, 1, :v_parameter) from tmp; Кажется я чего-то недопонимаю в работе этой функции. Это баг или фича?

Pekpytep

Сообщений: 727
Откуда: Луганск
Дата регистрации: 19.10.2010

Pekpytep

05.10.17 21:55:14

Всем привет.
Отлаживал давеча запрос и наткнулся на странное поведение функции coalesce


with
  tmp as
  (
  select
    5 as f1
  from dual
  )
-- так работает
select
  coalesce(f1, 1, &v_parameter)
from tmp;

-- а вот так - несовпадение типов
select
  coalesce(f1, 1, :v_parameter)
from tmp;

Кажется я чего-то недопонимаю в работе этой функции. Это баг или фича?

Re: Oracle. Coalesce и bind-переменные
Igor Korolyov Автор Сообщений: 34580 Дата регистрации: 28.05.2002	Igor Korolyov 06.10.17 16:48:30 Начнём с того что сама функция Coalesce тут совершенно не при чём Подставляемая переменная (та что "макро", она же Substitution Variable) это просто вставляемые в команду символы (при том это всегда строка). Похоже на фокс - там тоже "прокатит" если в то место где нужно число через макро послать строку с цифрами. Связываемая переменная (Bind Variable) - это ЗНАЧЕНИЕ которое посылается на сервер отдельно от "текста запроса". При этом у такой переменной есть тип. Естественно что если сделать переменную строковой, и при том попытаться её использовать в том месте где ожидается число, то возникнет ошибка. Переводя на фокс, ты пытаешься сделать следующее: var1="2" ? MAX(1, &var1) && и оно работает ? MAX(1, m.var1) && и оно ругается на неверный тип. Я не знаю из какой именно среды ты собираешься исполнять данный запрос, потому не могу точно ответить как надо писать такой код Просто общее соображение - для bind variable нужно указывать тип. P.S. Substitution Variable вообще-то работает в sql*plus и системах так или иначе (не всегда 100% корректно, к слову) эмулирующих этот самый "плюс". Это как бы "фишка" данной утилиты. А для них bind variable задаётся командой variable имя_переменной тип_переменной. Конечно же в каком toad при исполнении скрипта или отдельной команды он свой диалог ввода значения предложит - но и там будет поле для "типа". ------------------ WBR, Igor

Re: Oracle. Coalesce и bind-переменные

Igor Korolyov
Автор

Сообщений: 34580
Дата регистрации: 28.05.2002

Igor Korolyov

06.10.17 16:48:30

Начнём с того что сама функция Coalesce тут совершенно не при чём

Подставляемая переменная (та что "макро", она же Substitution Variable) это просто вставляемые в команду символы (при том это всегда строка). Похоже на фокс - там тоже "прокатит" если в то место где нужно число через макро послать строку с цифрами.

Связываемая переменная (Bind Variable) - это ЗНАЧЕНИЕ которое посылается на сервер отдельно от "текста запроса". При этом у такой переменной есть тип. Естественно что если сделать переменную строковой, и при том попытаться её использовать в том месте где ожидается число, то возникнет ошибка.
Переводя на фокс, ты пытаешься сделать следующее:

var1="2"
? MAX(1, &var1) && и оно работает
? MAX(1, m.var1) && и оно ругается на неверный тип.

Я не знаю из какой именно среды ты собираешься исполнять данный запрос, потому не могу точно ответить как надо писать такой код

Просто общее соображение - для bind variable нужно указывать тип.

P.S. Substitution Variable вообще-то работает в sql*plus и системах так или иначе (не всегда 100% корректно, к слову) эмулирующих этот самый "плюс". Это как бы "фишка" данной утилиты. А для них bind variable задаётся командой variable имя_переменной тип_переменной. Конечно же в каком toad при исполнении скрипта или отдельной команды он свой диалог ввода значения предложит - но и там будет поле для "типа".

------------------
WBR, Igor

Re: Oracle. Coalesce и bind-переменные
Pekpytep Сообщений: 727 Откуда: Луганск Дата регистрации: 19.10.2010	Pekpytep 06.10.17 19:48:57 Наверное, меня бы устроил этот ответ, если бы не одно "но"... Все остальные функции совершенно нормально обрабатывают бинд-переменные, кроме coalesce, потому и возник этот вопрос. nvl(f1, :v_parameter), равно как и nvl2(f1, 1, :v_parameter) совершенно нормально отрабатывают, в coalesce же всегда падает в исключение независимо от того, присвоить ли переменной null, 2 или '2'. Я, конечно, подозревал, что дело в неявном преобразовании типов, но почему только в этой функции? Если это важно, то запрос отлаживался в SQL Developer 17.3.0.271.2323 В понедельник попробую проверить в TOAD , если не забуду. )

Re: Oracle. Coalesce и bind-переменные

Pekpytep

Сообщений: 727
Откуда: Луганск
Дата регистрации: 19.10.2010

Pekpytep

06.10.17 19:48:57

Наверное, меня бы устроил этот ответ, если бы не одно "но"...
Все остальные функции совершенно нормально обрабатывают бинд-переменные, кроме coalesce, потому и возник этот вопрос.
nvl(f1, :v_parameter), равно как и nvl2(f1, 1, :v_parameter) совершенно нормально отрабатывают, в coalesce же всегда падает в исключение независимо от того, присвоить ли переменной null, 2 или '2'. Я, конечно, подозревал, что дело в неявном преобразовании типов, но почему только в этой функции?
Если это важно, то запрос отлаживался в SQL Developer 17.3.0.271.2323
В понедельник попробую проверить в TOAD , если не забуду. )

Re: Oracle. Coalesce и bind-переменные
Pekpytep Сообщений: 727 Откуда: Луганск Дата регистрации: 19.10.2010	Pekpytep 09.10.17 09:58:06 Эксперименты показали следующее: 1. При выполнении запроса в TOAD 12.7.1.11 из стартового сообщения получаем то же самое исключение - ORA-00932: несовместимые типы данных: ожидается NUMBER, получено CHAR 2. Предварительное декларирование переменной с указанием типа помогает только в случае, если переменная не равна null declare v_var number; v_out number; begin with tmp as ( select 1 f1 from dual) select coalesce(f1, 2, :v_var) into v_out from tmp; dbms_output.put_line(v_out); end; Точно такое же поведение получаем, если задекларировать переменную v_var как varchar2. 3. При явном преобразовании типов запрос отрабатывает ожидаемо with tmp as ( select 1 f1 from dual) select coalesce(f1, to_number(:v_var)) from tmp; 4. С функциями nvl() и nvl2() таких проблем не наблюдается. Склоняюсь к мнению, что это все-таки баг обработки значений null

Re: Oracle. Coalesce и bind-переменные

Pekpytep

Сообщений: 727
Откуда: Луганск
Дата регистрации: 19.10.2010

Pekpytep

09.10.17 09:58:06

Эксперименты показали следующее:

1. При выполнении запроса в TOAD 12.7.1.11 из стартового сообщения получаем то же самое исключение - ORA-00932: несовместимые типы данных: ожидается NUMBER, получено CHAR

2. Предварительное декларирование переменной с указанием типа помогает только в случае, если переменная не равна null


declare 
  v_var number;
  v_out number;

begin
  with
    tmp as
    (
    select
        1 f1
    from dual)

  select coalesce(f1, 2, :v_var) into v_out from tmp;
  
  dbms_output.put_line(v_out);
end;

Точно такое же поведение получаем, если задекларировать переменную v_var как varchar2.

3. При явном преобразовании типов запрос отрабатывает ожидаемо


with
  tmp as
  (
  select
      1 f1
  from dual)

select coalesce(f1, to_number(:v_var)) from tmp;

4. С функциями nvl() и nvl2() таких проблем не наблюдается.

Склоняюсь к мнению, что это все-таки баг обработки значений null

Re: Oracle. Coalesce и bind-переменные
Igor Korolyov Автор Сообщений: 34580 Дата регистрации: 28.05.2002	Igor Korolyov 09.10.17 15:27:57 variable v_var number select coalesce(1, :v_var) from dual; При запуске такого sql файла из sqlplus всё работает. TOAD не может адекватно исполнить такой скрипт (его реализация не совпадает с реализацией sqlplus). Однако если оставить только select, то он выводит диалог, и если в нём указать числовой тип, то всё тоже работает как положено. declare v_var number; ... не имеет никакого отношения к хост-переменной :v_var Если писать анонимный блок со своими переменными, то тоже всё работает (независимо от того задано значение или нет - главное что тип задан). declare v_var number; v_out number; begin select coalesce(null, 2, v_var) into v_out from dual; dbms_output.put_line(v_out); end; / Да, у coalesce, наверное, иной принцип "вывода типа результата" чем у nvl - но это вовсе не значит что она "не работает". Для NVL() тоже вполне можно привести пример когда она не работает, если её кормить разнотипными параметрами. Тривиальный: DECLARE V1 NUMBER; V2 VARCHAR2(20); V_RES VARCHAR2(20); BEGIN --V2 := 2; V2 := '2a'; SELECT TO_CHAR(nvl(V1, V2)) INTO V_RES FROM DUAL; DBMS_OUTPUT.PUT_LINE(V_RES); END; / Если невнимательный разработчик будет использовать v2 как число (несмотря на явное объявление), то вроде бы "всё работает", тогда как на самом деле совсем даже и нет, только увидеть это сложно ------------------ WBR, Igor

Re: Oracle. Coalesce и bind-переменные

Igor Korolyov
Автор

Сообщений: 34580
Дата регистрации: 28.05.2002

Igor Korolyov

09.10.17 15:27:57

variable v_var number

select coalesce(1, :v_var) from dual;

При запуске такого sql файла из sqlplus всё работает.

TOAD не может адекватно исполнить такой скрипт (его реализация не совпадает с реализацией sqlplus). Однако если оставить только select, то он выводит диалог, и если в нём указать числовой тип, то всё тоже работает как положено.

declare
v_var number;
...

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


declare
  v_var number;
  v_out number;
begin
  select coalesce(null, 2, v_var) into v_out from dual;
  dbms_output.put_line(v_out);
end;
/

Да, у coalesce, наверное, иной принцип "вывода типа результата" чем у nvl - но это вовсе не значит что она "не работает". Для NVL() тоже вполне можно привести пример когда она не работает, если её кормить разнотипными параметрами.
Тривиальный:


DECLARE
 V1    NUMBER;
 V2    VARCHAR2(20);
 V_RES VARCHAR2(20);
BEGIN
 --V2 := 2;
 V2 := '2a';
 SELECT TO_CHAR(nvl(V1, V2)) INTO V_RES FROM DUAL;
 DBMS_OUTPUT.PUT_LINE(V_RES);
END;
/

Если невнимательный разработчик будет использовать v2 как число (несмотря на явное объявление), то вроде бы "всё работает", тогда как на самом деле совсем даже и нет, только увидеть это сложно

------------------
WBR, Igor

:: Список Форумов•Список тем•Поиск•Войти

Извините, только зарегистрированные пользователи могут оставлять сообщения в этом форуме.

On-line: 2 alextash Guest