Absolutely! You can absolutely call a RESTful API from Oracle and tie it to a trigger for your specific "insert a record, then hit an external JSON API" scenario. Let’s walk through the practical approaches, code examples, and key things to watch out for to make this work reliably.

1. Use the Built-in UTL_HTTP Package #

This is the most common approach for older and newer Oracle versions (works back to 10g+). It lets you send HTTP/HTTPS requests directly from PL/SQL, which fits perfectly in a trigger.

Step 1: Grant Required Permissions #

First, your database user needs access to UTL_HTTP and network privileges to reach the external API:

-- Grant execute on UTL_HTTP
GRANT EXECUTE ON UTL_HTTP TO your_database_user;

-- Create a network ACL to allow access to your API host (for 11g+)
BEGIN
  DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
    acl         => 'api_access_acl.xml',
    description => 'Allow access to external REST API',
    principal   => 'YOUR_DATABASE_USER',
    is_grant    => TRUE,
    privilege   => 'connect',
    start_date  => SYSTIMESTAMP,
    end_date    => NULL
  );

  DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
    acl         => 'api_access_acl.xml',
    host        => 'your-api-domain.com', -- Replace with your API's host
    lower_port  => 443, -- Use 80 for HTTP, 443 for HTTPS
    upper_port  => 443
  );
  COMMIT;
END;
/

Step 2: Trigger with UTL_HTTP Call #

Here’s an example trigger that fires when a specific record is inserted, sends a JSON payload to your API:

CREATE OR REPLACE TRIGGER trg_after_insert_specific_record
AFTER INSERT ON your_target_table
FOR EACH ROW
WHEN (NEW.record_status = 'TRIGGER_API') -- Replace with your specific condition
DECLARE
  l_http_request  UTL_HTTP.REQ;
  l_http_response UTL_HTTP.RESP;
  l_api_url       VARCHAR2(2000) := 'https://your-api-domain.com/your-endpoint';
  l_json_payload  VARCHAR2(4000);
BEGIN
  -- Safely construct the JSON payload (avoid manual string concatenation issues!)
  l_json_payload := JSON_OBJECT(
    'record_id' VALUE :NEW.id,
    'record_name' VALUE :NEW.name,
    'status' VALUE :NEW.record_status
  ).TO_STRING();

  -- Send POST request with JSON body
  l_http_request := UTL_HTTP.BEGIN_REQUEST(l_api_url, 'POST', 'HTTP/1.1');
  UTL_HTTP.SET_HEADER(l_http_request, 'Content-Type', 'application/json');
  UTL_HTTP.SET_HEADER(l_http_request, 'Content-Length', LENGTH(l_json_payload));
  UTL_HTTP.WRITE_TEXT(l_http_request, l_json_payload);

  -- Retrieve and close the response
  l_http_response := UTL_HTTP.GET_RESPONSE(l_http_request);
  UTL_HTTP.END_RESPONSE(l_http_response);

EXCEPTION
  WHEN OTHERS THEN
    -- Log errors instead of failing the insert (adjust based on your business needs)
    INSERT INTO api_call_errors (record_id, error_message, error_timestamp)
    VALUES (:NEW.id, SQLERRM, SYSTIMESTAMP);
    -- Ensure we clean up the HTTP response even if something goes wrong
    IF UTL_HTTP.IS_OPEN(l_http_response) THEN
      UTL_HTTP.END_RESPONSE(l_http_response);
    END IF;
    -- Optional: RAISE; if you want the insert to fail if the API call fails
END;
/

2. Use DBMS_CLOUD (Oracle 19c+) #

If you’re on Oracle 19c or later, the DBMS_CLOUD package simplifies HTTPS calls and JSON handling, especially when dealing with SSL certificates. It’s more modern and requires less setup for secure connections.

Example usage in a trigger:

CREATE OR REPLACE TRIGGER trg_after_insert_with_dbms_cloud
AFTER INSERT ON your_target_table
FOR EACH ROW
WHEN (NEW.record_status = 'TRIGGER_API')
DECLARE
  l_api_url       VARCHAR2(2000) := 'https://your-api-domain.com/your-endpoint';
  l_json_payload  CLOB;
  l_response      CLOB;
