Implementing WhatsApp OTP Verification in Oracle APEX Using UltraMsg API

 

🚀 Introduction

In today’s digital applications, user verification plays a crucial role in ensuring security and trust.

Traditionally, OTPs are sent via SMS — but with the widespread use of WhatsApp, sending OTPs through WhatsApp offers:

  • Faster delivery

  • Better user reach

  • Improved engagement

In this blog, we explore how to implement OTP verification in Oracle APEX by integrating WhatsApp messaging using an external API service.

🎯 Objective

The goal of this implementation was to:

✔️ Generate OTP dynamically
✔️ Send OTP via WhatsApp
✔️ Integrate external messaging API
✔️ Enhance login/verification security
✔️ Enable real-time communication

🧩 Solution Overview

This solution integrates:

  • PL/SQL OTP generation

  • REST API call from Oracle APEX

  • WhatsApp messaging service

This allows Oracle APEX applications to send OTP directly to users via WhatsApp instead of SMS.

 🧱 Step-by-Step Approach

 Step 1: Configure WhatsApp API

To send OTP via WhatsApp, a messaging API such as UltraMsg is used. Use below link for create Instance account and generate API token

WhatsApp API gateway for sending messages and chatbot - Ultramsg

Steps:

  1. Create an account

  2. Verify your email and login to the UltraMsg dashboard

  3. Create instance    

  • Click “Create Instance”.

  • Name your instance (e.g., MyAPEXInstance).

  • Save your Instance ID (e.g., instance123456) — you’ll need this for API calls.

      4.Get API token

  • Navigate to “API Token” in the instance settings.

  • Copy the token (e.g., 2jhbj8sf1fygygh).

  • This token will be used in API requests.


Step 2: Prepare Oracle APEX

  1. Create a Page Item for Mobile Number

    • Item Name: P5_MOBILE_NUMBER

    • Type: Text Field

  2. Optional: Create a Page Item for OTP Verification

    • Item Name: P5_ENTERED_OTP

    • Type: Text Field

  3. Create a Button to Send OTP

    • Label: Send OTP

    • Action: Submit Page or Execute PL/SQL Code



Step 3: Create REST Call from APEX

Oracle APEX supports REST integration using:

👉 APEX_WEB_SERVICE.MAKE_REST_REQUEST

This allows sending HTTP requests to external services.

Example flow:

  • Pass mobile number

  • Pass OTP message

  • Trigger API


Step 4: PL/SQL Code to Send OTP via WhatsApp

Paste the following in “Execute PL/SQL Code” of your button or process:

CODE:

DECLARE

    l_otp           VARCHAR2(6);

    l_url           VARCHAR2(4000);

    l_response_b    BLOB;

    l_response_c    VARCHAR2(32767);

    l_mobile_number VARCHAR2(20);

    l_message       VARCHAR2(500);


BEGIN

    -- 1️⃣ Get mobile number from page item

    l_mobile_number := TRIM(:P5_MOBILE_NUMBER);

    IF l_mobile_number IS NULL THEN

        RAISE_APPLICATION_ERROR(-20001, 'Mobile number is required.');

    END IF;

    -- 2️⃣ Add country code if missing

    IF SUBSTR(l_mobile_number,1,3) != '+91' THEN

        l_mobile_number := '+91' || l_mobile_number;

    END IF;

   -- 3️⃣ Generate 6-digit OTP

    l_otp := TO_CHAR(TRUNC(DBMS_RANDOM.VALUE(100000, 999999)));

    l_message  := 'Your OTP is ' || l_otp || ' and is valid for 5 minutes';

    -- 4️⃣ Set UltraMsg API URL with token

     l_url :=  'https://api.ultramsg.com/instance158249/messages/chat?token="YOUR_TOKEN_NUMBER"='||l_mobile_number||'&body='||l_message||'&priority=10';

    -- 6️⃣ Call UltraMsg API

    l_response_b := APEX_WEB_SERVICE.MAKE_REST_REQUEST_B(

                        p_url         => l_url,

                        p_http_method => 'POST'

                    );

    -- 7️⃣ Convert BLOB to text

   l_response_c := UTL_RAW.CAST_TO_VARCHAR2(DBMS_LOB.SUBSTR(l_response_b,32767,1));

    -- 8️⃣ Store OTP

    APEX_UTIL.SET_SESSION_STATE('P5_ENTERED_OTP', l_otp);

    -- 9️⃣ Success message

    APEX_APPLICATION.G_PRINT_SUCCESS_MESSAGE :=

        ' OTP sent successfully '||

        '. WhatsApp response: ' || l_response_c;

