Publishing XMLType Web Services
Here is a question that often comes up from folks working with the XDB feature of the Oracle DB - how do I publish a query of an XMLType column as a Web service?
Here goes ... don't take this entry as a great example of code ... it simply shows the example in action.
1. Create an XMLType table/column and put some data into it:
connect scott/tiger
-- create table with xmltype column to store po in XML format
create table po_xml(
XPO SYS.XMLType /* purchase order in XML format */
);
-- insert PurchaseOrders
insert into po_xml
values(sys.XMLType.createXML(
' <PURCHASEORDER PONO="1001">
<CUSTOMER>
<CUSTNO>1</CUSTNO>
<CUSTNAME>Jean Nance</CUSTNAME>
<ADDRESS>
<STREET>2 Avocet Drive</STREET>
<CITY>Redwood Shores</CITY>
<STATE>CA</STATE>
<ZIP>95054</ZIP>
</ADDRESS>
<PHONELIST>
<VARCHAR2>415-555-1212</VARCHAR2>
</PHONELIST>
</CUSTOMER>
<ORDERDATE>29-APR-01</ORDERDATE>
<SHIPDATE>10-MAY-97 12.00.00.000000 AM</SHIPDATE>
<LINEITEMS>
<LINEITEM_TYP LineItemNo="1">
<ITEM StockNo="1534">
<PRICE>2234</PRICE>
<TAXRATE>2</TAXRATE>
</ITEM>
<QUANTITY>12</QUANTITY>
<DISCOUNT>0</DISCOUNT>
</LINEITEM_TYP>
<LINEITEM_TYP LineItemNo="2">
<ITEM StockNo="1535">
<PRICE>3456.23</PRICE>
<TAXRATE>2</TAXRATE>
</ITEM>
<QUANTITY>10</QUANTITY>
<DISCOUNT>10</DISCOUNT>
</LINEITEM_TYP>
</LINEITEMS>
<SHIPTOADDR />
</PURCHASEORDER>'
));
insert into po_xml
values(sys.XMLType.createXML(
' <PURCHASEORDER PONO="2001">
<CUSTOMER>
<CUSTNO>2</CUSTNO>
<CUSTNAME>John Nike</CUSTNAME>
<ADDRESS>
<STREET>323 College Drive</STREET>
<CITY>Edison</CITY>
<STATE>NJ</STATE>
<ZIP>08820</ZIP>
</ADDRESS>
<PHONELIST>
<VARCHAR2>609-555-1212</VARCHAR2>
<VARCHAR2>201-555-1212</VARCHAR2>
</PHONELIST>
</CUSTOMER>
<ORDERDATE>29-APR-01</ORDERDATE>
<SHIPDATE>20-MAY-97 12.00.00.000000 AM</SHIPDATE>
<LINEITEMS>
<LINEITEM_TYP LineItemNo="1">
<ITEM StockNo="1004">
<PRICE>6750</PRICE>
<TAXRATE>2</TAXRATE>
</ITEM>
<QUANTITY>1</QUANTITY>
<DISCOUNT>0</DISCOUNT>
</LINEITEM_TYP>
<LINEITEM_TYP LineItemNo="2">
<ITEM StockNo="1011">
<PRICE>4500.23</PRICE>
<TAXRATE>2</TAXRATE>
</ITEM>
<QUANTITY>2</QUANTITY>
<DISCOUNT>1</DISCOUNT>
</LINEITEM_TYP>
</LINEITEMS>
<SHIPTOADDR>
<STREET>55 Madison Ave</STREET>
<CITY>Madison</CITY>
<STATE>WI</STATE>
<ZIP>53715</ZIP>
</SHIPTOADDR>
</PURCHASEORDER>'
));
set long 100000
select x.xpo.getClobVal() xpo
from po_xml x;
2. Create a Java class that queries the table using JDBC using an input string parameter - the method should return an XML Element so that Oracle's Web services publishing tooling can publish it:
package com.company.finance;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.OracleResultSet;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.xdb.XMLType;
import oracle.xml.parser.v2.*;
import org.w3c.dom.*;
public class POProcessor
{
static Connection conn = null;
static OracleCallableStatement stmt = null;
public Element getPO(String poNumber) throws Exception {
Document poDoc = null;
String querySQL = "select e.XPO from po_xml e " +
"where e.xpo.extract('/PurchaseOrder/@PONO').getNumberVal()=" +
poNumber;
getConn();
OraclePreparedStatement stmt = (OraclePreparedStatement) conn.prepareStatement(querySQL);
ResultSet rset = stmt.executeQuery();
OracleResultSet orset = (OracleResultSet) rset;
while(orset.next()) {
// get the XMLType
XMLType poxml = XMLType.createXML(orset.getOPAQUE(1));
// get the XMLDocument as a string...
poDoc = (Document)poxml.getDOM();
return poDoc.getDocumentElement();
}
return poElement;
}
/*
* Default constructor that takes no arguments; sets up the database connection
* using the parameters given within DriverManager.getConnection().
*/
private void getConn() throws Exception {
// Load Oracle driver
DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
if (conn == null) {
// Connect to the local database
conn = DriverManager.getConnection
("jdbc:oracle:thin:@127.0.0.1:1521:o920",
"scott", "tiger");
}
}
}
3. Publish as a Web service - in this case I have used JDeveloper 10g rather than the command line tooling: 
4. Deploy it - see this sample of how Web services deployment works in JDeveloper - I won't duplicate it here.
5. Test it out by going to the endpoint home page: 
What was the missing piece that made it work (there's always a catch, right?!): xdb.jar from the Oracle9i DB install in the classpath - this is not distributed with OC4J 9.0.3 out of the box.
Some material that made this possible:
- The schema from an Oracle9i R2 install - see <Oracle_Home>rdbmsdemosxmltype3.sql
- Sample JDBC call against JDBC type - see <Oracle_Home>rdbmsdemosxmltype3.java
- XMLType Java documentation:
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96620/xdb09jav.htm#1656
- OracleAS 10g 9.0.4 Web Services documentation:
http://otn.oracle.com/tech/java/oc4j/904/doc_library/webservices-devguide-904-preview.pdf
- A SQLPlus query to make sure you really have data:
select e.XPO from po_xml e where e.xpo.extract('/PurchaseOrder/@PONO').getNumberVal()=2001
11:08:39 PM 
|
