Create a job to send out emails from APEX

 You can automate stock price update emails in Oracle APEX using Oracle Scheduler and APEX_MAIL. Here's how:

---

1. Create a Table to Store User Emails

If you don't already have a table for user emails, create one:

CREATE TABLE STOCK_SUBSCRIBERS (
    user_id   NUMBER PRIMARY KEY,
    email     VARCHAR2(255) UNIQUE NOT NULL
);

Populate this table with the 200 users who should receive emails.

---

2. Create a PL/SQL Procedure to Send Emails

CREATE OR REPLACE PROCEDURE SEND_STOCK_EMAILS AS
    v_subject    VARCHAR2(100);
    v_message    CLOB;
    v_email      VARCHAR2(255);
    CURSOR c_users IS SELECT email FROM STOCK_SUBSCRIBERS;
BEGIN
    -- Set email subject
    v_subject := 'Stock Price Update';

    -- Construct email body
    v_message := '<h2>Latest Stock Prices</h2><table border="1"><tr><th>Stock Code</th><th>Price</th></tr>';

    FOR rec IN (SELECT stock_code, current_price FROM STOCK_PRICES) LOOP
        v_message := v_message || '<tr><td>' || rec.stock_code || '</td><td>' || rec.current_price || '</td></tr>';
    END LOOP;

    v_message := v_message || '</table>';

    -- Send emails to all users
    FOR user_rec IN c_users LOOP
        v_email := user_rec.email;
        APEX_MAIL.SEND(
            p_to        => v_email,
            p_from      => 'noreply@yourdomain.com',
            p_subj      => v_subject,
            p_body      => v_message,
            p_body_html => v_message
        );
    END LOOP;

    -- Commit email queue
    APEX_MAIL.PUSH_QUEUE;
END SEND_STOCK_EMAILS;

• This procedure fetches stock prices from STOCK_PRICES and sends an HTML-formatted email.
• APEX_MAIL.PUSH_QUEUE ensures emails are processed.

---

3. Schedule the Job to Run at 9:30 AM & 3:30 PM on Weekdays

Use Oracle DBMS Scheduler to trigger the email procedure.

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'STOCK_EMAIL_JOB',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN SEND_STOCK_EMAILS; END;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=9,15; BYMINUTE=30',
        enabled         => TRUE
    );
END;

• Runs only on weekdays (BYDAY=MON,TUE,WED,THU,FRI).
• Executes at 9:30 AM and 3:30 PM.

---

4. Verify APEX Email Configuration

Ensure that APEX_INSTANCE_ADMIN has email configured:

BEGIN
    APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_HOST_ADDRESS', 'your.smtp.server');
    APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_USERNAME', 'your_email_user');
    APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_PASSWORD', 'your_email_password');
END;

---

5. Monitor Scheduled Job

Check if the job runs correctly:

SELECT job_name, state, last_start_date, next_run_date 
FROM USER_SCHEDULER_JOBS 
WHERE job_name = 'STOCK_EMAIL_JOB';