oracle存储过程调用webservice

来源：互联网发布：apache php exec 权限编辑：程序博客网时间：2024/04/28 18:11

oracle10以后，提供了可以使用存储过程调用webservice的功能，首先需要在官网下载dbws-callout-utility-10131包，解压缩之后把sqlj\lib下面的包都复制到oracle的product\11.2.0\dbhome_1\sqlj\lib下；

之后使用sys账户登录到数据库，设置

shared_pool_size=132M
java_pool_size=80M

showparameter SHARED_POOL_SIZE
show parameter JAVA_POOL_SIZE

alter system set SHARED_POOL_SIZE=132M scope=both;

alter system set JAVA_POOL_SIZE=80M scope=both;

之后执行

loadjava -u scott/tiger -r -v -f -genmissing dbwsclientws.jardbwsclientdb11.jar

scott/tiger 是需要调用webservice的用户和密码

之后执行

SQL> call dbms_java.grant_permission( 'SCOTT','SYS:java.lang.RuntimePermission', 'setFactory', '' );

SQL>call dbms_java.grant_permission( 'SCOTT','SYS:java.lang.RuntimePermission', 'shutdownHooks', '' ); SQL> call dbms_java.grant_permission( 'SCOTT','SYS:java.util.logging.LoggingPermission', 'control', '' );

再执行

SQL> @?/sqlj/lib/utl_dbws_decl.sql SQL> @?/sqlj/lib/utl_dbws_body.sql

需要给Scott用户一些权限

exec dbms_java.grant_permission( 'RADIUS', 'SYS:java.net.SocketPermission', 'localhost:7001', 'listen,resolve' );
exec dbms_java.grant_permission( 'SCOTT', 'SYS:java.net.SocketPermission', 'localhost', 'resolve' );
exec dbms_java.grant_permission( 'SCOTT', 'SYS:java.net.SocketPermission', '127.0.0.1:7001', 'connect,resolve' );
EXEC Dbms_Java.Grant_Permission('SCOTT','java.io.FilePermission', '*','read ');
EXEC dbms_java.grant_permission( 'SCOTT', 'SYS:java.lang.RuntimePermission', 'createClassLoader', '' );
exec dbms_java.grant_permission( 'SCOTT', 'SYS:java.util.PropertyPermission', 'HTTPClient.socket.idleTimeout', 'write' );
exec dbms_java.grant_permission( 'SCOTT', 'SYS:java.lang.RuntimePermission', 'setFactory', '' );
exec dbms_java.grant_permission( 'SCOTT', 'SYS:java.lang.RuntimePermission', 'accessClassInPackage.sun.util.calendar', '' );
exec dbms_java.grant_permission( 'SCOTT','SYS:java.lang.IllegalAccessException','getClassLoader', '' );
exec dbms_java.grant_permission( 'SCOTT', 'SYS:java.lang.RuntimePermission', 'shutdownHooks', '' );
exec dbms_java.grant_permission( 'SCOTT', 'SYS:java.util.logging.LoggingPermission', 'control', '' );
exec dbms_java.grant_permission( 'SCOTT', 'SYS:java.lang.RuntimePermission', 'setFactory', '' );

之后就可以执行webservice调用了

declare
   service_ sys.utl_dbws.SERVICE;
   call_ sys.utl_dbws.CALL;
   service_qname sys.utl_dbws.QNAME;
   port_qname sys.utl_dbws.QNAME;
   operation_qname sys.utl_dbws.QNAME;
   string_type_qname sys.utl_dbws.QNAME;
   retx ANYDATA;
   retx_string VARCHAR2(100);
   retx_len number;
   params sys.utl_dbws.ANYDATA_LIST;
begin
   service_qname := sys.utl_dbws.to_qname(null, 'HelloServiceEJB');
   service_ := sys.utl_dbws.create_service(service_qname);
   port_qname := sys.utl_dbws.to_qname(null, 'HttpSoap11');
   operation_qname := sys.utl_dbws.to_qname('http://oracle.j2ee.ws/javacallout/Hello/types', 'sayHelloElement');
   call_ := sys.utl_dbws.create_call(service_, port_qname, operation_qname);
   sys.utl_dbws.set_target_endpoint_address(call_, 'http://stacd15:8888/javacallout/javacallout');
   --sys.utl_dbws.set_property(call_, 'ENCODINGSTYLE_URI', 'http://schemas.xmlsoap.org/soap/encoding/');
   string_type_qname := sys.utl_dbws.to_qname('http://www.w3.org/2001/XMLSchema', 'string');
        sys.utl_dbws.add_parameter(call_, 'String_1', string_type_qname, 'ParameterMode.IN');
        sys.utl_dbws.set_return_type(call_, string_type_qname);
   params(0) := ANYDATA.convertvarchar('hello');
   retx := sys.utl_dbws.invoke(call_, params);
   retx_string := retx.accessvarchar2;
   dbms_output.put_line('PL/SQL DII client return ' || retx_string);
end;

需要注意的是

sys.utl_dbws.add_parameter(call_, 'String_1', string_type_qname, 'ParameterMode.IN');

中的'String_1' 是wsdl中的参数的名字，需要完全一致，如果存在多个参数，需要多次添加

utl_dbws.add_parameter(call_,
                           'arg0',
                           string_type_qname,
                           'ParameterMode.IN');
    utl_dbws.add_parameter(call_,
                           'arg1',
                           string_type_qname,
                           'ParameterMode.IN');
    utl_dbws.add_parameter(call_,
                           'arg2',
                           string_type_qname,
                           'ParameterMode.IN');
    utl_dbws.add_parameter(call_,
                           'arg3',
                           string_type_qname,
                           'ParameterMode.IN');
    utl_dbws.add_parameter(call_,
                           'arg4',
                           string_type_qname,
                           'ParameterMode.IN');
    utl_dbws.add_parameter(call_,
                           'arg5',
                           string_type_qname,
                           'ParameterMode.IN');
    utl_dbws.set_return_type(call_, string_type_qname);
    params(0) := ANYDATA.convertvarchar(‘11’);
    params(1) := ANYDATA.convertvarchar(‘22’);
    params(2) := ANYDATA.convertvarchar(‘33’);
    params(3) := ANYDATA.convertvarchar(‘44’);
    params(4) := ANYDATA.convertvarchar(‘55’);
    params(5) := ANYDATA.convertvarchar(‘66’);
    retx := utl_dbws.invoke(call_, params);

0 0