Привязать переменную с одинарными кавычками в Oracle dynamic sql

Я получаю разные версии файла xml, каждый из которых определен в другом пространстве имен. Поэтому мне нужно использовать пространство имен в качестве переменной привязки в моем динамическом запросе.

В Oracle 12.1 оператор хорошо работает с жестко заданным пространством имен: XMLNAMESPACES (DEFAULT 'http://www.ff.org').

Если я попытаюсь заменить строку переменной связывания, она выдаст ошибку из-за отсутствия одинарных кавычек. Я уже пытался добавить одинарные кавычки при привязке переменной в «немедленном выполнении» или добавлении кавычек в динамический запрос, включающий переменную привязки: sNamespace, ни один из них не сработал.

declare 
  sNamespace varchar2(100);
  sXMLVersion  VARCHAR2(3);
  sstmt varchar2(1000);
begin
  sNamespace := 'http://www.ff.org';

  sstmt := q'#
   with t_base as (select xmltype('<froot xmlns="http://www.ff.org">
                                     </froot>') as xml from dual)
   SELECT  case when v.is_root = 1 then '1' 
                else '2' end       
    into :sXMLVersion
           FROM t_base t, 
           XMLTABLE(XMLNAMESPACES(DEFAULT :sNamespace),
                 '$d' passing t.xml as "d"
                     columns 
                       is_root INTEGER           PATH 'exists(froot)'
                 ) v

  #';
    execute immediate sstmt using sNamespace into sXMLVersion;
    dbms_output.put_line(sXMLVersion);   
end;

sql dynamic oracle bind-variables
person dbspecialist    schedule 15.09.2019    source источник
comment
Изменить XMLTABLE(XMLNAMESPACES(DEFAULT :sNamespace) на XMLTABLE(XMLNAMESPACES(DEFAULT ':sNamespace')   -  person Popeye    schedule 15.09.2019
comment
К сожалению, это не правильное решение, так как теперь выдается ORA-01006 Переменная привязки не существует. Пожалуйста, предоставьте рабочий код только в качестве предложения решения.   -  person dbspecialist    schedule 15.09.2019
comment
Одной из основных проблем с вашим заявлением является sstmt := 'SELECT ... INTO :sXMLVersion FROM ...', это неправильно, должно быть sstmt := 'SELECT ... FROM ...'; execute immediate sstmt INTO sXMLVersion   -  person Wernfried Domscheit    schedule 15.09.2019

Ответы (2)


arrow_upward
1
arrow_downward

Проблема с использованием переменной связывания в пространстве имен DEFAULT

 XMLTABLE(XMLNAMESPACES(DEFAULT :sNamespace)

это то, что вы получаете ошибку ORA-19102: XQuery string literal expected

Это говорит о том, что в качестве пространства имен ожидаются только литералы, что снова предполагает, что вы должны динамически настраивать оператор SQL.

Лучший способ, который я могу себе представить, - это две строки в q-кавычках следующим образом

declare 
  sNamespace varchar2(100) := 'http://www.ff.org';
  sIsRoot number;
  sstmt varchar2(1000) := q'#with t_base as (select xmltype('<froot xmlns="http://www.ff.org">
                                     </froot>') as xml from dual)
   SELECT  v.is_root    
           FROM t_base t, 
           XMLTABLE(XMLNAMESPACES(DEFAULT '#'||sNamespace||q'#'),
                 '$d' passing t.xml as "d"
                     columns 
                       is_root INTEGER           PATH 'exists(froot)'
                 ) v#';
begin  
    execute immediate sstmt  into sIsRoot;
    dbms_output.put_line(sIsRoot);   
end;
/
person Marmite Bomber    schedule 17.09.2019

arrow_upward
1
arrow_downward

Почему вы используете with t_base as (select xmltype(...) as xml from dual)? Я думаю, вы можете использовать его напрямую. Вы не можете привязать пространство имен XML, поскольку оно не является переменной. Вы можете использовать один из них:

DECLARE
    sstmt  VARCHAR2(10000);
    sNamespace VARCHAR2(100);
    res INTEGER;
BEGIN

    sNamespace := 'http://www.ff.org';

    sstmt  := q'# 
    SELECT 
        CASE WHEN is_root = 1 THEN 1 
        ELSE 2 END       
    FROM 
        XMLTABLE(
            XMLNAMESPACES(DEFAULT '#'||sNamespace||'''),'||
    q'#'), 
            '$d' PASSING :x as "d" COLUMNS 
            is_root INTEGER PATH 'exists(froot)'
        ) x 
    #';

    DBMS_OUTPUT.PUT_LINE(sstmt);

    EXECUTE IMMEDIATE sstmt INTO res USING XMLTYPE('<froot xmlns="http://www.ff.org"></froot>');
    DBMS_OUTPUT.PUT_LINE ( 'res = ' || res );


    sstmt  := q'# 
    SELECT 
        CASE WHEN is_root = 1 THEN 1 
        ELSE 2 END       
    FROM 
        XMLTABLE(
            XMLNAMESPACES(DEFAULT '#'||sNamespace||'''),'||
    q'# 
            '$d' PASSING XMLTYPE(:x) as "d" COLUMNS 
            is_root INTEGER PATH 'exists(froot)'
        ) x 
    #';

    DBMS_OUTPUT.PUT_LINE(sstmt);

    EXECUTE IMMEDIATE sstmt INTO res USING '<froot xmlns="http://www.ff.org"></froot>';
    DBMS_OUTPUT.PUT_LINE ( 'res = ' || res );



END;

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

person Wernfried Domscheit    schedule 15.09.2019
comment
Спасибо за ответ. Опубликованный код - это лишь небольшая часть огромного оператора, просто для демонстрационных целей я проанализировал код xml в подзапросе t_base, конечно, его также можно связать с помощью оператора execute. Я не могу использовать обычную строку concat, так как мой sql содержит автоматически сгенерированный код с большим количеством одинарных кавычек. Вот почему я предпочитаю использовать специальные кавычки q'# для создания динамического кода. Проблема здесь заключается в привязке переменной пространства имен, остальная часть кода, безусловно, может быть изменена по-разному. - person dbspecialist; 15.09.2019
comment
Синтаксис первых двух операторов не соответствует синтаксису (using приходит после предложения into). После исправления вы получите ORA-19102: XQuery string literal expected. - person Marmite Bomber; 17.09.2019