Row-level database activity can drive outbonud HTTP calls by wiring a trigger to invoke a PL/SQL procedure that performs the request via UTL_HTTP. Network access must be permitted using ACLs, and character-set handling needs attention to prevent data corruption.

Trigger to invoke an HTTP request

CREATE OR REPLACE TRIGGER trg_req_http_notify
AFTER INSERT ON REQUESTHANDLERINFO
FOR EACH ROW
BEGIN
  pkg_http_bridge.send_request(
    p_req_id => :NEW.id,
    p_params => :NEW.params
  );
END trg_req_http_notify;

Procedure to issue a POST and read the response

CREATE OR REPLACE PACKAGE pkg_http_bridge AS
  PROCEDURE send_request(p_req_id IN VARCHAR2, p_params IN VARCHAR2);
END pkg_http_bridge;
/

CREATE OR REPLACE PACKAGE BODY pkg_http_bridge AS
  PROCEDURE send_request(p_req_id IN VARCHAR2, p_params IN VARCHAR2) IS
    l_req       UTL_HTTP.req;
    l_resp      UTL_HTTP.resp;
    l_line      VARCHAR2(32767);
    l_base_url  VARCHAR2(1024) :=
      'http://192.168.0.156:8080/bzjw_platform/f/responseresult/responseResult/invokeOutRequest';

    -- Parameters are placed on the query string to avoid body-encoding issues in non-UTF8 databases
    l_query     VARCHAR2(4000) :=
      'rid=' || UTL_URL.escape(p_req_id, TRUE) ||
      '&params=' || UTL_URL.escape(p_params, TRUE);

    -- Minimal payload for POST semantics
    l_payload   VARCHAR2(200) := 'ping=1';
    l_raw       RAW(32767) := UTL_RAW.cast_to_raw(l_payload);
  BEGIN
    l_req := UTL_HTTP.begin_request(url => l_base_url || '?' || l_query, method => 'POST');

    UTL_HTTP.set_body_charset('UTF-8');
    UTL_HTTP.set_header(l_req, 'Content-Type', 'application/x-www-form-urlencoded');
    UTL_HTTP.set_header(l_req, 'Content-Length', TO_CHAR(UTL_RAW.length(l_raw)));
    UTL_HTTP.set_header(l_req, 'Keep-Alive', 'timeout=1');

    UTL_HTTP.write_raw(l_req, l_raw);

    l_resp := UTL_HTTP.get_response(l_req);
    LOOP
      UTL_HTTP.read_line(l_resp, l_line, TRUE);
      DBMS_OUTPUT.put_line(l_line);
    END LOOP;
  EXCEPTION
    WHEN UTL_HTTP.end_of_body THEN
      UTL_HTTP.end_response(l_resp);
  END send_request;
END pkg_http_bridge;
/

Alternaitve: POST body encoded to UTF-8 (for non-UTF8 database charsets)

When the database character set is not UTF-8 (for example, GBK), POST body content can be corrupted if sent as VARCHAR2. Convert the body to UTF-8 bytes explicit and declare the charset in the Content-Type header.

CREATE OR REPLACE PROCEDURE proc_http_post_utf8(
  p_req_id  IN VARCHAR2,
  p_params  IN VARCHAR2
) AS
  l_req       UTL_HTTP.req;
  l_resp      UTL_HTTP.resp;
  l_line      VARCHAR2(32767);
  l_url       VARCHAR2(1024) :=
    'http://192.168.0.156:8080/bzjw_platform/f/responseresult/responseResult/invokeOutRequest';

  -- Send parameters in the body
  l_form      VARCHAR2(4000) := 'rid=' || p_req_id || '&params=' || p_params;
  l_raw_body  RAW(32767) := UTL_I18N.string_to_raw(l_form, 'AL32UTF8');
BEGIN
  l_req := UTL_HTTP.begin_request(l_url, 'POST');

  UTL_HTTP.set_header(l_req, 'Content-Type', 'application/x-www-form-urlencoded; charset=UTF-8');
  UTL_HTTP.set_header(l_req, 'Content-Length', TO_CHAR(UTL_RAW.length(l_raw_body)));
  UTL_HTTP.write_raw(l_req, l_raw_body);

  l_resp := UTL_HTTP.get_response(l_req);
  LOOP
    UTL_HTTP.read_line(l_resp, l_line, TRUE);
    DBMS_OUTPUT.put_line(l_line);
  END LOOP;
EXCEPTION
  WHEN UTL_HTTP.end_of_body THEN
    UTL_HTTP.end_response(l_resp);
END proc_http_post_utf8;
/

Network ACL setup

Oracle requires explicit ACL configuration to allow UTL_HTTP outbound access.

Create the ACL:

BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl(
    acl         => 'acl_http_req.xml',
    description => 'HTTP access',
    principal   => 'CONNECT',
    is_grant    => TRUE,
    privilege   => 'connect',
    start_date  => NULL,
    end_date    => NULL
  );
END;
/

Grant a specific user privileges in the ACL:

BEGIN
  DBMS_NETWORK_ACL_ADMIN.add_privilege(
    acl        => 'acl_http_req.xml',
    principal  => 'SD_JY',  -- use the exact case of the database username
    is_grant   => TRUE,
    privilege  => 'connect',
    start_date => NULL,
    end_date   => NULL
  );
END;
/

Bind the ACL to the target host and port:

BEGIN
  DBMS_NETWORK_ACL_ADMIN.assign_acl(
    acl        => 'acl_http_req.xml',
    host       => '192.168.0.156',
    lower_port => 8080,
    upper_port => NULL
  );
END;
/

Verify ACL artifacts

List ACL files stored in the data dictionary:

SELECT any_path
FROM   resource_view
WHERE  any_path LIKE '/sys/acls/%.xml';

Inspect granted privileges:

SELECT acl,
       principal,
       privilege,
       is_grant,
       TO_CHAR(start_date, 'DD-MON-YYYY') AS start_date,
       TO_CHAR(end_date,   'DD-MON-YYYY') AS end_date
FROM   dba_network_acl_privileges;