UTL_DBWS : Handling Web Service methods that accept XML Input parameters

Dealing with Web Services callouts using the UTL_DBWS package requires some thought when calling methods which accept XML data such as "org.w3c.dom.Element". In this example I show how to ensure the XML data within your XML file is correctly escaped prior to sending the request to the Web Service.

An example of UTL_DBWS package can be found on steve's blog here. That shows how to set it up at the database level in 11g.

Lets assume we have a Web Service deployed to OAS 10.1.3.x with a class as follows. Basic method which takes a org.w3c.dom.Element parameter for it's only service method testXML.

package pas.au.xml.ws;

import org.w3c.dom.Element;

public class XMLWebService
{
  public XMLWebService()
  {
  }
  
  public String testXML (Element xmlInput)
  {
    return "success";
  }
}

With UTL_DBWS installed we could simply write a PLSQL block as follows to call out Web Service method and the output clearly shows this works fine.

set serveroutput on size 100000
set linesize 130

declare

  service_ sys.utl_dbws.SERVICE;
  call_ sys.utl_dbws.CALL;
  service_qname sys.utl_dbws.QNAME;
  port_qname sys.utl_dbws.QNAME;
  response sys.XMLTYPE;
  request sys.XMLTYPE;

  inputXML varchar2(300) := null;
  myXML varchar2(100) := 'pas';

begin

  dbms_output.put_line('Calling Web Service http://beast.au.oracle.com:7777/xmlws/XMLWSSoapHttpPor');
  service_qname := sys.utl_dbws.to_qname(null, 'testXMLElement');
  service_      := sys.utl_dbws.create_service(service_qname);
  call_         := sys.utl_dbws.create_call(service_);
  sys.utl_dbws.set_target_endpoint_address(call_, 'http://beast.au.oracle.com:7777/xmlws/XMLWSSoapHttpPort');
  sys.utl_dbws.set_property( call_, 'OPERATION_STYLE', 'document');

  dbms_output.put_line('Preparing request');

  inputXML := '<ns1:testXMLElement xmlns:ns1="http://pas.au.xml.ws/types/"><ns1:xmlInput><pas>'||
              myXML||
              '</pas></ns1:xmlInput>'||
              '</ns1:testXMLElement>';

  request       := sys.XMLTYPE(inputXML);
  response      := sys.utl_dbws.invoke(call_, request);

  dbms_output.put_line('** Soap Response **'||chr(10)||response.getStringVal());

  dbms_output.put_line('Result = '||
        response.extract('//ns0:result/child::text()',
        'xmlns:ns0="http://pas.au.xml.ws/types/"').getstringval());
end;
/
show errors;

Output

SCOTT@linux11gr2> @invoke_xmlws.sql
Calling Web Service http://beast.au.oracle.com:7777/xmlws/XMLWSSoapHttpPor
Preparing request
** Soap Response **
<ns0:testXMLResponseElement xmlns:ns0="http://pas.au.xml.ws/types/">

<ns0:result>success</ns0:result>
</ns0:testXMLResponseElement>

Result = success

PL/SQL procedure successfully completed.

No errors.
SCOTT@linux11gr2>

The problem here is we can't just assume that the data which we pass into theWeb Service method is not a special character which in the XML world would require that it be escaped. The data we passed in our first test was a simple string such as "pas" as shown below.

myXML varchar2(100) := 'pas';

Lets assume we now want to pass data as follows by introducing a & character. The assumption here is we can't control what is passed to the method so it may be a special character which XML won't accept.

myXML varchar2(100) := 'pas&';

The result when running the PLSQL Block now is a runtime exception as follows.

SCOTT@linux11gr2> @invoke_xmlws.sql
Calling Web Service http://beast.au.oracle.com:7777/xmlws/XMLWSSoapHttpPor
Preparing request
declare
*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00242: invalid use of ampersand ('&') character (use &)
Error at line 1
ORA-06512: at "SYS.XMLTYPE", line 310
ORA-06512: at line 29


No errors.
SCOTT@linux11gr2>

In fact if you try this outside of PLSQL / Oracle RDBMS itself, such as a J2SE client you would get a runtime exception as follows. The database error was a lot more useful then this.

java.security.PrivilegedActionException: javax.xml.soap.SOAPException: Unable to get header stream in saveChanges. SOAP exception while trying to externalize: Error parsing envelope: (1, 1) Start of root element expected.. java.io.IOException: SOAP exception while trying to externalize: Error parsing envelope: (1, 1) Start of root element expected.

So the solution around this is to use the package method dbms_xmlgen.CONVERT to convert the XML data into the escaped XML equivalent. So now our SQL is as follows

