Monday, August 23, 2004

XMLType from a PLSQL Web Services Perspective A customer today asked a common question of: how the heck do you return XML from a PL/SQL Web service? The unsaid reason for the question was that most PL/SQL Web services examples show primitive types or abstract types as return types. There are a number of ways but the one that came to mind first was to simply use the database XMLType.  Using sample data from my blog Publishing XMLType Web Services I changed the Java Web services implementation from that sample to be PL/SQL centric.  The moving parts are as follows: 1. PLSQL Package PACKAGE "PURCHASEORDER" AS FUNCTION GETPO (PONUM NUMBER) RETURN XMLTYPE; END; 2. PLSQL Package Body PACKAGE BODY "PURCHASEORDER" AS FUNCTION GETPO (PONUM NUMBER) RETURN XMLTYPE IS v_xml XMLTYPE; BEGIN   select e.XPO   into v_xml   from po_xml e   where e.xpo.extract('/PURCHASEORDER/@PONO').getNumberVal() = PONUM; RETURN v_xml; END GETPO; END PURCHASEORDER; 3. Table create and insert See the SQLPlus code example here http://radio.weblogs.com/0132036/2003/11/29.html Once you have done that both JDeveloper and the Web services assembler will be happy as punch to publish the PURCHASEORDER procedure as a Web service. This example Publish PL/SQL Web Services shows how to do it with Web Services Assembler (replace the DBFUNC naming with the above package) and this example from OTN - http://www.oracle.com/technology/tech/webservices/htdocs/series/plsql/index.html shows how to do it from JDeveloper. Other interesting return types such as result sets, BLOBs and idiosyncratic PL/SQL types can be found here http://www.oracle.com/technology/sample_code/tech/java/jsp/dbwebservices.html. comment [] 11:50:10 PM