Consuming Webservice using UTL_DBWS

Question

Background: We have a Webservice that is running under axis2 on a Tomcat. We would like our Oracle (10g Enterprise Edition Release 10.2.0.1.0) db to call our Webservice when a new row is entered into one of our tables. We've discovered we can use a trigger to call a Java Stored Procedure (JSP) or us PL/SQL and the UTL_DBWS utility. Neither is working for us however. Consuming the webservice from a standalone java class using either the axis javax.xml.rpc implementation or the oracle implementation does work outside of the db.

UTL_DBWS attempt:

unzipped contents of dbws-callout-utility-10131.zip to C:\oracle\product\10.2.0\db_5\sqlj\lib which was pulled down from http://www.oracle.com/technology/sample_code/tech/java/jsp/dbwebservices.html

ran loadjava command

loadjava -u <USER>/<PASSWORD>@<MACHINE>:1521:<INSTANCE> -r -v -f -genmissing -s -grant public C:\oracle\product\10.2.0\db_5\sqlj\lib\dbwsclientws.jar C:\oracle\product\10.2.0\db_5\sqlj\lib\dbwsclientdb102.jar

Added a bunch of permissions

execute dbms_java.grant_permission( '<<user>>', 'SYS:java.lang.RuntimePermission', 'getClassLoader', '' )
    execute dbms_java.grant_permission( '<<user>>', 'SYS:oracle.aurora.security.JServerPermission', 'Verifier', '' );
    execute dbms_java.grant_permission( '<<user>>', 'SYS:java.lang.RuntimePermission', 'accessClassInPackage.sun.util.calendar', '' ) ;
    execute dbms_java.grant_permission( '<<user>>', 'SYS:java.net.SocketPermission', '<<machineName>>', 'resolve' );
    execute dbms_java.grant_permission( '<<user>>', 'SYS:java.net.SocketPermission', '<<machineIP>>', 'connect,resolve' );
    execute dbms_java.grant_permission( '<<user>>', 'SYS:java.lang.RuntimePermission', 'createClassLoader', '' );

Our pl/sql:

FUNCTION wsproxy_send_request
return varchar2
as
l_service            SYS.UTL_DBWS.service;
l_call               SYS.UTL_DBWS.call;
l_result             ANYDATA;
l_wsdl_url           VARCHAR2(32767);
l_namespace          VARCHAR2(32767);
l_service_qname      SYS.UTL_DBWS.qname;
l_port_qname         SYS.UTL_DBWS.qname;
l_operation_qname    SYS.UTL_DBWS.qname;
l_input_params       SYS.UTL_DBWS.anydata_list;
boolean_type_qname   SYS.UTL_DBWS.QNAME;

begin

l_wsdl_url := 'http://<<host>>/axis2/services/<<serviceName>>?wsdl';
l_namespace := 'http://<<namespace>>';
l_service_qname := SYS.UTL_DBWS.to_qname(l_namespace, '<<serviceName>>');
l_port_qname := SYS.UTL_DBWS.to_qname(l_namespace, '<<myendpoint>>');
l_operation_qname := SYS.UTL_DBWS.to_qname(l_namespace, 'send');

l_service := SYS.UTL_DBWS.create_service (wsdl_document_location => URIFACTORY.getURI(l_wsdl_url), service_name => l_service_qname);
l_call := SYS.UTL_DBWS.create_call ( service_handle => l_service, port_name => l_port_qname, operation_name => l_operation_qname);

SYS.UTL_DBWS.SET_PROPERTY(l_call, 'OPERATION_STYLE', 'rpc');

l_input_params(0) := ANYDATA.ConvertNumber(1);
l_input_params(1) := ANYDATA.ConvertNumber(24387236726);
l_input_params(2) := ANYDATA.ConvertVarchar2('CTE');
l_input_params(3) := ANYDATA.ConvertVarchar2('STORE_RECORD_LOCATOR');
l_input_params(4) := ANYDATA.ConvertVarchar2('a');

boolean_type_qname := sys.utl_dbws.to_qname('http://www.w3.org/2001/XMLSchema', 'boolean');
sys.utl_dbws.set_return_type(l_call, boolean_type_qname);

l_result := SYS.UTL_DBWS.invoke (call_handle => l_call, input_params => l_input_params);

SYS.UTL_DBWS.release_call (call_handle => l_call);
SYS.UTL_DBWS.release_service(service_handle => l_service);

RETURN ANYDATA.AccessVarchar2(l_result);
END;

When we execute:

select wsproxy_send_request from dual;

we get:

Error: ORA-29532: Java call terminated by uncaught Java exception: java.lang.ArrayIndexOutOfBoundsException
ORA-06512: at "SYS.UTL_DBWS", line 568
ORA-06512: at "SYS.UTL_DBWS", line 492
ORA-06512: at "SYS.UTL_DBWS", line 380
ORA-06512: at "WSPROXY.WSPROXY_SEND_REQUEST", line 37

SQLState:  99999
ErrorCode: 29532
Position: 37

(Position 37 is the invoke call)

Answer 1

The trick was to add the parameter types/names... I haven't found the UTL_DBWS.add_parameter method documented anywhere, but I should of guessed it's existence since you have to do the same in a java implementation.

Anyway here is what I added

