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, Sep 15 '19 at 09: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, Sep 15 '19 at 09: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, Sep 15 '19 at 14:04

Wernfried Domscheit · Answer 1 · 2019-09-17T13:19:48.913

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.

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.. — dbspecialist, Sep 15 '19 at 10:00
The first two statements are out of syntax (the `using` comes [after the](https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/executeimmediate_statement.htm#LNPLS01317) `into` clause). After fix you get `ORA-19102: XQuery string literal expected`. — Marmite Bomber, Sep 17 '19 at 11:55

score 1 · Answer 2 · answered Sep 17 '19 at 11:53

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

Bind variable with single quotes in Oracle dynamic sql

2 Answers2