Automating Economic Insight Emails using Oracle APEX AI Services

As finance and tech converge, the ability to generate and send timely, data-driven insights can transform decision-making processes. In this post, we explore how to harness Oracle APEX AI Services and APEX_MAIL to automatically generate and email a financial summary of Ghana's economic indicators — daily, weekly, or monthly to investors.

🔧 Prerequisites

Before diving into code, make sure the following are set up:

1. Oracle APEX Environment (Cloud or On-Premise)

2. AI Services Plugin enabled and configured (apex_ai package)

3. APEX Mail Setup:

   • SMTP settings correctly configured under Manage Instance > Mail Server

   • Valid sender address (p_from)

4. Workspace Name (You’ll use this to get the security_group_id)

🧠 Use Case

We want to automate the generation of monthly economic insight reports (e.g., for May 2025) based on Bank of Ghana's data using APEX AI Chat and send it to our stakeholders via email.

Setting Up APEX AI Services Plugin (apex_ai)

Before using the apex_ai.chat function, you need to ensure that Oracle APEX AI Services is correctly configured in your environment. Follow these steps:

1. Enable AI Services in Your APEX Instance

• Navigate to App Builder > Workspace Utilities > Generative AI.

• Click "Create" to register a new service.

• Choose the appropriate AI Provider (Oracle Cloud or OpenAI) based on your setup.

• Provide your API Key or Oracle AI settings (endpoint, credentials, etc.).

• Assign a Static ID (e.g., financial_assistant) — this ID will be used in your PL/SQL.

• 

Configure SMTP Settings (Admin Task) . Click the link to read about how to set up email delivery in OCI

🪄 The Full PL/SQL Block

create or replace procedure send_financial_summary_email
as
    l_security_group_id number;
    l_messages          apex_ai.t_chat_messages;
    l_response1         clob;
    l_body              clob;
    l_id                number;

    r_month varchar2(20) := to_char(add_months(sysdate, -1), 'MON');
    r_year  varchar2(4)  := to_char(sysdate, 'YYYY');
begin
    -- Set the security group context for APEX workspace
    apex_util.set_security_group_id(apex_util.find_security_group_id(p_workspace => 'WKSP_TESTING'));

    -- AI Chat request to generate financial summary
    l_response1 := apex_ai.chat(
        p_prompt            => 'As a financial and investment analyst, analyze Bank of Ghana economic data and summarize the latest key indicators for Ghana for ' || r_month || ', ' || r_year || '.',
        p_system_prompt     => 'You are a financial analyst reporting economic insights from the Bank of Ghana for publication.',
        p_service_static_id => 'error_assistant',
        p_messages          => l_messages
    );

    -- Compose email body
    l_body := 'Dear User,' || utl_tcp.crlf || utl_tcp.crlf;
    l_body := l_body || 'Here is the analysis of the Bank of Ghana economic data for ' || r_month || ', ' || r_year || ':' || utl_tcp.crlf || utl_tcp.crlf;
    l_body := l_body || l_response1 || utl_tcp.crlf || utl_tcp.crlf;
    l_body := l_body || 'Sincerely,' || utl_tcp.crlf;
    l_body := l_body || 'The Finance Insight Dev Team' || utl_tcp.crlf;

    -- Send the email
    l_id := apex_mail.send(
        p_to   => 'investoremail@outlook.com',
        p_from => 'test@cloudifyhub.com',
        p_body => l_body,
        p_subj => 'Economic Insight: Ghana | ' || r_month || ' ' || r_year || ''
    );

    apex_mail.push_queue;
    commit;

    -- Show in DBMS output (for debug/log)
    dbms_output.put_line('Email Sent. AI Response:');
    dbms_output.put_line(l_response1);
end;
/

🗓️ Automate It with a Scheduler

To make this a daily/monthly automation, use DBMS_SCHEDULER OR APEX AUTOMATIONS to run the PL/SQL block at intervals.

BEGIN
  -- Create the job
  DBMS_SCHEDULER.create_job (
    job_name        => 'send_economic_insight_email',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN send_financial_summary_email(); END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MONTHLY;BYMONTHDAY=1;BYHOUR=8',
    enabled         => TRUE,
    comments        => 'Monthly Economic Insight Email Job'
  );

  -- Run the job immediately once
  DBMS_SCHEDULER.run_job (
    job_name => 'send_economic_insight_email',
    use_current_session => FALSE -- Set to TRUE if you want it to run in the current session
  );

  DBMS_OUTPUT.PUT_LINE('Job "send_economic_insight_email" created and submitted for immediate execution.');
END;
/

or you can create your customize page and pass some parameters.(Depends on you my Oracle APEX Buddy)

Output

✅ Benefits

• 🔁 Hands-Free Reporting — Once set, it runs on autopilot. Agentic report

• 🤖 AI-Powered Insight — Leverages LLM capabilities.

• 📈 Professional Presentation — Clean email format ready to share.

• 📅 Customizable Schedule — Daily, weekly, or monthly.

Caution

AI can hallucinate

💬 Final Thoughts

This approach showcases the powerful synergy between Oracle APEX, AI Services, and automation tools to deliver high-value insights in real-time. Whether for finance, healthcare, or logistics, this pattern can be adapted to fit any domain where timely, intelligent communication matters.