Oracle8i Application Developer's Guide - XML Release 3 (8.1.7) Part Number A86030-01 |
|
Using XML-SQL Utility (XSU), 12 of 26
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.
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(); } }
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.
|
![]() Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|