从 Oracle 触发器调用 Web 服务

Question

我正在尝试从数据插入的触发器调用Web服务。

下面是编写的服务调用触发器。

create or replace trigger TRG_EDI_TRANSACTIONS
before insert on edi_transactions
for each row
declare
--SOAP REQUESTS/RESPONSE
soap_req_msg VARCHAR2(2000);
-- HTTP REQUEST/RESPONSE
http_req  UTL_HTTP.req;
http_resp UTL_HTTP.resp;
buffer    varchar2(4000);
PRAGMA AUTONOMOUS_TRANSACTION;
begin
soap_req_msg := '
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:edi="http://edi.hnb.com" xmlns:xsd="http://edi.hnb.com/xsd">
<soapenv:Header/>
<soapenv:Body>
<edi:processEDIData>
<edi:request>
<xsd:bankCode>' || :NEW.Bank_Code ||
'</xsd:bankCode>
<xsd:brCode>' || :NEW.Br_Code ||
'</xsd:brCode>
<xsd:cardParticular>' || :NEW.Tran_Particular ||
'</xsd:cardParticular>
<xsd:crncyCode>' || :NEW.Crncy_Code ||
'</xsd:crncyCode>
<xsd:dateStatus>' || :NEW.Date_Status ||
'</xsd:dateStatus>
<xsd:dthInitSolId>' || :NEW.Dth_Init_Sol_Id ||
'</xsd:dthInitSolId>
<xsd:foracid>' || :NEW.Foracid ||
'</xsd:foracid>
<xsd:partTranSrlNum>' ||
:NEW.Part_Tran_Srl_Num || '</xsd:partTranSrlNum>
<xsd:partTranType>' || :NEW.Part_Tran_Type ||
'</xsd:partTranType>
<xsd:pstdDate>' || :NEW.Pstd_Date ||
'</xsd:pstdDate>
<xsd:retry>' || :NEW.Retry ||
'</xsd:retry>
<xsd:solId>' || :NEW.Sol_Id ||
'</xsd:solId>
<xsd:tranAmt>' || :NEW.Tran_Amt ||
'</xsd:tranAmt>
<xsd:tranCrncyCode>' || :NEW.Tran_Crncy_Code ||
'</xsd:tranCrncyCode>
<xsd:tranDate>' || :NEW.Tran_Date ||
'</xsd:tranDate>
<xsd:tranId>' || :NEW.Tran_Id ||
'</xsd:tranId>
<xsd:tranParticular>' || :NEW.Tran_Particular ||
'</xsd:tranParticular>
<xsd:tranRmks>' || :NEW.Tran_Rmks ||
'</xsd:tranRmks>
<xsd:tranSubType>' || :NEW.Tran_Sub_Type ||
'</xsd:tranSubType>
<xsd:tranType>' || :NEW.Tran_Type ||
'</xsd:tranType>
<xsd:trfStatus>' || :NEW.Trf_Status || '</xsd:trfStatus>
</edi:request>
</edi:processEDIData>
</soapenv:Body>
</soapenv:Envelope>
';
http_req := UTL_HTTP.begin_request('http://10.110.6.49:8305/services/prxy_edi_router_svc ',
'POST',
'HTTP/1.1');
UTL_HTTP.set_header(http_req, 'Accept-Encoding', 'gzip,deflate');
UTL_HTTP.set_header(http_req, 'Content-Type', 'text/xml');
utl_http.set_header(http_req, 'SOAPAction', 'processEDIData');
UTL_HTTP.set_header(http_req, 'Content-Length', length(soap_req_msg));
UTL_HTTP.set_header(http_req, 'Host', '10.110.6.49:8305');
UTL_HTTP.set_header(http_req, 'Connection', 'Keep-Alive');
UTL_HTTP.write_text(http_req, soap_req_msg);
http_resp := UTL_HTTP.get_response(http_req);
begin
loop
utl_http.read_line(http_resp, buffer);
dbms_output.put_line(buffer);
end loop;
utl_http.end_response(http_resp);
exception
when utl_http.end_of_body then
utl_http.end_response(http_resp);
end;
end TRG_EDI_TRANSACTIONS;

有没有办法不等待响应。

(类似于 WSO2 ESB 中的"OUT_ONLY"属性，它发送请求 并且不期望得到回应(

问题是当我通过触发器调用 Web 服务时，如果 Web 服务关闭，则会发生连接超时，触发器正在等待响应并引发错误。

如果有人可以指导这一点，将很高兴。

甲骨文版本 11.2.0.3.0。

Answer 1

您可以使用dbms_job.submit.此包计划和管理作业队列中的作业。

dbms_job.submit(job => my_job, 
what => 'my_procedure(foo);'
next_date => sysdate+1,
interval => 'sysdate+1');

您可以在此处阅读有关它的更多信息。