Oracle Apex - Automatic Weekly Email-CodePudding

I have an updatable (and constantly changing) table that looks something like this (simplified):

I'm trying to create an automated process to send over the info to the users on the table. I know how to send out emails but not sure how to create a process since Apex Automations only use SQL or PL/SQL Functions. I also know that we could use 'subscriptions' using an Interactive Report but that sends out the complete report to all the users. Any ideas on how to achieve this?

Thanks!

CodePudding user response：

I'd create a stored procedure that reads that table and sends an e-mail.

Then schedule the procedure using DBMS_SCHEDULER built-in package.

In other words, avoid Apex for that matter; let the database itself handle it.

CodePudding user response：

Use a pl/sql block to loop through the table and send and email for each entry (or for each distinct email adress). This would look something like this:

DECLARE
BEGIN
  FOR r IN (SELECT * FROM <your_table) LOOP
    apex_mail.send(p_to => r.email
                   ...--other parameters for apex_mail.send
  END LOOP;
END;

This block can be used as the source of an Apex Automation - in that case you can schedule the automation to run weekly. Alternatively you could put the code in a procedure (optionally within a package) and schedule it with dbms_scheduler

Note that it is possible to define an email template in your apex application, that is an easier way to create and maintain the text of the automated emails. apex_mail.send takes a template id as parameter (docs)

There are several blogs that show how to do this. Here is a blog showing how to use automations to send a reminder email based on the emp/dept sample dataset.