Help - Search - Members - Calendar
Full Version: DBMS_XMLQUERY vs DBMS_XMLGEN
Oracle DBA Forums > Oracle > Oracle Forum
Randy Strommen
Well, I wanted to pass along what I learned with regards to these two utilities.

Issue Summary
I am creating a webservice to return nested XML data. The data contains a special character that prevented IE 6.0 from displaying the XML output.
CODE
   <Plant num="4">
      <TypeCode>PFL</TypeCode>
      <TypeDesc>Plant</TypeDesc>
      <Code>ABAXA</Code>
      <PrefScientificName>Arracacia xanthorrhiza</PrefScientificName>
      <PrefCommonName>Arracacha</PrefCommonName>
      <CommonNames>
         <CommonNames_ROW num="3">
            <Language>French</Language>
            <CommonName>Pomme de terre cÚleri</CommonName>
         </CommonNames_ROW>
      </CommonNames>
   </Plant>


By default, XMLGEN generates an xml header <?xml version = '1.0'?>
This generates the IE 6.0 error because of the accent marked e in cÚleri.
By converting to DBMS_XMLQUERY, I can set the XML header to our company standard encoding <?xml version = '1.0' encoding = 'ISO-8859-1'?>, and the webservice works just fine.

The code is almost identical. The irrelavant portions are snipped out here.
CODE
    qryCtx := DBMS_XMLQuery.newContext(v_prd_query);
    --
    --  Set the encoding tag to handle Unicode
    --
    DBMS_XMLQuery.setEncodingTag(qryCtx, 'ISO-8859-1');
    DBMS_XMLQuery.setRowsetTag(qryCtx, 'root');
    DBMS_XMLQuery.setRowTag(qryCtx, v_bayer_group_tag);
    --
    --  Set up the bind variables.
    --  Note: DBMS_XMLQuery cannot handle a bind variable occurring more than once.
    --
    DBMS_XMLQuery.setbindvalue (qryCtx, 'cp_bayer_group', UPPER(i_bayer_group));
    DBMS_XMLQuery.setbindvalue (qryCtx, 'cp_filter1',      UPPER(i_filter));
    DBMS_XMLQuery.setbindvalue (qryCtx, 'cp_filter2',      UPPER(i_filter));
    DBMS_XMLQuery.setbindvalue (qryCtx, 'cp_filter3',      UPPER(i_filter));
    --
    --  Execute the query and put the results into the CLOB
    --
    o_results := DBMS_XMLQuery.getXML(qryCtx);
    --
    --  Close the context
    --
    DBMS_OUTPUT.PUT_LINE('Close Context');
    DBMS_XMLQuery.closeContext(qryCtx);


As you can see from the comment, I learned that multiple occurences of a bind variable does not work in DBMS_XMLQUERY. You receive the followinig error.
oracle.xml.sql.OracleXMLSQLException: Missing IN or OUT parameter at index:: 2
Where the index refers to which occurence of a bind variable (starting at 0) failed. In this case, it was the third bind_variable occurence that failed.

CODE
      || 'FROM   sde_bayer_code_pref_names_v bc' || CHR(10)
      || '      ,(SELECT bc2.type_code    AS type_code' || CHR(10)
      || '              ,bc2.code_seq_id  AS code_seq_id' || CHR(10)
      || '        FROM   sde_bayer_code_pref_names_v bc2' || CHR(10)
      || '        WHERE  UPPER(bc2.code) like UPPER(:cp_filter1)' || CHR(10)
      || '        UNION' || CHR(10)
      || '        SELECT sn2.type_code    AS type_code' || CHR(10)
      || '              ,sn2.code_seq_id  AS code_seq_id' || CHR(10)
      || '        FROM   sde_bayer_code_sci_names_v sn2' || CHR(10)
      || '        WHERE  UPPER(sn2.short_name) like UPPER(:cp_filter1  || :cp_filter2)' || CHR(10)


So, DBMS_XMLGEN is faster, recommended, but doesn't allow for the specification of the encoding tag. DBMS_XMLQUERY allows for the encoding tag, as well as the specification of an id variable and value for each row in a rowset, but it cannot handle multiple occurences of the same name for a bind variable.
burleson
Hi Randy,

Thanks!

We will note this!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.