Oracle触发器调用WebService如何实现无需等待响应？

Hey there! Let's fix this trigger so it doesn't hang or error out when the WebService is down. The goal is to implement a fire-and-forget (OUT_ONLY) behavior, just like WSO2 ESB does. Here are two solid approaches for Oracle 11.2.0.3.0:

Approach 1: Modify UTL_HTTP to Send Without Waiting for Response #

This method tweaks your existing trigger to send the request and immediately close the connection, ignoring any response or errors. It uses your existing autonomous transaction but removes the response-waiting logic.

create or replace trigger TRG_EDI_TRANSACTIONS 
before insert on edi_transactions 
for each row 
declare 
    -- SOAP REQUEST
    soap_req_msg VARCHAR2(2000); 
    -- HTTP REQUEST OBJECT
    http_req UTL_HTTP.req; 
    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>';

    -- Set ultra-short timeouts to avoid blocking
    UTL_HTTP.set_transfer_timeout(1);
    UTL_HTTP.set_response_timeout(1);

    begin
        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');
        -- Tell server to close connection immediately after receiving request
        UTL_HTTP.set_header(http_req, 'Connection', 'Close');

        -- Send the request
        UTL_HTTP.write_text(http_req, soap_req_msg);

        -- Skip waiting for response, just close the request
        UTL_HTTP.end_request(http_req);
    exception
        when others then
            -- Ignore all errors so the trigger doesn't fail the main transaction
            null;
            -- Ensure we clean up the HTTP request even if something goes wrong
            UTL_HTTP.end_request(http_req);
    end;

    COMMIT; -- Required for autonomous transaction
end TRG_EDI_TRANSACTIONS;

Key points for this approach:

• Short timeouts: Ensures the trigger doesn't hang if the WebService is unresponsive
• Connection: Close: Tells the server to terminate the connection right after receiving the request
• Error swallowing: Catches all exceptions so the main insert transaction isn't disrupted
• Autonomous transaction commit: Needed to finalize the independent trigger transaction

Approach 2: Use DBMS_SCHEDULER for Fully Asynchronous Execution #

This is the more robust option: the trigger creates a background job to call the WebService, then immediately finishes. The job runs independently, so even if the WebService is down, your insert transaction isn't affected.

Step 1: Create a stored procedure for the WebService call #

create or replace procedure PROC_CALL_EDI_WEBSERVICE(
    p_bank_code edi_transactions.Bank_Code%type,
    p_br_code edi_transactions.Br_Code%type,
    p_tran_particular edi_transactions.Tran_Particular%type,
    p_crncy_code edi_transactions.Crncy_Code%type,
    p_date_status edi_transactions.Date_Status%type,
    p_dth_init_sol_id edi_transactions.Dth_Init_Sol_Id%type,
    p_foracid edi_transactions.Foracid%type,
    p_part_tran_srl_num edi_transactions.Part_Tran_Srl_Num%type,
    p_part_tran_type edi_transactions.Part_Tran_Type%type,
    p_pstd_date edi_transactions.Pstd_Date%type,
    p_retry edi_transactions.Retry%type,
    p_sol_id edi_transactions.Sol_Id%type,
    p_tran_amt edi_transactions.Tran_Amt%type,
    p_tran_crncy_code edi_transactions.Tran_Crncy_Code%type,
    p_tran_date edi_transactions.Tran_Date%type,
    p_tran_id edi_transactions.Tran_Id%type,
    p_tran_rmks edi_transactions.Tran_Rmks%type,
    p_tran_sub_type edi_transactions.Tran_Sub_Type%type,
    p_tran_type edi_transactions.Tran_Type%type,
    p_trf_status edi_transactions.Trf_Status%type
) as
    soap_req_msg VARCHAR2(2000);
    http_req UTL_HTTP.req;
    http_resp UTL_HTTP.resp;
    buffer varchar2(4000);
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>' || p_bank_code || '</xsd:bankCode>
                    <xsd:brCode>' || p_br_code || '</xsd:brCode>
                    <xsd:cardParticular>' || p_tran_particular || '</xsd:cardParticular>
                    <xsd:crncyCode>' || p_crncy_code || '</xsd:crncyCode>
                    <xsd:dateStatus>' || p_date_status || '</xsd:dateStatus>
                    <xsd:dthInitSolId>' || p_dth_init_sol_id || '</xsd:dthInitSolId>
                    <xsd:foracid>' || p_foracid || '</xsd:foracid>
                    <xsd:partTranSrlNum>' || p_part_tran_srl_num || '</xsd:partTranSrlNum>
                    <xsd:partTranType>' || p_part_tran_type || '</xsd:partTranType>
                    <xsd:pstdDate>' || p_pstd_date || '</xsd:pstdDate>
                    <xsd:retry>' || p_retry || '</xsd:retry>
                    <xsd:solId>' || p_sol_id || '</xsd:solId>
                    <xsd:tranAmt>' || p_tran_amt || '</xsd:tranAmt>
                    <xsd:tranCrncyCode>' || p_tran_crncy_code || '</xsd:tranCrncyCode>
                    <xsd:tranDate>' || p_tran_date || '</xsd:tranDate>
                    <xsd:tranId>' || p_tran_id || '</xsd:tranId>
                    <xsd:tranParticular>' || p_tran_particular || '</xsd:tranParticular>
                    <xsd:tranRmks>' || p_tran_rmks || '</xsd:tranRmks>
                    <xsd:tranSubType>' || p_tran_sub_type || '</xsd:tranSubType>
                    <xsd:tranType>' || p_tran_type || '</xsd:tranType>
                    <xsd:trfStatus>' || p_trf_status || '</xsd:trfStatus>
                </edi:request>
            </edi:processEDIData>
        </soapenv:Body>
    </soapenv:Envelope>';

    begin
        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);

        loop
            utl_http.read_line(http_resp, buffer);
            -- Optional: Log the response to a table here if needed
        end loop;
        utl_http.end_response(http_resp);
    exception
        when utl_http.end_of_body then
            utl_http.end_response(http_resp);
        when others then
            -- Optional: Log errors to a table for debugging
            -- INSERT INTO edi_ws_errors (tran_id, error_msg, error_date) VALUES (p_tran_id, SQLERRM, SYSDATE);
            utl_http.end_response(http_resp);
            raise;
    end;