string_type_qname := SYS.UTL_DBWS.to_qname('http://www.w3.org/2001/XMLSchema', 'string');
long_type_qname := SYS.UTL_DBWS.to_qname('http://www.w3.org/2001/XMLSchema', 'long');

SYS.UTL_DBWS.add_parameter(l_call, 'args0', long_type_qname, 'ParameterMode.IN');
SYS.UTL_DBWS.add_parameter(l_call, 'args1', long_type_qname, 'ParameterMode.IN');
SYS.UTL_DBWS.add_parameter(l_call, 'args2', string_type_qname, 'ParameterMode.IN');
SYS.UTL_DBWS.add_parameter(l_call, 'args3', string_type_qname, 'ParameterMode.IN');
SYS.UTL_DBWS.add_parameter(l_call, 'args4', string_type_qname, 'ParameterMode.IN');

So the whole thing now is:

CREATE OR REPLACE
FUNCTION wsproxy_send_request
return varchar2
as
l_service               SYS.UTL_DBWS.service;
l_call              SYS.UTL_DBWS.call;
l_result                ANYDATA;
l_wsdl_url              VARCHAR2(32767);
l_namespace             VARCHAR2(32767);
l_service_qname         SYS.UTL_DBWS.qname;
l_port_qname            SYS.UTL_DBWS.qname;
l_operation_qname       SYS.UTL_DBWS.qname;
l_input_params          SYS.UTL_DBWS.ANYDATA_LIST;
boolean_type_qname      SYS.UTL_DBWS.QNAME;
string_type_qname       SYS.UTL_DBWS.QNAME;
long_type_qname         SYS.UTL_DBWS.QNAME;

begin

l_wsdl_url := 'http://<<host>>/axis2/services/<<serviceName>>?wsdl';
l_namespace := 'http://<<namespace>>';
l_service_qname := SYS.UTL_DBWS.to_qname(l_namespace, '<<serviceName>>');
l_port_qname := SYS.UTL_DBWS.to_qname(l_namespace, '<<endPoint>>');
l_operation_qname := SYS.UTL_DBWS.to_qname(l_namespace, 'send');

l_service := SYS.UTL_DBWS.create_service (URIFACTORY.getURI(l_wsdl_url), l_service_qname);
l_call := SYS.UTL_DBWS.create_call (l_service, l_port_qname, l_operation_qname);

boolean_type_qname := SYS.UTL_DBWS.to_qname('http://www.w3.org/2001/XMLSchema', 'boolean');
string_type_qname := SYS.UTL_DBWS.to_qname('http://www.w3.org/2001/XMLSchema', 'string');
long_type_qname := SYS.UTL_DBWS.to_qname('http://www.w3.org/2001/XMLSchema', 'long');

SYS.UTL_DBWS.add_parameter(l_call, 'args0', long_type_qname, 'ParameterMode.IN');
SYS.UTL_DBWS.add_parameter(l_call, 'args1', long_type_qname, 'ParameterMode.IN');
SYS.UTL_DBWS.add_parameter(l_call, 'args2', string_type_qname, 'ParameterMode.IN');
SYS.UTL_DBWS.add_parameter(l_call, 'args3', string_type_qname, 'ParameterMode.IN');
SYS.UTL_DBWS.add_parameter(l_call, 'args4', string_type_qname, 'ParameterMode.IN');

SYS.UTL_DBWS.SET_PROPERTY(l_call, 'SOAPACTION_USE', 'TRUE');
SYS.UTL_DBWS.SET_PROPERTY(l_call, 'SOAPACTION_URI', '');
SYS.UTL_DBWS.SET_PROPERTY(l_call, 'ENCODINGSTYLE_URI', '');
SYS.UTL_DBWS.SET_PROPERTY(l_call, 'OPERATION_STYLE', 'rpc');

l_input_params(0) := ANYDATA.ConvertNumber(1);
l_input_params(1) := ANYDATA.ConvertNumber(24387236726);
l_input_params(2) := ANYDATA.ConvertVarchar2('CTE');
l_input_params(3) := ANYDATA.ConvertVarchar2('STORE_RECORD_LOCATOR');
l_input_params(4) := ANYDATA.ConvertVarchar2('a');

sys.utl_dbws.set_return_type(l_call, boolean_type_qname);

l_result := SYS.UTL_DBWS.invoke(l_call, l_input_params);   

SYS.UTL_DBWS.release_call (l_call);
SYS.UTL_DBWS.release_service(l_service);

RETURN ANYDATA.AccessVarchar2(l_result);
END;
/

I finally saw this after paying closer attention to the guide posted in the oracle forums http://forums.oracle.com/forums/thread.jspa?threadID=633268&tstart=0

I'm getting null for my response now, but the parameter issue has been solved.

Answer 2

I've not used this package yet but usually in Oracle the arrays are numbered from 1 to N. Could you replace the lines 28-32 with:

l_input_params(1) := ANYDATA.ConvertNumber(1);
l_input_params(2) := ANYDATA.ConvertNumber(24387236726);
l_input_params(3) := ANYDATA.ConvertVarchar2('CTE');
l_input_params(4) := ANYDATA.ConvertVarchar2('STORE_RECORD_LOCATOR');
l_input_params(5) := ANYDATA.ConvertVarchar2('a');