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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-18 18:39:35  来源:igfitidea点击:

Consuming Webservice using UTL_DBWS

oracleweb-servicesplsql

提问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');