BEGIN
  l_json_payload := JSON_OBJECT(
    'record_id' VALUE :NEW.id,
    'record_name' VALUE :NEW.name
  ).TO_CLOB();

  -- Send POST request
  l_response := DBMS_CLOUD.SEND_REQUEST(
    uri => l_api_url,
    method => 'POST',
    body => l_json_payload,
    headers => JSON_OBJECT('Content-Type' VALUE 'application/json')
  );

  -- Optional: Process the response
  DBMS_OUTPUT.PUT_LINE('API Response: ' || l_response);
EXCEPTION
  WHEN OTHERS THEN
    INSERT INTO api_call_errors (record_id, error_message, error_timestamp)
    VALUES (:NEW.id, SQLERRM, SYSTIMESTAMP);
END;
/

1. Synchronous vs. Asynchronous Execution #

A trigger runs synchronously with the insert transaction. If your API is slow or unavailable, it will delay the insert or even cause the transaction to fail. For production environments, I strongly recommend an asynchronous approach:

• Have the trigger write the API request details to a dedicated queue table (e.g., api_request_queue).
• Use a scheduled job (via DBMS_SCHEDULER) or Oracle Advanced Queuing (AQ) to process the queue in the background.

Here’s a quick example of this pattern:

Queue Table Setup #

CREATE TABLE api_request_queue (
  queue_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  record_id NUMBER,
  json_payload CLOB,
  api_url VARCHAR2(2000),
  status VARCHAR2(20) DEFAULT 'PENDING',
  created_at TIMESTAMP DEFAULT SYSTIMESTAMP,
  processed_at TIMESTAMP,
  error_message VARCHAR2(4000)
);

Trigger That Writes to Queue #

CREATE OR REPLACE TRIGGER trg_after_insert_queue
AFTER INSERT ON your_target_table
FOR EACH ROW
WHEN (NEW.record_status = 'TRIGGER_API')
BEGIN
  INSERT INTO api_request_queue (record_id, json_payload, api_url)
  VALUES (
    :NEW.id,
    JSON_OBJECT('record_id' VALUE :NEW.id, 'name' VALUE :NEW.name).TO_CLOB(),
    'https://your-api-domain.com/your-endpoint'
  );
END;
/

Scheduled Job to Process Queue #

CREATE OR REPLACE PROCEDURE process_api_queue
IS
  CURSOR c_pending_requests IS
    SELECT queue_id, json_payload, api_url, record_id
    FROM api_request_queue
    WHERE status = 'PENDING'
    FOR UPDATE SKIP LOCKED; -- Prevent concurrent processing conflicts
  l_response CLOB;
BEGIN
  FOR req IN c_pending_requests LOOP
    BEGIN
      l_response := DBMS_CLOUD.SEND_REQUEST(
        uri => req.api_url,
        method => 'POST',
        body => req.json_payload,
        headers => JSON_OBJECT('Content-Type' VALUE 'application/json')
      );

      -- Mark request as successful
      UPDATE api_request_queue
      SET status = 'SUCCESS', processed_at = SYSTIMESTAMP
      WHERE queue_id = req.queue_id;
    EXCEPTION
      WHEN OTHERS THEN
        -- Mark request as failed and log error
        UPDATE api_request_queue
        SET status = 'FAILED', processed_at = SYSTIMESTAMP, error_message = SQLERRM
        WHERE queue_id = req.queue_id;
    END;
  END LOOP;
  COMMIT;
END;
/

-- Create a job to run every 5 minutes
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'process_api_queue_job',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN process_api_queue; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MINUTELY;INTERVAL=5',
    enabled         => TRUE,
    comments        => 'Process pending REST API requests from queue'
  );
END;
/

2. JSON Payload Safety #

Never manually concatenate strings to build JSON (this leads to SQL injection risks and syntax errors if fields contain special characters like single quotes). Use Oracle’s JSON_OBJECT or JSON_ARRAY functions to construct valid JSON automatically.

3. SSL Certificate Handling #

If your API uses HTTPS:

• For UTL_HTTP, you’ll need to configure an Oracle wallet with the API’s root certificate.
• For DBMS_CLOUD, Oracle can often use the system’s trusted certificates automatically, but you can also specify a wallet if needed.

4. Error Handling #

Always include exception blocks to log errors instead of letting the trigger fail silently. This helps you debug API call issues without disrupting your main database operations.

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