Menjalankan panggilan HTTP sinkron menggunakan paket UTL_HTTP di dalam trigger atau transaksi aktif adalah antipattern fatal pada Oracle Database. Pola ini menyebabkan lock escalation, konsumsi session pool hingga exhaustion, dan kegagalan transaksi inti ketika sistem hilir (downstream API) melambat.

Antipattern Panggilan Sinkron dan Anatomi Masalah

Ketika sebuah DML mengeksekusi trigger yang memanggil UTL_HTTP.REQUEST atau UTL_HTTP.BEGIN_REQUEST, koneksi database dipaksa menunggu I/O jaringan eksternal sebelum menyelesaikan instruksi. Oracle mempertahankan exclusive row-level lock pada baris yang dimodifikasi sampai transaksi menerima COMMIT atau ROLLBACK.

Jika downstream webhook mengalami latensi, cold start, atau kegagalan jaringan, efek domino berikut terjadi:

  • Hanging Row Locks: Baris data terkunci selama waktu tunggu HTTP (default timeout dapat mencapai tak terhingga jika tidak diatur). Transaksi lain yang menyentuh baris tersebut masuk ke status enq: TX - row lock contention.
  • Connection Pool Depletion: Aplikasi web atau backend kehabisan koneksi pool database karena thread tertahan menunggu sesi Oracle yang membeku di layer jaringan.
  • Error ORA-29273 dan Rollback Data Inti: Jika terjadi network glitch, Oracle melempar exception ORA-29273: HTTP request failed. Tanpa isolasi transaksi, seluruh operasi bisnis rollback hanya karena sistem notifikasi gagal merespons.

Arsitektur Solusi: Transactional Outbox Pattern

Pemisahan transaksi bisnis dari pengiriman I/O jaringan dilakukan melalui Transactional Outbox Pattern. Transaksi bisnis hanya menulis event ke tabel antrean lokal dalam satu unit kerja (ACID). Worker terpisah berbasis DBMS_SCHEDULER mengambil antrean secara asinkron dan mengirimkannya via UTL_HTTP.

1. DDL Skema Antrean Outbox dan DLQ

Tabel ini menyimpan status antrean, pelacakan exponential backoff, payload, idempotency key, dan penampung pesan gagal permanen (Dead-Letter Queue / DLQ).

CREATE TABLE webhook_outbox (
    id              NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    event_type      VARCHAR2(64)   NOT NULL,
    idempotency_key VARCHAR2(128)  NOT NULL UNIQUE,
    target_url      VARCHAR2(1000) NOT NULL,
    payload         CLOB           NOT NULL,
    status          VARCHAR2(20)   DEFAULT 'PENDING' NOT NULL,
    retry_count     NUMBER(3)      DEFAULT 0 NOT NULL,
    max_retries     NUMBER(3)      DEFAULT 5 NOT NULL,
    next_retry_at   DATE           DEFAULT SYSDATE NOT NULL,
    last_error      VARCHAR2(4000),
    created_at      TIMESTAMP      DEFAULT SYSTIMESTAMP NOT NULL,
    updated_at      TIMESTAMP      DEFAULT SYSTIMESTAMP NOT NULL,
    CONSTRAINT chk_outbox_status CHECK (status IN ('PENDING', 'PROCESSING', 'SUCCESS', 'FAILED', 'DLQ'))
);

CREATE INDEX idx_webhook_outbox_poll 
ON webhook_outbox (status, next_retry_at);

Konfigurasi UTL_HTTP: Timeout dan HTTPS Wallet

Sebelum mengirimkan data keluar, runtime HTTP Oracle harus dikonfigurasi secara ketat. Dua parameter wajib diterapkan: transfer timeout dan kredensial Oracle Wallet untuk HTTPS.

  • Timeout Ekplisit: UTL_HTTP.SET_TRANSFER_TIMEOUT(5) membatasi waktu I/O socket maksimal 5 detik.
  • Detailed Exception Support: Membuka deskripsi root cause spesifik seperti timeout ORA-29276 daripada masking generic error.
  • HTTPS Wallet: Dibutuhkan oleh Oracle untuk validasi sertifikat SSL/TLS.
-- Konfigurasi Wallet dan Timeout Global
BEGIN
    UTL_HTTP.SET_WALLET('file:/opt/oracle/wallets/webhook_wallet', 'WalletPassword123');
    UTL_HTTP.SET_DETAILED_EXCP_SUPPORT(TRUE);
END;
/

Implementasi Prosedur Worker dengan Isolasi Exception

Worker memproses data menggunakan klausa FOR UPDATE SKIP LOCKED. Pola ini mencegah benturan antar-sesi jika worker dijalankan paralel. Seluruh error jaringan ditangkap dan dijadwalkan ulang dengan interval eksponensial (2, 4, 8, 16 menit).

CREATE OR REPLACE PACKAGE pkg_webhook_engine AS
    PROCEDURE process_outbox_queue(p_batch_size IN NUMBER DEFAULT 50);
END pkg_webhook_engine;
/

