I have a requirement to send birthday email to employee and have a procedure that needs to be modified to include templates as detailed below.

Whenever it is employee's birthday, i.e. sysdate=employee_birthday, an email would be triggered to a particular email id on that day.

The format of email is the main challenge as it needs to be as greeting template.

What I have are 3 tables:

1)

EMP_DATA

   CREATE TABLE EMP_DATA(ID NUMBER, EMP_NAME VARCHAR2(100),EMP_BIRTHDAY DATE,EMP_PIC BLOB,EMAIL VARCHAR2(1000),CONSTRAINT (ID_PK) PRIMARY KEY(ID) USING INDEX ENABLE)
        
        INSERT INTO EMP_DATA VALUES(1,'Allan Pedro',to_date('12/12/1990','DD/MM/YYYY'),null,alpha@itorg.com);
        
        INSERT INTO EMP_DATA VALUES(2,'Selena Pedrick',to_date('12/12/1990','DD/MM/YYYY'),null,alpha@itorg.com);

This is dummy data and on any day one or more than one employee may have birthday.

2)`CREATE TABLE BIRTHDAY_GREETING(GREETING_DATE DATE,GREETING BLOB)`

3)`CREATE TABLE WISH(WISH_DATE DATE,WISH VARCHAR2(4000))`

insert into wish(to_date('12/12/2020','DD/MM/YYYY'),'The warrior is the average man with laser like focus. We wish you achieve all that you want in life.

We want you to succeed.

All the best!')

I had no idea on how to save the images so using the method suggest in the thread, created 2 regions for emp_pic and greeting pic and stored them in tables: EMP_BLOB_TBL and GREETING_BLOB_TBL.

However when i upload image from app, there is always an error on page saying error uploading image, (that's a different issue however )

https://community.oracle.com/tech/developers/discussion/4090790/how-to-upload-an-image-file-into-a-table-blob-in-apex-5-for-dummies

    CREATE TABLE  "EMP_BLOB_TBL"    (  "IMAGE_NAME" VARCHAR2(400),      "FILENAME" VARCHAR2(350),      "MIME_TYPE" VARCHAR2(255),      "DOC_SIZE" NUMBER,     "CHARSET" VARCHAR2(128),      "LAST_UPDATE_DATE" DATE,     "CONTENT" BLOB)
    CREATE TABLE  "GREETING_BLOB_TBL"    (  "IMAGE_NAME" VARCHAR2(400),      "FILENAME" VARCHAR2(350),      "MIME_TYPE" VARCHAR2(255),      "DOC_SIZE" NUMBER,     "CHARSET" VARCHAR2(128),      "LAST_UPDATE_DATE" DATE,     "CONTENT" BLOB)

Now i am still able to upload and save the images in these tables, so moving to requirement details:

For employee_pic for email, the image name would match employee name.

So when employee pic is uploaded it would have name of employee. So for Allan Pedro, image_name is also Allan Pedro.

Similary for greeting, the table BIRTHDAY_GREETING has date column.
So greeting image is uploaded with the name as date.

Now, what needs to be done is in the procedure i have written for this.

I have written basic proc for sending email on birthday and in db set a job to run everyday.

Procedure that needs to be modified:

 CREATE OR REPLACE PROCEDURE GREETINGS
    IS
    BEGIN
    
    
      FOR i IN (SELECT emp_name,email,emp_birthday from emp_data where substr(to_date(emp_birthday,'MM/DD/YYYY'),1,6) = substr(to_date(SYSDATE,'DD/MM/YYYY'),1,6))
      LOOP
    
    
      APEX_MAIL.SEND(
      p_to        => i.email,
      p_cc => NULL,
      p_from      => 'no.reply@yourdomain.com',
      p_subj      => 'Birthday Celebrations',
      p_body      => 'Birthday greetings'
      );
      END LOOP;
      APEX_MAIL.PUSH_QUEUE;
    END GREETINGS;

Now, the expected email format (in display order) generated on every day which is a birthday is that should be generated modifying the procedure is:

GREETING IMAGE
Birthday Employee/Employees names and photos in a template
Wish (text)

So, conditions would be:

EMP_DATA.EMP_NAME=EMP_BLOB_TBL.IMAGE_NAME to pic employee's photo.(match on names)

EMP_DATE.EMP_BIRTHDAY=BIRTHDAY_GREETING.GREETING_DATE for the greeting image to display.(match on dates)

Similarly, EMP_DATA.EMP_BIRTHDAY=WISH.WISH_DATE

The dates would be matched on month and day only obviously like the condition i have put in procedure, to convert in DD/MM format and truncate to remove the year part so that they match exactly.

So basically, all the GREETING and WISH DATE Content matching with the employee's birthday date need to be displayed in the email

Apex version is 20.2

declare
   l_image     blob;
   l_name      varchar2(255);
   l_mime_type varchar2(255);
   l_length    number;
begin
   select file_content,
       file_name,
       file_mime_type,
       dbms_lob.getlength(file_content)
   into l_image,
       l_name,
       l_mime_type,
       l_length
   from asktom_images
   where id = :GET_ID;
   
   sys.htp.init();
   sys.owa_util.mime_header(
      nvl(l_mime_type, 'application/octet'),
      false
   );
   sys.htp.p('Cache-Control: max-age=36000');
   sys.htp.p('Content-Length: ' || l_length);
   sys.htp.p('Content-Disposition: inline; filename="' || l_name || '"; filename*=UTF-8''''' || l_name);
   
   sys.owa_util.http_header_close;
   sys.wpg_docload.download_file(l_image);
   apex_application.stop_apex_engine;
end;