1 Reply Latest reply on Mar 6, 2019 11:10 AM by tsuji

    RETURNING SEQUENCE, supported or not?

    nikos

      According to the documentation of the function XMLQUERY, RETURNING CONTENT is the only valid option and in the documentation it explicitly states (on docs.oracle.com/en/database/oracle/oracle-database/18/adxdb/xquery-and-XML-DB.html):

      "Oracle XML DB supports only the RETURNING CONTENT clause of SQL/XML function XMLQuery; it does not support the RETURNING SEQUENCE clause."

       

      On https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/XMLQUERY.html RETURNING SEQUENCE is not mentioned, but the syntax does not offer any other option than RETURNING CONTENT.

       

      Even the syntax check in SQL Delevoper considers SEQUENCE to be incorrect after RETURNING. And yet it appears to be working correctly. If I use any other random word after RETURNING then I get a syntax error on runtime (ORA-19101: CONTENT keyword expected), but SEQUENCE works and seems to behave correctly (does not add a document node around the result).

       

      So the question is, has RETURNING SEQUENCE been implemented and is supported, but someone forgot to update the documentation (and maybe include some info in the "What's new" section)? Or is this an intentional secret because this feature is not tested and shouldn't have been included in the released version?

       

      Here is a simple example that shows that RETURNING SEQUENCE works:

       

      SELECT XMLQUERY('$x/name()' PASSING XMLQUERY('<R>5</R>' RETURNING SEQUENCE) AS "x" RETURNING CONTENT)

      FROM dual

      Returns nicely "R" (as XMLTYPE). $x is the R element node.

       

      SELECT XMLQUERY('$x/name()' PASSING XMLQUERY('<R>5</R>' RETURNING CONTENT) AS "x" RETURNING CONTENT)

      FROM dual

      Returns null since the document node added by RETURNING CONTENT does not have a name. $x in a document node.

       

      And to confirm that the R element node is still there:

      SELECT XMLQUERY('$x/*/name()' PASSING XMLQUERY('<R>5</R>' RETURNING CONTENT) AS "x" RETURNING CONTENT)

      FROM dual

      Which returns "R". $x is the document node, $x/* matches the R element node.

        • 1. Re: RETURNING SEQUENCE, supported or not?
          tsuji

          I think your reading of the matter is quite correct and pertinent. Prima facaie it should have mentioned returning sequence being rendered support since some undetermined time, at least, it's being supported in v11g already.

           

          Apart from your demos, which are very good and to the point, I would suggest a even more explicit verification as variants to yours ...

          SELECT XMLQUERY('$x instance of document-node()' PASSING XMLQUERY('<R>5</R>' RETURNING SEQUENCE) AS "x" RETURNING CONTENT)
          FROM dual
          /
          SELECT XMLQUERY('$x instance of document-node()' PASSING XMLQUERY('<R>5</R>' RETURNING CONTENT) AS "x" RETURNING CONTENT)
          FROM dual
          /
          SELECT XMLQUERY('$x instance of element()' PASSING XMLQUERY('<R>5</R>' RETURNING SEQUENCE) AS "x" RETURNING CONTENT)
          FROM dual
          /
          SELECT XMLQUERY('$x instance of element()' PASSING XMLQUERY('<R>5</R>' RETURNING CONTENT) AS "x" RETURNING CONTENT)
          FROM dual
          /
          

           

          Just mentioning in passing, the oracle distribution of derby db under the name of javadb(?) should have by default and optional in expliciting RETURN SEQUENCE. Go figure!