Oracle APEX and Microsoft Teams Integration

Have you ever missed celebrating a colleague's birthday? In a busy work environment, it can happen to the best of us. Recently, we had a similar situation, and it got me thinking about how we could leverage the power of Oracle APEX to automate these important reminders.

That's how this new integration idea was born: automatically posting birthday announcements to a Microsoft Teams channel directly from our Oracle APEX application.

In this post, I'll walk you through how we built an automation that runs daily and pushes a message to a Microsoft Teams chat whenever an employee has a birthday that day. We'll be using Microsoft Teams Workflows and Oracle APEX web services.

Let's dive in!

The Goal: Automated Birthday Announcements in Microsoft Teams

Our objective is simple:

Every morning at 8:30 AM IST, an Oracle APEX process will run.
This process will query our employee database for anyone whose birthday falls on the current date.
For each employee celebrating their birthday, a message will be automatically posted to a designated Microsoft Teams channel/chat.

Prerequisites

Before we begin, ensure you have the following:

An Oracle APEX application.
A table in your database containing employee information, including their name and date of birth.
Access to a Microsoft Teams workspace and the ability to create or configure webhooks (which the Microsoft Teams Workflows component will utilize behind the scenes).

Step-by-Step Implementation

Let's break down the implementation into manageable steps:

Microsoft Teams Configuration: Setting up an Incoming Webhook

Select More options next to the chat you want to add to the workflow. Then, select Workflows.
In the Workflows dialog box, search and select “Post to a chat when a webhook request is received”
Enter a descriptive name for the workflow.
Select the Group Chat where you want to post the wishes and click Add Workflow.
Copy the POST URL from next step.

Creating the APEX Automation

Here's how to set up the Oracle APEX automation:

Navigate to Oracle APEX Automation:
- Go to App Builder.
- Select your Application.
- Go to Shared Components.
Create Automation:
- Click "Create".
Fill in Automation Details:
- In the pop-up wizard, enter the following:
  - Name: A descriptive name (e.g., "Daily Birthday Wishes to Teams Chat").
  - Type: Scheduled.
  - Action initiated: Always.
  - Execution Schedule: Select Custom.
  - Frequency: Daily.
  - Interval: 1.
  - Execution Time: 3 AM UTC (8:30 AM IST).
- Click "Create".
Add Action and Enable Automation:
- The automation is added in "Disabled" status. You need to add an action and then enable the automation.
- Edit the default action that was added.
  - Name: Send Birthday Wishes.
  - Type: Execute Code.
  - Code: Paste the PL/SQL code (provided below) into the "Code" section.
  - Click "Apply Changes".
- Change the automation schedule status to "Active".
- Save Changes.
Run Automation (Optional):
- You can now see that the automation is scheduled to run.
- If you want to run the automation immediately, click "Run".

PL/SQL Code for APEX Automation

The PL/SQL code provided is for reference only. Readers should refine it with appropriate error logging and use the correct table and column names from their specific database schema.

DECLARE
   l_employee_name VARCHAR2(100); 
   l_webhook_url   VARCHAR2(1000) := 'https://prod-xx.centralindia.logic.azure.com:443/workflows/26cba624c75xxxxxxxxxxxxxxx/triggers/manual/paths/invoke?api-version=2016-06-01&sp=%2Ftriggers%2Fmanual%2Frun&sv=1.0&sig=DJXu9tU6yiwU8qCwxxxxTdx0yAko_RDAsDf-Og';
   l_json_template CLOB;
   l_json_payload  CLOB;
   l_clob_response CLOB;