end PROC_CALL_EDI_WEBSERVICE;

Step 2: Update the trigger to create a background job #

create or replace trigger TRG_EDI_TRANSACTIONS 
before insert on edi_transactions 
for each row 
declare 
    v_job_name VARCHAR2(100) := 'EDI_WS_JOB_' || TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') || '_' || :NEW.Tran_Id;
begin 
    -- Create an immediate one-time job to run the WebService call
    DBMS_SCHEDULER.create_job(
        job_name        => v_job_name,
        job_type        => 'STORED_PROCEDURE',
        job_action      => 'PROC_CALL_EDI_WEBSERVICE',
        number_of_arguments => 20,
        start_date      => SYSTIMESTAMP,
        enabled         => TRUE,
        auto_drop       => TRUE -- Auto-delete the job after execution
    );

    -- Pass all required parameters to the stored procedure
    DBMS_SCHEDULER.set_job_argument_value(v_job_name, 1, :NEW.Bank_Code);
    DBMS_SCHEDULER.set_job_argument_value(v_job_name, 2, :NEW.Br_Code);
    DBMS_SCHEDULER.set_job_argument_value(v_job_name, 3, :NEW.Tran_Particular);
    DBMS_SCHEDULER.set_job_argument_value(v_job_name, 4, :NEW.Crncy_Code);
    DBMS_SCHEDULER.set_job_argument_value(v_job_name, 5, :NEW.Date_Status);
    DBMS_SCHEDULER.set_job_argument_value(v_job_name, 6, :NEW.Dth_Init_Sol_Id);
    DBMS_SCHEDULER.set_job_argument_value(v_job_name, 7, :NEW.Foracid);
    DBMS_SCHEDULER.set_job_argument_value(v_job_name, 8, :NEW.Part_Tran_Srl_Num);
    DBMS_SCHEDULER.set_job_argument_value(v_job_name, 9, :NEW.Part_Tran_Type);
    DBMS_SCHEDULER.set_job_argument_value(v_job_name, 10, :NEW.Pstd_Date);
    DBMS_SCHEDULER.set_job_argument_value(v_job_name, 11, :NEW.Retry);
    DBMS_SCHEDULER.set_job_argument_value(v_job_name, 12, :NEW.Sol_Id);
    DBMS_SCHEDULER.set_job_argument_value(v_job_name, 13, :NEW.Tran_Amt);
    DBMS_SCHEDULER.set_job_argument_value(v_job_name, 14, :NEW.Tran_Crncy_Code);
    DBMS_SCHEDULER.set_job_argument_value(v_job_name, 15, :NEW.Tran_Date);
    DBMS_SCHEDULER.set_job_argument_value(v_job_name, 16, :NEW.Tran_Id);
    DBMS_SCHEDULER.set_job_argument_value(v_job_name, 17, :NEW.Tran_Rmks);
    DBMS_SCHEDULER.set_job_argument_value(v_job_name, 18, :NEW.Tran_Sub_Type);
    DBMS_SCHEDULER.set_job_argument_value(v_job_name, 19, :NEW.Tran_Type);
    DBMS_SCHEDULER.set_job_argument_value(v_job_name, 20, :NEW.Trf_Status);
end TRG_EDI_TRANSACTIONS;

Key benefits of this approach:

• Full decoupling: The trigger finishes instantly, no dependency on WebService availability
• Error handling: You can easily add logging for failed calls to debug issues later
• Scalability: Background jobs are managed by Oracle's scheduler, so you don't have to handle threading or resource management

Recommendation #

• Use Approach 1 if you need a quick, simple fix with minimal code changes
• Use Approach 2 if you want a robust, maintainable solution with proper error logging and full isolation from the main transaction

内容的提问来源于stack exchange，提问作者Yasothar