Oracle Form September 10, 2026 hannanmentor@gmail.com 5 min read

Oracle Forms Modernization: Calling Modern REST APIs from Oracle Forms 12c Using UTL_HTTP and JSON

Across government agencies, manufacturing facilities, logistics hubs, and financial institutions worldwide, Oracle Forms 12c continues to power critical day-to-day transaction processing. However, modern business requirements frequently mandate communicating with external microservices, cloud ERPs, and third-party SaaS providers via RESTful JSON APIs.

Many development teams assume that integrating modern web services into Oracle Forms requires a total rewrite or cumbersome middle-tier proxies. In reality, Oracle Forms runs on a powerful foundation: the Oracle Database and PL/SQL. By leveraging database-native UTL_HTTP alongside Oracle 12c/19c JSON functions, you can invoke any HTTPS REST API directly from your Forms triggers and blocks seamlessly.

1. Architecture: How Oracle Forms Communicates with REST APIs

When an operator clicks a button or triggers a validation event in an Oracle Form (such as WHEN-BUTTON-PRESSED or KEY-COMMIT), Forms executes client-side PL/SQL. For REST integration, the best architectural pattern is to encapsulate the HTTP call inside a Database Package, allowing the database engine to handle network sockets, TLS encryption, and response parsing.

2. Configuring the Oracle Wallet for HTTPS / TLS Security

Because virtually all modern REST APIs require TLS (HTTPS), the Oracle Database must possess an Oracle Wallet containing the root Certificate Authority (CA) certificates of the target endpoint. Without this wallet, UTL_HTTP will terminate with ORA-29024: Certificate validation failure.

# Create an Oracle Wallet using orapki on the database server
orapki wallet create -wallet /u01/app/oracle/wallets/rest_wallet -pwd MySecretPassword123 -auto_login

# Add Trusted Root Certificate (e.g., DigiCert or Let's Encrypt Root CA)
orapki wallet add -wallet /u01/app/oracle/wallets/rest_wallet -trusted_cert -cert /tmp/isrgrootx1.pem -pwd MySecretPassword123

3. Building the Production PL/SQL REST Invoker Package

Below is a production-hardened PL/SQL function designed to transmit JSON POST requests and extract response bodies cleanly:

CREATE OR REPLACE PACKAGE BODY pkg_rest_client AS

    FUNCTION send_post_request(
        p_endpoint_url IN VARCHAR2,
        p_json_body    IN CLOB,
        p_bearer_token IN VARCHAR2
    ) RETURN CLOB IS
        v_http_req     UTL_HTTP.req;
        v_http_resp    UTL_HTTP.resp;
        v_buffer       VARCHAR2(32767);
        v_response_clob CLOB;
    BEGIN
        -- Configure SSL Wallet
        UTL_HTTP.set_wallet('file:/u01/app/oracle/wallets/rest_wallet', 'MySecretPassword123');
        UTL_HTTP.set_transfer_timeout(15); -- 15 seconds timeout

        -- Open Request
        v_http_req := UTL_HTTP.begin_request(p_endpoint_url, 'POST', 'HTTP/1.1');
        UTL_HTTP.set_header(v_http_req, 'Content-Type', 'application/json; charset=utf-8');
        UTL_HTTP.set_header(v_http_req, 'Authorization', 'Bearer ' || p_bearer_token);
        UTL_HTTP.set_header(v_http_req, 'Content-Length', LENGTH(p_json_body));

        -- Send Payload
        UTL_HTTP.write_text(v_http_req, p_json_body);

        -- Read Response
        v_http_resp := UTL_HTTP.get_response(v_http_req);
        DBMS_LOB.createtemporary(v_response_clob, TRUE);

        BEGIN
            LOOP
                UTL_HTTP.read_text(v_http_resp, v_buffer, 32767);
                DBMS_LOB.writeappend(v_response_clob, LENGTH(v_buffer), v_buffer);
            END LOOP;
        EXCEPTION
            WHEN UTL_HTTP.end_of_body THEN
                UTL_HTTP.end_response(v_http_resp);
        END;

        RETURN v_response_clob;
    EXCEPTION
        WHEN OTHERS THEN
            IF v_http_resp.status_code IS NOT NULL THEN
                UTL_HTTP.end_response(v_http_resp);
            END IF;
            RAISE;
    END send_post_request;

END pkg_rest_client;
/

4. Invoking from Oracle Forms Triggers

Inside Oracle Forms Developer, add the following logic inside a button’s WHEN-BUTTON-PRESSED trigger to call the REST service and instantly update screen items with returned status data:

-- WHEN-BUTTON-PRESSED Trigger in Oracle Forms
DECLARE
    v_request_json  CLOB;
    v_response_json CLOB;
    v_auth_status   VARCHAR2(50);
    v_tracking_no   VARCHAR2(100);
BEGIN
    -- Construct payload from current form item values
    v_request_json := '{"order_id": ' || :ORDERS.ORDER_ID || 
                      ', "amount": ' || :ORDERS.ORDER_TOTAL || '}';

    -- Call database REST client package
    v_response_json := pkg_rest_client.send_post_request(
        p_endpoint_url => 'https://api.paymentservice.com/v1/charge',
        p_json_body    => v_request_json,
        p_bearer_token => 'sec_live_9921kldms882'
    );

    -- Parse JSON response directly using Oracle 12c+ JSON_VALUE
    SELECT JSON_VALUE(v_response_json, '$.status'),
           JSON_VALUE(v_response_json, '$.confirmation_code')
    INTO v_auth_status, v_tracking_no
    FROM dual;

    -- Update Oracle Forms fields
    :ORDERS.PAYMENT_STATUS := v_auth_status;
    :ORDERS.AUTH_CODE      := v_tracking_no;
    
    SET_ITEM_PROPERTY('ORDERS.AUTH_CODE', VISUAL_ATTRIBUTE, 'VA_SUCCESS');
    SYNCHRONIZE;

    MESSAGE('Payment authorized successfully: ' || v_tracking_no);
EXCEPTION
    WHEN OTHERS THEN
        MESSAGE('REST Error: ' || SQLERRM);
        RAISE FORM_TRIGGER_FAILURE;
END;

5. Modernizing Legacy Forms Without Rewriting

Using this architecture, existing enterprise Forms installations can integrate with cloud document storage (AWS S3, Azure Blob), modern identity providers (Okta, Keycloak), SMS alerts (Twilio), and AI endpoints—extending the lifespan and utility of proven business systems while modernizing their digital capabilities.

Previous Next