CODEWITHSUNDEEP
sqloracle-databasedynamicbind-variables
dbspecialist
dbspecialist

Reputation: 13

Bind variable with single quotes in Oracle dynamic sql

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;

Upvotes: 1

Views: 1691

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59486

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.

Upvotes: 1

Marmite Bomber
Marmite Bomber

Reputation: 21075

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

Upvotes: 1

Related Questions