Generating XML from ResultSet Objects

We saw how we can supply a SQL query and get the results as XML. In the last example, we saw how we can retrieve results in a paginated fashion. However in web cases, we might want to retrieve the previous page and not just the next page of results. To provide this scrollable functionality, we can use the Scrollable ResultSet. Use the ResultSet object to move back and forth within the result set and use the utility to generate the XML everytime.

XSU Example 4: Generating XML from JDBC ResultSets (Java)

We will show how to use the JDBC ResultSet and generate XML from that. Note that using the ResultSet might be necessary in cases which are not handled directly by the utility (for example, setting the batch size, binding values,...) We will extend the previously defined pageTest class so that we handle any page.

public class pageTest()
{
   Connection conn;
   OracleXMLQuery qry;
   ResultSet rset;
   int lastRow = 0;

   public pageTest(String sqlQuery)
   {
     conn  = getConnection("scott","tiger");
     Statement stmt = conn.createStatement(sqlQuery);// create a scrollable 
Rset

     ResultSet rset = stmt.executeQuery();  // get the result set..
     qry = new OracleXMLQuery(conn,rset);   // create a OracleXMLQuery 
instance
     qry.keepObjectOpen(true); // Don't lose state after the first fetch
   }
  
   // Returns the next XML page..!
   public String getResult(int startRow, int endRow)
   {
     rset.scroll(lastRow-startRow);  // scroll inside the result set
     qry.setMaxRows(endRow-startRow); // set the max # of rows to 
retrieve..!
     return qry.getXMLString();
   }

   // Function to still perform the next page. 
   public String nextPage()
   {
     String result = getResult(lastRow,lastRow+10);
     lastRow+= 10;
     return result;
   }

   public void close()
   {
     stmt.close();   // close the statement..
     conn.close();   // close the connection
     qry.close();    // close the query..
   }
  
   public void main(String[] argv)
   {
     pageTest test = new pageTest("select * from emp");

     int i = 0;
     // Get the data one page at a time..!!!!!
     while ((str = test.getResult(i,i+10))!= null)
     {
         System.out.println(str);
         i+= 10;
     }
     test.close();
   }
}
 

XSU Example 5: Generating XML from Procedure Return Values (REF CURSORS) (Java)

The OracleXMLQuery class provides XML conversion only for query string or for ResultSets. But in your application if you had PL/SQL procedures which returned REF cursors, how would you do the conversion?

In this case, you can use the above mentioned ResultSet conversion mechanism to perform the task. REF cursors are references to cursor objects in PL/SQL. These cursor objects are valid SQL statements which can be iterated upon to get a set of values. These REF cursors are converted in to OracleResultSet objects in the Java world.

You can execute these procedures, get the OracleResultSet object and then send that in to the OracleXMLQuery object to get the desired XML.

Take this PL/SQL function which defines a REF cursor and returns it:

CREATE OR REPLACE package body testRef is

  function testRefCur RETURN empREF is
  a empREF;
  begin 
      OPEN a FOR select * from scott.emp; 
      return a;
  end;
end;
/

Now, everytime this function is called, it opens a cursor object for the query, "select * from emp" and returns that cursor instance. If you wanted to convert this to XML, you can do the following:

import org.w3c.dom.*;
import oracle.xml.parser.v2.*;
import java.sql.*;
import oracle.jdbc.driver.*;
import oracle.xml.sql.query.OracleXMLQuery;
import java.io.*;
public class REFCURtest
{
   public static void main(String[] argv)
     throws SQLException
   { 
      String str;
      Connection conn  = getConnection("scott","tiger"); // create connection

      // Create a ResultSet object by calling the PL/SQL function
      CallableStatement stmt =
         conn.prepareCall("begin ? := testRef.testRefCur(); end;");

      stmt.registerOutParameter(1,OracleTypes.CURSOR); // set the define type

      stmt.execute();   // Execute the statement.
      ResultSet rset = (ResultSet)stmt.getObject(1);  // Get the ResultSet

      OracleXMLQuery qry = new OracleXMLQuery(conn,rset); // prepare Query class
      qry.setRaiseNoRowsException(true);
      qry.setRaiseException(true);
      qry.keepCursorState(true);        // set options (keep the cursor alive..
      while ((str = qry.getXMLString())!= null)
           System.out.println(str);

      qry.close();    // close the query..!

      // Note since we supplied the statement and resultset, closing the
      // OracleXMLquery instance will not close these. We would need to 
      // explicitly close this ourselves..!
      stmt.close();
      conn.close();
   }
    // Get the connection given the user name and password..!
    private static Connection getConnection(String user, String passwd)
      throws SQLException
    {
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
      Connection conn =
          DriverManager.getConnection("jdbc:oracle:oci8:@",user,passwd);
     return conn;
   }

}

To apply the stylesheet on the other hand, use the applyStylesheet() command. This forces the stylesheet to be applied before generating the output.