When calling web services from an oracle database you can use utl_dbws package. There are times though that you need to debug it to determine why it may be failing. This can be achieved as follows.
call dbms_java.set_output(100000);Example output from a SQL file as follows. This example is calling a JAXRPC 1.4 web service from OAS 10.1.3.4
SQL:
set serveroutput oncall dbms_java.set_output(100000);-- call web service methods here now for end point below-- http://beast.au.oracle.com:7777/wsrocks/DemoWSSoapHttpPort-- WSDL = http://beast.au.oracle.com:7777/wsrocks/DemoWSSoapHttpPort?WSDLpromptprompt Calling method todaysDate from web servicepromptselect PasWS102.todaysDate from dual;promptprompt Calling method echoString from web servicepromptselect PasWS102.echoString('Pas') from dual;Output:
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 17 08:56:38 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Enter password: *****
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Call completed.
Calling method todaysDate from web service
TODAYSDATE
----------------------------------------------------------------------------------------------------
Tue Mar 17 07:57:37 EST 2009
ServiceFacotory: oracle.j2ee.ws.client.ServiceFactoryImpl@bb101899
WSDL: null
Service: oracle.j2ee.ws.client.BasicService@ce6cb3d5
*** Created service: -677524827 - oracle.jpub.runtime.dbws.DbwsProxy$ServiceProxy@2968f5e4 ***
ServiceProxy.get(-677524827) = oracle.jpub.runtime.dbws.DbwsProxy$ServiceProxy@2968f5e4
** Calling web service method {todaysDate} **
setProperty(javax.xml.rpc.soap.http.soapaction.use, true)
setProperty(javax.xml.rpc.soap.http.soapaction.uri, http://pas.au.ws//todaysDate)
setProperty(javax.xml.rpc.soap.operation.style, document)
** Invoking web service method {todaysDate} **
Attribute 0: http://pas.au.ws/types/: xmlns, http://pas.au.ws/types/
createElement(todaysDateElement,null,http://pas.au.ws/types/)
Attribute 0: http://pas.au.ws/types/: xmlns, http://pas.au.ws/types/
request:
response:
Tue Mar 17 07:57:37 EST 2009
** Successfully invoked web service method {todaysDate} **
Calling method echoString from web service
PASWS102.ECHOSTRING('PAS')
----------------------------------------------------------------------------------------------------
Pas
ServiceFacotory: oracle.j2ee.ws.client.ServiceFactoryImpl@bb101899
WSDL: null
Service: oracle.j2ee.ws.client.BasicService@d05260ad
*** Created service: -677524826 - oracle.jpub.runtime.dbws.DbwsProxy$ServiceProxy@7789dbfd ***
ServiceProxy.get(-677524826) = oracle.jpub.runtime.dbws.DbwsProxy$ServiceProxy@7789dbfd
** Calling web service method {echoString} **
setProperty(javax.xml.rpc.soap.http.soapaction.use, true)
setProperty(javax.xml.rpc.soap.http.soapaction.uri, http://pas.au.ws//echoString)
setProperty(javax.xml.rpc.soap.operation.style, document)
** Invoking web service method {echoString} **
Attribute 0: http://pas.au.ws/types/: xmlns, http://pas.au.ws/types/
createElement(echoStringElement,null,http://pas.au.ws/types/)
Attribute 0: http://pas.au.ws/types/: xmlns, http://pas.au.ws/types/
dbwsproxy.element.node.child.0: 1, null
createElement(s,null,http://pas.au.ws/types/)
dbwsproxy.text.node.child.0: 3, Pas
request:
Pas
response:
Pas
** Successfully invoked web service method {echoString} **
SCOTT@linux10g>