Tuesday, 17 March 2009

Getting debug output from utl_dbws PLSQL package

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 on

call 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?WSDL

prompt
prompt Calling method todaysDate from web service
prompt

select PasWS102.todaysDate from dual;

prompt
prompt Calling method echoString from web service
prompt

select 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>

No comments: