1 Reply Latest reply: Mar 30, 2013 4:21 PM by rp0428 RSS

    How to execute an sql function containing a SELECT query as paramete

    997240

      Hi
      I want to execute this sql query using JDBC methods , but could not be able to find any appropriate method to execute this query.

      select dbms_xmlgen.getxml(select * from departments) from dual

      please some one help with appropriate java code to execute it.
      Thank you

        • 1. Re: How to execute an sql function containing a SELECT query as paramete
          rp0428
          >
          I want to execute this sql query using JDBC methods , but could not be able to find any appropriate method to execute this query.

          select dbms_xmlgen.getxml(select * from departments) from dual

          please some one help with appropriate java code to execute it.
          >
          Java code? You can't execute that query at all since it is invalid. The parameter needs to be a query string NOT a query.
          select dbms_xmlgen.getxml('select * from departments') from dual 
          See the DBMS_XMLGEN package in the docs
          http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_xmlgen.htm#i1013206
          >
          Converts the results from the SQL query string to XML format, and returns the XML as a temporary CLOB, which must be subsequently freed using the DBMS_LOB.FREETEMPORARY call:

          DBMS_XMLGEN.GETXML (
          sqlQuery IN VARCHAR2,
          dtdOrSchema IN number := NONE)
          RETURN CLOB;
          >
          Then you execute that query like any other query that returns a result set. The result set will consist of one row and one column of CLOB datatype.

          See the JDBC Developer's Guide - it has examples of how to execute queries that return result sets.

          And this section shows how tow read/write CLOBs
          http://docs.oracle.com/cd/B28359_01/java.111/b31224/oralob.htm#sthref755