EXCEPTION

    WHEN OTHERS THEN

        APEX_APPLICATION.G_PRINT_SUCCESS_MESSAGE :=

            ' Error sending OTP: ' || SQLERRM;

END;



Step 5: Send OTP via WhatsApp

Using the REST API, the OTP is sent instantly to the user's WhatsApp number.

Message format:

“Your OTP is 123456”

This replaces traditional SMS delivery.


Step 6: Verification Flow

Once the user receives OTP:

  • OTP is entered in APEX page

  • Compared with generated OTP

  • Access granted if matched



Tips & Best Practices

  • OTP Expiry: You can store a timestamp in session or table and check expiry.

  • Message Template: Customize your message text for better branding.

  • Multiple Users: If your app has multiple users, store OTP in a table instead of session.

  • Security: Do not log OTPs in plain text in production.


💡 Use Cases

This implementation can be used for:

✔️ Login verification
✔️ Registration validation
✔️ Password reset
✔️ Approval workflows


📈 Business Benefits

This approach enables:

✔️ Faster OTP delivery
✔️ Reduced SMS dependency
✔️ Improved user experience
✔️ Real-time communication


🔮 Future Enhancements

Possible upgrades:

  • Expiry timer
  • Retry limit
  • OTP resend


🏁 Conclusion

Integrating WhatsApp-based OTP verification into Oracle APEX enhances both security and usability.

By leveraging REST APIs and modern messaging platforms, applications can provide seamless authentication experiences beyond traditional SMS methods.


Comments

Post a Comment

Popular posts from this blog

Building Secure RESTful Services in Oracle APEX 24.2 Using ORDS, OAuth2 Client Credentials, and PL/SQL

Image
📘 Introduction Oracle APEX 24.2 provides powerful capabilities to expose database data as secure RESTful APIs using Oracle REST Data Services (ORDS). In modern enterprise architectures, APIs are essential for enabling system-to-system communication. Instead of direct database access, RESTful services allow controlled and secure data sharing. In this blog, we will walk through how to create a REST API in Oracle APEX 24.2 and secure it using privileges and OAuth authentication. 🎯 Objective The goal of this implementation is to: • Expose database data through REST APIs • Create structured API endpoints • Secure APIs using OAuth • Enable controlled external access 🧱 Prerequisites Before starting, ensure: • Oracle APEX 24.2 is installed • ORDS 20.x or later is configured • RESTful Services are enabled • A working table exists (Example: HR.EMPLOYEES) 🌐 REST API URL Structure The generated API follows this format: https://host:port/ords/schema/module/template ⚙️ Implementat...
Read more

Building a Portfolio Generator in Oracle APEX: A Step-by-Step Guide to Generate Downloadable Portfolio Documents

Image
  Introduction This tutorial demonstrates how to build a Portfolio Generator application in Oracle APEX where users can enter resume details and generate a downloadable portfolio HTML/ PDF with one click . The application collects user details, work experience, skills, and education, then dynamically generates a styled HTML portfolio that can be printed as PDF . Step- by- Step  Implementation  Guide Step 1 -  Create Database Tables First create the tables required to store resume information. We will create 5 tables . 1️⃣ Resume Master Table Stores the main profile information.   CREATE TABLE "RESUME_MASTER"     ( "RESUME_ID" NUMBER GENERATED ALWAYS AS IDENTITY, "FULL_NAME" VARCHAR2(200),  "EMAIL" VARCHAR2(200),  "PHONE" VARCHAR2(50),  "ADDRESS" VARCHAR2(500),  "DOB" DATE,  "OBJECTIVE" CLOB,  "CURRENT_COMPANY" VARCHAR2(200),  "CURRENT_ROLE" VARCHAR2(200),  "C...
Read more