CREATE OR REPLACE PACKAGE BODY pkg_webhook_engine AS

    PROCEDURE dispatch_http(
        p_url             IN VARCHAR2,
        p_payload         IN CLOB,
        p_idempotency_key IN VARCHAR2
    ) IS
        v_req  UTL_HTTP.REQ;
        v_res  UTL_HTTP.RESP;
        v_buff VARCHAR2(32767);
    BEGIN
        UTL_HTTP.SET_TRANSFER_TIMEOUT(5);
        v_req := UTL_HTTP.BEGIN_REQUEST(p_url, 'POST', 'HTTP/1.1');
        
        UTL_HTTP.SET_HEADER(v_req, 'Content-Type', 'application/json; charset=UTF-8');
        UTL_HTTP.SET_HEADER(v_req, 'Content-Length', DBMS_LOB.GETLENGTH(p_payload));
        UTL_HTTP.SET_HEADER(v_req, 'X-Idempotency-Key', p_idempotency_key);
        
        -- Stream payload CLOB ke HTTP stream
        DECLARE
            v_offset NUMBER := 1;
            v_chunk  NUMBER := 8000;
        BEGIN
            WHILE v_offset <= DBMS_LOB.GETLENGTH(p_payload) LOOP
                DBMS_LOB.READ(p_payload, v_chunk, v_offset, v_buff);
                UTL_HTTP.WRITE_TEXT(v_req, v_buff);
                v_offset := v_offset + v_chunk;
            END LOOP;
        END;
        
        v_res := UTL_HTTP.GET_RESPONSE(v_req);
        
        -- Evaluasi respons downstream
        IF v_res.status_code NOT BETWEEN 200 AND 299 THEN
            UTL_HTTP.END_RESPONSE(v_res);
            RAISE_APPLICATION_ERROR(-20001, 'Remote endpoint returned HTTP ' || v_res.status_code);
        END IF;
        
        UTL_HTTP.END_RESPONSE(v_res);
    EXCEPTION
        WHEN OTHERS THEN
            -- Memastikan response handle selalu dibersihkan guna mencegah session leak
            BEGIN
                UTL_HTTP.END_RESPONSE(v_res);
            EXCEPTION
                WHEN OTHERS THEN NULL;
            END;
            RAISE;
    END dispatch_http;

    PROCEDURE process_outbox_queue(p_batch_size IN NUMBER DEFAULT 50) IS
        CURSOR c_records IS
            SELECT id, target_url, payload, idempotency_key, retry_count, max_retries
            FROM webhook_outbox
            WHERE status IN ('PENDING', 'FAILED')
              AND next_retry_at <= SYSDATE
            ORDER BY next_retry_at ASC
            FETCH FIRST p_batch_size ROWS ONLY
            FOR UPDATE SKIP LOCKED;

        TYPE t_outbox IS TABLE OF c_records%ROWTYPE;
        v_list t_outbox;
    BEGIN
        OPEN c_records;
        FETCH c_records BULK COLLECT INTO v_list;
        CLOSE c_records;

        FOR i IN 1 .. v_list.COUNT LOOP
            BEGIN
                dispatch_http(
                    v_list(i).target_url,
                    v_list(i).payload,
                    v_list(i).idempotency_key
                );

                UPDATE webhook_outbox
                SET status = 'SUCCESS',
                    updated_at = SYSTIMESTAMP
                WHERE id = v_list(i).id;
                
            EXCEPTION
                WHEN OTHERS THEN
                    DECLARE
                        v_err VARCHAR2(4000) := SUBSTR(SQLERRM, 1, 4000);
                        v_next_retry DATE;
                        v_new_status VARCHAR2(20);
                    BEGIN
                        -- Exponential backoff: base interval 10 detik * 2^(retry_count)
                        IF v_list(i).retry_count + 1 >= v_list(i).max_retries THEN
                            v_new_status := 'DLQ';
                            v_next_retry := NULL;
                        ELSE
                            v_new_status := 'FAILED';
                            v_next_retry := SYSDATE + (POWER(2, v_list(i).retry_count + 1) * 10 / 86400);
                        END IF;

                        UPDATE webhook_outbox
                        SET status = v_new_status,
                            retry_count = retry_count + 1,
                            next_retry_at = NVL(v_next_retry, next_retry_at),
                            last_error = v_err,
                            updated_at = SYSTIMESTAMP
                        WHERE id = v_list(i).id;
                    END;
            END;
            COMMIT; -- Commit per baris untuk menjaga batas transaksi isolatif
        END LOOP;
    END process_outbox_queue;

END pkg_webhook_engine;
/

Otomatisasi Eksekusi via DBMS_SCHEDULER

Pola ini membutuhkan scheduler job yang berjalan periodik untuk memicu pengiriman tanpa intervensi transaksi utama.

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'JOB_WEBHOOK_DISPATCHER',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN pkg_webhook_engine.process_outbox_queue(100); END;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=SECONDLY; INTERVAL=10',
        enabled         => TRUE,
        comments        => 'Worker background untuk pengiriman outbox webhook via UTL_HTTP'
    );
END;
/

Troubleshooting Masalah Umum UTL_HTTP

  1. ORA-29273 & ORA-29276: Disebabkan timeout transfer socket. Pastikan batas SET_TRANSFER_TIMEOUT tidak lebih besar dari 10 detik dan endpoint hilir mampu menerima volume koneksi.
  2. ORA-29024: Certificate validation failure: Terjadi jika Root CA atau Intermediate CA dari HTTPS target belum diimpor ke dalam Oracle Wallet menggunakan orapki utility.
  3. ORA-24247: Network access denied by access control list (ACL): Oracle 11g ke atas mewajibkan pemberian izin jaringan melalui paket DBMS_NETWORK_ACL_ADMIN atau DBMS_HOST_ACL untuk host target.
Alternatif: Jika sistem memiliki infrastruktur Kafka atau RabbitMQ terpisah, delegasikan penulisan event menggunakan Change Data Capture (Oracle GoldenGate/Debezium) untuk meniadakan seluruh eksekusi HTTP dari database.