BEGIN
   FOR i IN (
      SELECT
         emp.first_name
         || ' '
         || emp.last_name full_name
      FROM
         employees emp
      WHERE
            emp.employee_status = 'ACTIVE'
         AND to_char(
            emp.date_of_birth
          , 'MM-DD'
         ) = to_char(
            sysdate
          , 'MM-DD'
         )
   ) LOOP
      l_employee_name := i.full_name;
    -- Define the JSON template using Q-quoting
      l_json_template := q'[{
      "type": "message",
      "attachments": [
        {
          "contentType": "application/vnd.microsoft.card.adaptive",
          "contentUrl": null,
          "content": {
            "$schema": "http://adaptivecards.io/schemas/adaptive-card.json",
            "type": "AdaptiveCard",
            "version": "1.5",
            "msteams": {
                "width": "Full"
            },
            "body": [
              {
                "type": "TextBlock",
                "text": "🎉 Happy Birthday! 🎂",
                "weight": "Bolder",
                "size": "Large",
                "wrap": true,
                "horizontalAlignment": "Center"
              },
              {
                "type": "TextBlock",
                "text": "Please join us in wishing **##EMPLOYEE_NAME##** a very happy birthday today!",
                "wrap": true,
                "spacing": "Medium"
              },
              {
                "type": "TextBlock",
                "text": "May your day be filled with joy, laughter, and all the things that make you smile. 🥳",
                "wrap": true,
                "spacing": "Small"
              },
              {
                "type": "TextBlock",
                "text": "From all of us at **Maathra**, have a fantastic year ahead! 🌟",
                "wrap": true,
                "spacing": "Medium"
              }
            ]
          }
        }
      ]
    }]';

    -- Replace the placeholder with the actual employee name defined above
      l_json_payload  := replace(
         l_json_template
       , '##EMPLOYEE_NAME##'
       , l_employee_name
      );

    -- Set the necessary HTTP header using SET_REQUEST_HEADERS (plural)
    -- Since p_reset defaults to TRUE, this call clears any previous headers
    -- and sets only the specified ones.
      apex_web_service.set_request_headers(
         p_name_01  => 'Content-Type'
       , p_value_01 => 'application/json'
        -- No need to specify p_reset => TRUE, as it's the default behavior
      );

    -- Make the RESTful web service call (POST request)
      l_clob_response := apex_web_service.make_rest_request(
         p_url         => l_webhook_url
       , p_http_method => 'POST'
       , p_body        => l_json_payload
        -- p_wallet_path => 'file:/path/to/your/wallet', -- Specify if needed
        -- p_wallet_pwd  => 'wallet_password'          -- Specify if needed
      );

    -- Check the response status code
      IF apex_web_service.g_status_code = 200
      OR apex_web_service.g_status_code = 202 THEN
         dbms_output.put_line('Successfully sent notification for ' || l_employee_name);
        -- Optional: DBMS_OUTPUT.PUT_LINE('Response: ' || l_clob_response);
      ELSE
         dbms_output.put_line('Error sending notification for ' || l_employee_name);
         dbms_output.put_line('Status Code: ' || apex_web_service.g_status_code);
        -- Optional: Log the response body which might contain error details
        -- DBMS_OUTPUT.PUT_LINE('Error Response: ' || l_clob_response);
      END IF;

   END LOOP;
EXCEPTION
   WHEN OTHERS THEN
        -- Catch any other unexpected errors
      dbms_output.put_line('Unexpected error sending notification for '
                           || l_employee_name
                           || ': '
                           || sqlerrm);
        -- Re-raise the exception if necessary, or log to a table
      RAISE;
END;

Outcome: Automated Birthday Wishes in Microsoft Teams

Here's an example of how the automated birthday wishes appear in a Microsoft Teams chat:

Alternative: Maathra CloudApps Automation

In our specific application, we used the Automation feature available as part of our CloudApps application, instead of the APEX automation. Both approaches function similarly, as the CloudApps automation is an abstraction layer over dbms_scheduler. Here are the configuration steps within our CloudApps system.

References

Microsoft Teams Workflow: https://support.microsoft.com/en-us/office/creating-a-workflow-from-a-chat-in-teams-e3b51c4f-49de-40aa-a6e7-bcff96b99edc
Oracle APEX Automations: https://docs.oracle.com/en/database/oracle/apex/24.2/htmdb/about-automations.html
Oracle dbms_scheduler: https://docs.oracle.com/en/database/oracle/oracle-database/21/arpls/DBMS_SCHEDULER.html
Adaptive Cards: https://adaptivecards.io/

Integrating Oracle APEX with Microsoft Teams for Announcements

Table of contents