Send WhatsApp Messages from PL/SQL

Why send WhatsApp from PL/SQL?

Many enterprise applications run on Oracle Database with business logic written in PL/SQL packages, triggers, and stored procedures. Instead of building a separate microservice or scheduler, you can call the Zaptilo WhatsApp API directly from PL/SQL using Oracle's built-in UTL_HTTP package.

• Send WhatsApp from triggers, jobs, packages, or anonymous blocks
• Real-time order, invoice, and payment notifications
• OTP delivery from your existing authentication procedure
• No external scripts, cron jobs, or message queues needed
• Full audit trail in your own database tables

Prerequisites

1. Oracle Database 11g or higher with UTL_HTTP available
2. A free Zaptilo account with an API token (Dashboard → Developer Tools → Access Tokens)
3. An approved WhatsApp template in your Zaptilo account
4. Network ACL permission for the database user to call web.zaptilo.ai

Step 1: Grant Network ACL Permission

Oracle 11g and above require an Access Control List (ACL) to allow PL/SQL to make outbound HTTP calls. Run this as a privileged user (e.g. SYS):

BEGIN
    DBMS_NETWORK_ACL_ADMIN.append_host_ace(
        host => 'web.zaptilo.ai',
        ace  => xs$ace_type(
            privilege_list => xs$name_list('http', 'connect', 'resolve'),
            principal_name => 'YOUR_DB_USER',
            principal_type => xs_acl.ptype_db
        )
    );
END;
/

Replace YOUR_DB_USER with the schema that will run the procedure.

Step 2: Create the Message Log Table

We'll log every API call — both successes and failures — for auditing and debugging.

CREATE TABLE message_log (
    id            NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    mobile_number VARCHAR2(20),
    template_name VARCHAR2(100),
    request_body  CLOB,
    response_code NUMBER,
    response_body CLOB,
    created_at    TIMESTAMP DEFAULT SYSTIMESTAMP
);

Step 3: Create the WhatsApp Procedure

This procedure builds a JSON payload, sends it to the Zaptilo API, captures the response, and logs everything to message_log.

CREATE OR REPLACE PROCEDURE proc_whatsapp_message (
    p_template_name VARCHAR2,
    p_person_name   VARCHAR2,
    p_mobile_number VARCHAR2,
    p_value         VARCHAR2,
    p_image_url     VARCHAR2
) AS
    l_url           VARCHAR2(4000) := 'https://web.zaptilo.ai/api/send/template';
    l_token         VARCHAR2(200)  := 'YOUR_ZAPTILO_API_TOKEN';
    l_http_request  UTL_HTTP.req;
    l_http_response UTL_HTTP.resp;
    l_response_text VARCHAR2(32767) := '';
    l_request_body  CLOB;
BEGIN
    -- Build JSON payload for the WhatsApp template message
    l_request_body := '{
        "phone": "' || p_mobile_number || '",
        "template": {
            "name": "' || p_template_name || '",
            "language": { "code": "en" },
            "components": [
                {
                    "type": "header",
                    "parameters": [
                        {
                            "type": "image",
                            "image": { "link": "' || p_image_url || '" }
                        }
                    ]
                },
                {
                    "type": "body",
                    "parameters": [
                        { "type": "text", "text": "' || p_person_name || '" },
                        { "type": "text", "text": "' || p_value || '" }
                    ]
                }
            ]
        }
    }';

    -- Initialize HTTP request
    l_http_request := UTL_HTTP.begin_request(l_url, 'POST', 'HTTP/1.1');

    -- Set required headers
    UTL_HTTP.set_header(l_http_request, 'Content-Type', 'application/json');
    UTL_HTTP.set_header(l_http_request, 'Authorization', 'Bearer ' || l_token);
    UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_request_body));

    -- Send JSON payload
    UTL_HTTP.write_text(l_http_request, l_request_body);

    -- Read response
    l_http_response := UTL_HTTP.get_response(l_http_request);
    BEGIN
        LOOP
            UTL_HTTP.read_line(l_http_response, l_response_text, TRUE);
        END LOOP;
    EXCEPTION
        WHEN UTL_HTTP.end_of_body THEN NULL;
    END;

    -- Log success
    INSERT INTO message_log (
        mobile_number, template_name, request_body, response_code, response_body
    ) VALUES (
        p_mobile_number, p_template_name, l_request_body,
        l_http_response.status_code, l_response_text
    );

    UTL_HTTP.end_response(l_http_response);
    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        -- Log failure
        INSERT INTO message_log (
            mobile_number, template_name, request_body, response_code, response_body
        ) VALUES (
            p_mobile_number, p_template_name, l_request_body, NULL, SQLERRM
        );
        COMMIT;
END;
/

Replace YOUR_ZAPTILO_API_TOKEN with your actual token from the Zaptilo dashboard.

Step 4: Call the Procedure

Send a test WhatsApp message using an approved template:

BEGIN
    proc_whatsapp_message(
        p_template_name => 'order_confirmation',
        p_person_name   => 'John Doe',
        p_mobile_number => '919876543210',
        p_value         => 'ORD-12345',
        p_image_url     => 'https://example.com/logo.png'
    );
END;
/

Common Use Cases

Best Practices

• Always store API tokens in a secure configuration table or Oracle Wallet, never hard-code in procedures.
• Use DBMS_SCHEDULER jobs for high-volume sends to avoid blocking transactions.
• Wrap calls in autonomous transactions (PRAGMA AUTONOMOUS_TRANSACTION) so logging works even if the parent transaction rolls back.
• Escape user input properly when building JSON to avoid breaking the payload.
• Monitor the message_log table for non-2xx responses.