plsql调用webservice.doc

PLSQL调用WEBSERVICE王凡2009-9-201. 一般方法utl_dbms目前网上使用最多的是使用UTL_DBWS包。1.1. 准备工作下载需要用到的jar包 10G之前:/technology/sample_code/tech/java/jsp/dbwscallout-utility.zip 10G:/technology/sample_code/tech/java/jsp/dbwscallout-utility-10R2.zip 10G 和 11I:/technology/sample_code/tech/java/jsp/dbwscallout-utility-10131.zip下载后解压出两个包1.2. 加载jar包查看下系统的java class 数量SELECT owner, status, COUNT(*) FROM dba_objects WHERE object_type = JAVA CLASS GROUP BY owner, status;我的数据库为Win的话，使用如下命令行loadjava -u sys/wangfan -r -v -f -genmissing -s -grant public F:dbws-callout-utility-10R2sqljlibdbwsa.jar F:dbws-callout-utility-10R2sqljlibdbwsclient.jar这个过程会持续一段时间。1.3. 权限授予对需要调用的用户授予权限，也可以再loadjava的时候换成需要的用户execute dbms_java.grant_permission(,SYS:java.util.PropertyPermission,xySet,write);execute dbms_java.grant_permission(,SYS:java.util.PropertyPermission,xyHost, write);execute dbms_java.grant_permission(schema,SYS:java.util.PropertyPermission,xyPort, write);execute dbms_java.grant_permission(,SYS:java.lang.RuntimePermission,accessClassInPackage.sun.util.calendar,);execute dbms_java.grant_permission(,SYS:java.lang.RuntimePermission,getClassLoader,);execute dbms_java.grant_permission(,SYS:.SocketPermission,*,connect,resolve);execute dbms_java.grant_permission(,SYS:java.util.PropertyPermission,*,read,write);execute dbms_java.grant_permission(,SYS:java.lang.RuntimePermission,setFactory,);1.4. 调用代码实现DECLARE l_service utl_dbws.service; l_call utl_dbws.CALL; l_result anydata; l_wsdl_url VARCHAR2(1024); l_service_name VARCHAR2(200); l_operation_name VARCHAR2(200); string_type_qname sys.utl_dbws.QNAME; l_input_params utl_dbws.anydata_list;BEGIN l_wsdl_url := http:/localhost:7001/BSM/BsmPickReleaseIfaceService?WSDL; l_service_name := BsmPickReleaseIfaceServiceService; l_operation_name := hello; l_service := utl_dbws.create_service(wsdl_document_location = urifactory.geturi(l_wsdl_url), service_name = l_service_name); l_call := utl_dbws.create_call(service_handle = l_service, port_name = NULL, operation_name = l_operation_name); string_type_qname := sys.utl_dbws.to_qname(/2001/XMLSchema, string); sys.utl_dbws.add_parameter(l_call, name, string_type_qname, ParameterMode.IN); sys.utl_dbws.set_return_type(l_call, string_type_qname); utl_dbws.add_parameter(l_call, name, string_type_qname, ParameterMode.IN); l_input_params(1) := anydata.convertvarchar2(12); l_result := utl_dbws.invoke(call_handle = l_call, input_params = l_input_params); utl_dbws.release_call(call_handle = l_call); utl_dbws.release_service(service_handle = l_service); dbms_output.put_line(anydata.accessvarchar2(l_result);2. 其它方法utl_http上面这种方法在服务器端使用了安全认证的时候，不能使用。在这里使用utl_http直接发送http请求，请求发送后接受回传的xml，将xml放入clob，再使用xmldom将clob解析出来。2.1. 提交请求PROCEDURE get_http_resp(p_xml_char IN VARCHAR2, p_ws_url IN VARCHAR2) IS v_http_req utl_http.req; BEGINv_http_req := utl_http.begin_request(p_ws_url, POST, utl_http.http_version_1_1);-设置安全连接信息 utl_http.set_authentication(v_http_req, wsuser, wsuserwsuser); - 保持连接状态utl_http.set_persistent_conn_support(v_http_req, TRUE);-设置编码 utl_http.set_body_charset(v_http_req, utf-8); utl_http.set_header(v_http_req, Content-Type, application/soap+xml;charset=utf-8); utl_http.set_header(v_http_req, Content-Length, length(p_xml_char); utl_http.write_text(v_http_req, p_xml_char); v_http_resp := utl_http.get_response(v_http_req); dbms_output.put_line(v_http_resp.status_code); dbms_output.put_line(v_http_resp.reason_phrase); EXCEPTION WHEN utl_http.end_of_body THEN utl_http.end_response(v_http_resp); WHEN OTHERS THEN dbms_output.put_line(SQLCODE | _ | SQLERRM); END;2.2. 将返回结果放入clobPROCEDURE get_resp_clob(p_xml_char IN VARCHAR2, p_ws_url IN VARCHAR2) IS v_ct NUMBER := 0;v_len NUMBER;-设置长度后，在循环读取的时候会每次读出这么多字符 v_replyline NVARCHAR2(4000); BEGIN get_http_resp(p_xml_char, p_ws_url); -定位lob 非常重要 不然lob会报ora-22275 SELECT 1 INTO v_resp_clob FROM dual; -打开lob对象 dbms_lob.OPEN(v_resp_clob, dbms_lob.lob_readwrite); IF (v_http_resp.status_code = utl_http.http_ok) THEN BEGIN-将返回结果循环放入clob LOOP utl_http.read_text(v_http_resp, v_replyline); dbms_output.put_line(substr(v_replyline, 1, 255); v_len := length(v_replyline); IF v_ct = 0 THEN dbms_lob.WRITE(v_resp_clob, v_len, 1, v_replyline); ELSE dbms_lob.writeappend(v_resp_clob, v_len, v_replyline); END IF; v_ct := v_ct + 1; END LOOP; utl_http.end_response(v_http_resp);-在这里不知道是什么原因，当结果全部读完后还会循环，此时会报end-of-body的exception，直接捕获，然后关闭resp。 EXCEPTION WHEN utl_http.end_of_body THEN utl_http.end_response(v_http_resp); END; END IF; dbms_output.put_line(length(v_resp_clob); -dbms_lob.CLOSE(v_clob); EXCEPTION WHEN OTHERS THEN utl_http.end_response(v_http_resp); dbms_lob.CLOSE(v_resp_clob); dbms_output.put_line(SQLCODE | _ | SQLERRM); END;2.3. 解析xmlPROCEDURE ws_clob_xmldoc(p_xml_char IN VARCHAR2, p_ws_url IN VARCHAR2) IS v_parser xmlparser.parser; v_xmldoc xmldom.domdocument; v_subelement xmldom.domelement; v_nodeliststock xmldom.domnodelist; v_xml_body VARCHAR2(100) := test; v_recordcountouter NUMBER; v_test xmldom.domnode; v_test_nodelist xmldom.domnodelist; v_test_nodect NUMBER; v_t xmldom.domnode; v_len NUMBER; BEGIN get_resp_clob(p_xml_char, p_ws_url); dbms_output.put_line(substr(v_resp_clob, 1, 255); v_parser := xmlparser.newparser; xmlparser.parseclob(v_parser, v_resp_clob); v_xmldoc := xmlparser.getdocument(v_parser); -获得根元素 v_subelement := xmldom.getdocumentelement(v_xmldoc); v_nodeliststock := xmldom.getelementsbytagname(v_subelement, v_xml_body); v_recordcountouter := xmldom.getlength(v_nodeliststock); dbms_output.put_line(v_recordcountouter); FOR i IN 0 . v_recordcountouter - 1 LOOP v_test := xmldom.item(v_nodeliststock, i); v_test_nodelist := xmldom.getchildnodes(n = v_test); v_test_nodect := xmldom.getlength(v_test_nodelist); FOR j IN 0 . v_test_nodect LOOP v_t := xmldom.item(v_test_nodelist, j); v_len := length(xmldom.getprefix(v_t);-在这里如果直接将node的名字取出会有问题，会出java：，但是可以直接取出java：字符，所以截取java： dbms_output.put_line(substr(xmldom.getnodename(v_t), v_len + 2, length(xmldom.getnodename(v_t) - v_len - 1); dbms_output.put_line(xmldom.getnodevalue(xmldom.getfirstchild(v_t); END LOOP; END LOOP; dbms_lob.CLOSE(v_resp_clob); END;2.4. 完整代码2.5. 结果2.6. 调用代码declarev_url VARCHAR2(500);soappacket VARCHAR2(4000);begindbms_output.enable(100000);-这里的地址是wsdl地址去