set serveroutput on size 100000
set linesize 130
call dbms_java.set_output(100000);

declare

  service_ sys.utl_dbws.SERVICE;
  call_ sys.utl_dbws.CALL;
  service_qname sys.utl_dbws.QNAME;
  port_qname sys.utl_dbws.QNAME;
  response sys.XMLTYPE;
  request sys.XMLTYPE;

  inputXML varchar2(300) := null;
  myXML varchar2(100) := 'pas&';

begin

  dbms_output.put_line('Calling Web Service http://beast.au.oracle.com:7777/xmlws/XMLWSSoapHttpPor');
  service_qname := sys.utl_dbws.to_qname(null, 'testXMLElement');
  service_      := sys.utl_dbws.create_service(service_qname);
  call_         := sys.utl_dbws.create_call(service_);
  sys.utl_dbws.set_target_endpoint_address(call_, 'http://beast.au.oracle.com:7777/xmlws/XMLWSSoapHttpPort');
  sys.utl_dbws.set_property( call_, 'OPERATION_STYLE', 'document');

  dbms_output.put_line('Preparing request');

  --add to convert XML with special characters correctly escaped
  inputXML := '<ns1:testXMLElement xmlns:ns1="http://pas.au.xml.ws/types/"><ns1:xmlInput><pas>'||
              dbms_xmlgen.CONVERT(myXML, dbms_xmlgen.ENTITY_ENCODE)||
              '</pas></ns1:xmlInput>'||
              '</ns1:testXMLElement>';

  request       := sys.XMLTYPE(inputXML);
  response      := sys.utl_dbws.invoke(call_, request);

  dbms_output.put_line('** Soap Response **'||chr(10)||response.getStringVal());

  dbms_output.put_line('Result = '||
         response.extract('//ns0:result/child::text()',
          'xmlns:ns0="http://pas.au.xml.ws/types/"').getstringval());
end;
/
show errors;

Note: I added a call to call dbms_java.set_output(100000); prior to running the BLOCK so we could see our REQUEST/RESPONSE XML to verify it has done what we needed it to do.

And the output now shows it works correctly.

Output

SCOTT@linux11gr2> @invoke_xmlws2.sql

Call completed.

Calling Web Service http://beast.au.oracle.com:7777/xmlws/XMLWSSoapHttpPor
ServiceFacotory: oracle.j2ee.ws.client.ServiceFactoryImpl@7e6efb86
WSDL: null
Service: oracle.j2ee.ws.client.BasicService@917ab4b8
*** Created service: -1261915451 - oracle.jpub.runtime.dbws.DbwsProxy$ServiceProxy@eec8c59c ***
ServiceProxy.get(-1261915451) = oracle.jpub.runtime.dbws.DbwsProxy$ServiceProxy@eec8c59c
setProperty(javax.xml.rpc.soap.operation.style, document)
Preparing request
dbwsproxy.add.map: ns1, http://pas.au.xml.ws/types/
Attribute 0: http://pas.au.xml.ws/types/: xmlns:ns1, http://pas.au.xml.ws/types/
dbwsproxy.lookup.map: ns1, http://pas.au.xml.ws/types/
createElement(ns1:testXMLElement,null,http://pas.au.xml.ws/types/)
dbwsproxy.add.soap.element.namespace: ns1, http://pas.au.xml.ws/types/
Attribute 0: http://pas.au.xml.ws/types/: xmlns:ns1, http://pas.au.xml.ws/types/
dbwsproxy.element.node.child.0: 1, null
dbwsproxy.lookup.map: ns1, http://pas.au.xml.ws/types/
createElement(ns1:xmlInput,null,http://pas.au.xml.ws/types/)
dbwsproxy.element.node.child.0: 1, null
createElement(pas,null,null)
dbwsproxy.text.node.child.0: 3, pas&
request:
<ns1:testXMLElement xmlns:ns1="http://pas.au.xml.ws/types/">
<ns1:xmlInput>
<pas>pas&amp;</pas>
</ns1:xmlInput>
</ns1:testXMLElement>
response:
<ns0:testXMLResponseElement xmlns:ns0="http://pas.au.xml.ws/types/">
<ns0:result>success</ns0:result>
</ns0:testXMLResponseElement>
** Soap Response **
<ns0:testXMLResponseElement xmlns:ns0="http://pas.au.xml.ws/types/">

<ns0:result>success</ns0:result>
</ns0:testXMLResponseElement>

Result = success

PL/SQL procedure successfully completed.

No errors.
SCOTT@linux11gr2>