Bind variable with single quotes in Oracle dynamic sql

Question

I'm receiving different versions of an xml file each defined in different namespace. So i need to use the namespace as bind variable in my dynamic query.

On Oracle 12.1, the statement runs well with hardcoded namespace: XMLNAMESPACES (DEFAULT 'http://www.ff.org').

If i try to replace the string with a bind variable, it throws an error due to missing single quotes. I've already tried to add the single quotes when binding the variable in "execute immediate" or adding quotes in dynamic query enclosing the bind variable :sNamespace, non of them worked.

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;

Change XMLTABLE(XMLNAMESPACES(DEFAULT :sNamespace) to XMLTABLE(XMLNAMESPACES(DEFAULT ':sNamespace') — Popeye
– Popeye, Commented Sep 15, 2019 at 9:13
Unfortunately this is not the correct solution, as it throws now ORA-01006 Bind variable does not exist. Please only provide working code as solution proposal. — dbspecialist
– dbspecialist, Commented Sep 15, 2019 at 9:39
One major issue with your statement is sstmt := 'SELECT ... INTO :sXMLVersion FROM ...', That's wrong, it must be sstmt := 'SELECT ... FROM ...'; execute immediate sstmt INTO sXMLVersion — Wernfried Domscheit
– Wernfried Domscheit, Commented Sep 15, 2019 at 14:04

Marmite Bomber · Accepted Answer · 2019-09-17 11:53:05Z

The problem with using the bind variable in DEFAULT namespace

 XMLTABLE(XMLNAMESPACES(DEFAULT :sNamespace)

is that you get an error ORA-19102: XQuery string literal expected

That suggests, only literals are expected as namespace, which again suggest you must dynamicaly set up the SQL statement.

The best way I can immagine would be to you two q-quoted strings as follows

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;
/

Wernfried Domscheit · Accepted Answer · 2019-09-17 13:19:48Z

1

Why do you use with t_base as (select xmltype(...) as xml from dual)? I think you can use it directly. You cannot bind XML namespace as it is not a variable. You can use one of these:

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;

This should be fine because the namespace usually does not contain any quotes.

edited Sep 17, 2019 at 13:19

answered Sep 15, 2019 at 9:47

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

2 Comments

dbspecialist Over a year ago

Thx for the reply. The posted code is just a small portion of a huge statement, just for demo purposes i've parsed the xml code within the subquery t_base, surely it can be also bound via the execute statement. I can't use the normal string concat, as my sql contains auto generated code with lots of single quotes. This is why i prefer using the special quoting q'# to build up the dynamic code.The issue here is binding the namespace variable, the rest of the code surely can be changed in diffrent ways..

Marmite Bomber Over a year ago

The first two statements are out of syntax (the using comes after the into clause). After fix you get ORA-19102: XQuery string literal expected.

Collectives™ on Stack Overflow

Bind variable with single quotes in Oracle dynamic sql

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related