oracle 使用 UTL_DBWS 使用 Web 服务
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/1189411/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Consuming Webservice using UTL_DBWS
提问by Josh
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.
背景:我们有一个在 Tomcat 上的axis2 下运行的Web 服务。我们希望我们的 Oracle(10g 企业版 10.2.0.1.0 版)数据库在我们的一个表中输入新行时调用我们的 Web 服务。我们发现我们可以使用触发器来调用 Java 存储过程 (JSP) 或我们的 PL/SQL 和 UTL_DBWS 实用程序。然而,两者都不适合我们。使用轴 javax.xml.rpc 实现或 oracle 实现从独立 java 类使用 web 服务确实可以在 db 之外工作。
UTL_DBWS attempt:
UTL_DBWS 尝试:
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
将 dbws-callout-utility-10131.zip 的内容解压到 C:\oracle\product\10.2.0\db_5\sqlj\lib,从http://www.oracle.com/technology/sample_code/tech/ java/jsp/dbwebservices.html
ran loadjava command
运行 loadjava 命令
loadjava -u <USER>/<PASSWORD>@<MACHINE>:1521:<INSTANCE> -r -v -f -genmissing -s -grant public C:\oracle\product.2.0\db_5\sqlj\lib\dbwsclientws.jar C:\oracle\product.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:
我们的 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)
(位置 37 是调用调用)
采纳答案by Josh
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.
诀窍是添加参数类型/名称......我没有在任何地方找到记录的 UTL_DBWS.add_parameter 方法,但我应该猜到它存在,因为你必须在 java 实现中做同样的事情。
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
在仔细阅读了在 oracle 论坛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.
我现在的响应为空,但参数问题已解决。
回答by Vincent Malgrat
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:
我还没有使用过这个包,但通常在 Oracle 中,数组的编号从 1 到 N。您能否将第 28-32 行替换为:
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');