需要触发某张表，当新增或修改这张表时，调用存储过程，然后存储过程再调用webservice。
create or replace trigger userpostexchange_t
after insert or update on userpostexchange for each row declare cn varchar2(50); get_return varchar2(8); begin cn:=:new.cn; amc_cndzzf_pkg.autoApprovalTask(cn,get_return); EXCEPTION WHEN OTHERS THEN NULL; end; create or replace package amc_cndzzf_pkg is url Varchar2(300) := 'http://127.0.0.1:7001/fsscRytb/services/PersonSynchroImpl'; --webservice的wsdl地址. namespace Varchar2(1000) := 'xmlns="http://aaa.com" xmlns:xsd="http://www.w3.org/2001/XMLSchema"'; action Varchar2(300) := ''; procedure autoApprovalTask(cn in varchar2,to_return out varchar2) is req common_soap.request; resp common_soap.response; str varchar2(2000); begin str := '-----test------'; req := common_soap.new_request('addPerson', namespace); common_soap.add_parameter(req, 'in0', '', cn); resp := common_soap.invoke(req, url, action); to_return := common_soap.get_return_value(resp, 'addPersonResponse', namespace); end; end; CREATE OR REPLACE PACKAGE Body common_soap AS FUNCTION new_request(method IN VARCHAR2, namespace IN VARCHAR2) RETURN request AS req request; BEGIN req.method := method; req.namespace := namespace; RETURN req; END; PROCEDURE add_parameter(req IN OUT NOCOPY request, name IN VARCHAR2, type IN VARCHAR2, value IN VARCHAR2) AS BEGIN req.body := req.body || '<' || name || ' ' || type || '>' || value || '</' || name || '>'; END; PROCEDURE generate_envelope(req IN OUT NOCOPY request, env IN OUT NOCOPY VARCHAR2) AS BEGIN env := '<?xml version = "1.0" encoding = "gbk"?> <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <SOAP-ENV:Body><' || req.method || ' ' || req.namespace || '>' || req.body || '</' || req.method || '></SOAP-ENV:Body></SOAP-ENV:Envelope>'; END; PROCEDURE show_envelope(env IN VARCHAR2) AS i pls_integer; len pls_integer; BEGIN i := 1; len := length(env); WHILE (i <= len) LOOP dbms_output.put_line(substr(env, i, 60)); i := i + 60; END LOOP; END; PROCEDURE check_fault(resp IN OUT NOCOPY response) AS fault_node xmltype; fault_code VARCHAR2(256); fault_string VARCHAR2(32767); BEGIN fault_node := resp.doc.extract('/soap:Fault', 'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/'); IF (fault_node IS NOT NULL) THEN fault_code := fault_node.extract('/soap:Fault/faultcode/child::text()', 'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/') .getstringval(); fault_string := fault_node.extract('/soap:Fault/faultstring/child::text()', 'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/') .getstringval(); raise_application_error(-20000, fault_code || ' - ' || fault_string); END IF; END; FUNCTION invoke(req IN OUT NOCOPY request, url IN VARCHAR2, action IN VARCHAR2) RETURN response AS env VARCHAR2(32767); http_req utl_http.req; http_resp utl_http.resp; resp response; BEGIN generate_envelope(req, env); show_envelope(env); http_req := utl_http.begin_request(url, 'POST', 'HTTP/1.1'); utl_http.set_header(http_req, 'Content-Type', 'text/xml'); utl_http.set_header(http_req, 'Content-Length', length(env)); utl_http.set_header(http_req, 'SOAPAction', action); utl_http.write_text(http_req, env); -- insert into test values(to_char(env)); -- commit; http_resp := utl_http.get_response(http_req); utl_http.read_text(http_resp, env); utl_http.end_response(http_resp); resp.doc := xmltype.createxml(env); resp.doc := resp.doc.extract('/soap:Envelope/soap:Body/child::node()', 'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"'); if resp.doc is not null then show_envelope(resp.doc.getstringval()); check_fault(resp); else dbms_output.put_line('resp.doc is null'); end if; RETURN resp; END; FUNCTION get_return_value(resp IN OUT NOCOPY response, name IN VARCHAR2, namespace IN VARCHAR2) RETURN VARCHAR2 is v_xml varchar2(4000); to_return varchar2(4000); BEGIN if resp.doc is not null then --v_xml:= substr(resp.doc.extract('//text()',namespace).getstringval(),0,3000); v_xml := substr(resp.doc.getstringval(), 0, 3000); --dbms_output.put_line('---------'||v_xml); to_return := v_xml; else to_return := '1'; END if; return to_return; end; END;