Trying to replace dbms_xmlgen.xmlget with sys_xmlagg

Question

I'm working on parameterizing some JDBC queries against Oracle 10gR2.

Most of the queries are of the form:

String value = "somevalue";
String query = "select dbms_xmlgen.xmlget('select c1, c2 from t1 where c1 = ''"
    + somevalue + "'' ') xml from dual;";

I can't parameterize that as is, since the actual select is in a quoted string inside xmlget and parameters are not expanded inside a string. JDBC will see that query as having no parameters.

I've been fairly successful in emulating the behavior of dbms_xmlgen.xmlget with:

String query = "SELECT xmltype.getclobval(sys_xmlagg(xmlelement(\"ROW\","                                                                                        
    + "xmlforest(c1, c2)))) xml from t1 where c1 = ?";

The only issue I have not been able to resolve is the case where the query returns no rows.

With dbms_xmlgen.xmlget, no rows returns an empty CLOB. But, with sys_xmlagg, no rows results in a CLOB consisting of:

<?xml version="1.0"?><ROWSET></ROWSET>

I'm looking for a solution that will give me an empty CLOB instead of an empty document.

Answer 1

I don't have access to an Oracle DB at the moment, so please forgive inaccuracies.

The parameterization of the DBMS_XMLGEN call seems to be the goal. This is accomplished by using a little PL/SQL. The Oracle Docs for the DBMS_XMLGEN package describe a few operations which should help. First, create a context from a SYS_REFCURSOR using this form:

DBMS_XMLGEN.NEWCONTEXT (
  queryString  IN SYS_REFCURSOR)
RETURN ctxHandle;

Then, use the context in another form of GetXML:

DBMS_XMLGEN.GETXML (
   ctx          IN ctxHandle, 
   tmpclob      IN OUT NCOPY CLOB,
   dtdOrSchema  IN number := NONE)
RETURN BOOLEAN;

Using this method also gives the benefit of potentially reusing the CLOB (not making a new temporary one), which may help with performance. There is another form which is more like the one you were using in your example, but loses this property.

One more thing... The return of GETXML in this example should tell you whether there were rows returned or not. This should be more reliable than checking the contents of the CLOB when the operation completes. Alternately, you can use the NumRowsProcessed function on the context to get the count of the rows included in the CLOB.

Roughly, your code would look something like this:

DECLARE
  srcRefCursor SYS_REFCURSOR;
  ctxHandle ctxHandle;
  somevalue VARCHAR2(1000);
  myClob CLOB;
  hasRows boolean;
BEGIN
  OPEN srcRefCursor FOR
      SELECT c1, c2 
      FROM t1 
      WHERE c1 = somevalue; --Note parameterized value

  ctxHandle := DBMS_XMLGEN.NEWCONTEXT(srcRefCursor);

  hasRows := DBMS_XMLGEN.GETXML(
      ctxHandle,
      myClob -- XML stored in myCLOB
  );

  IF (hasRows) THEN
    /* Do work on CLOB here */
  END IF;


  DBMS_XMLGEN.CLOSECONTEXT(ctxHandle);
END;