Tuesday, January 11, 2022

Display tabular data in an email (APEX Mail #6)

 



As promised, here's most of what you need to know about sending email with Oracle APEX. Let's move on to topic number six.


#6 Display tabular data in an email

In this blog post we would like to show you how to display tabular data in an email template. Surely there are several approaches, but with this one described here we have already achieved good goals. Our approach here is that we generate HTML code for a table using PL/SQL code. And now we’ll show you how we solved it ;-)


Let´s start by creating (or modifying) a demo app

To avoid having to create a new application again, let's take the demo app from the previous blog post

First, we add a new email template to this application. The easiest way is to copy the existing "Hello World" template (btw. another great feature of APEX 21.2). To do this, go to "Other Components" in the "Shared Components" section, select "Email Templates" and click "Copy". Then select "Hello World" under "Copy Email Template" and name it for example "Hello Report" ("New Static Identifier" will be generated automatically).

When this is done, the template must be edited. For that, go to the template details and replace the body html code with the following HTML code.


<p>Hello #CONTACT#,</p>
<p>this is an email sent from <strong>Oracle APEX</strong>.</p>
<br>
#REPORT!RAW#
<br>
<p>Best Regards</p>


Line number four is new with the placeholder #REPORT# and the appended "!RAW" for HTML escape. In this placeholder we will insert the HTML code for the tabular data.

For this we need a new "hidden" page item, which we will add on page 1 in the "Send email" region. For example, name it "P1_TABULAR_DATA".

Then we need a new dynamic action that will be executed when the page loads. Just name it e.g. "Create Tabular Data". Create a true Action that execute Server-Side-Code and enter the following PL/SQL Code. 


declare
   l_tabular_data VARCHAR2(32767 CHAR);
begin
   -- add table header
   l_tabular_data := l_tabular_data || '<table>'
                  || '<tr>'
                  || '<th>EMPNO</th>'
                  || '<th>ENAME</th>'
                  || '<th>DEPTNO</th>'
                  || '<th>DNAME</th>'
                  || '</tr>';

   -- add table data    
   for rec in (select empno, ename, deptno, dname from emp_dept_v)
   loop
      l_tabular_data := l_tabular_data || '<tr>'
                     || '<td>' || rec.empno || '</td>'
                     || '<td>' || rec.ename || '</td>'
                     || '<td>' || rec.deptno || '</td>'
                     || '<td>' || rec.dname|| '</td>'
                     || '</tr>';
   end loop;
   
   -- close table
   l_tabular_data := l_tabular_data || '</table>';

   :P1_TABULAR_DATA := l_tabular_data;
end;  


Note: For the tabular data, the tables EMP and DEPT and the corresponding view EMP_DEPT_V are required. You can install them under the sample datasets.


As you can read in the PL/SQL code, the HTML code for a table is generated. In the first step, the headers are written and then the data is written using a for loop. Finally, the HTML code is set into the page item.

The last step required here is to select "P1_TABULAR_DATA" as item to return.


So, all preparations are done!

In order to use the new template, we need another process on page 1. So copy the process "Send Email"  and name it, for example, "Send Email with tabular data". Then change the email template to "Hello Report" and add another placeholder -> REPORT / &P1_TABULAR_DATA.


To be consistent with the other processes, we will set the "Server Side Condition" so that this process is only executed when the "Send" button is clicked and the "Hello Report" email template is selected. Therefore, go to "Server-Side-Condition" and select "Send" as "When Button Pressed". Then set "Item = Value" as the type, "P1_EMAIL_TEMPLATE" as the item, and "HELLO_REPORT" as the Value.


So, that's it... Now let's start the application and try to send an email with a report. 
The email should look like this:




As you can see, we were able to add tabular data to the email template. That's good, but it doesn't look pretty at the moment. To beautify the report a bit and get it in shape, we can add some CSS rules.


The CSS rules are added when generating the HTML code for the report. To do this, we go into the PL/SQL code from the dynamic action on page load and replace the PL/SQL code to be executed with the following:


declare
   l_tabular_data VARCHAR2(32767 CHAR);
begin
   --add css styles
   l_tabular_data := '<head>'
                  || '<style>'
                  -- for table
                  || '.my_table {'
                  || 'font-family: arial, sans-serif;'
                  || 'border-collapse: collapse;'
                  || 'width: 100%;'
                  || '}'
                  -- for table-header  
                  || '.my_table th {'
                  || 'background-color: #eeeeff;'
                  || 'border: 1px solid #dddddd;'
                  || 'text-align: left;'
                  || 'padding: 8px;'
                  || '}'
                  -- for table-data
                  || '.my_table td {'
                  || 'border: 1px solid #dddddd;'
                  || 'text-align: left;'
                  || 'padding-left: 8px;'
                  || '}'                  
                  || '</style>'
                  || '</head>';

   -- add table header
   l_tabular_data := l_tabular_data || '<table class="my_table">'
                  || '<tr>'
                  || '<th>EMPNO</th>'
                  || '<th>ENAME</th>'
                  || '<th>DEPTNO</th>'
                  || '<th>DNAME</th>'
                  || '</tr>';

   -- add table data    
   for rec in (select empno, ename, deptno, dname from emp_dept_v)
   loop
      l_tabular_data := l_tabular_data || '<tr>'
                     || '<td>' || rec.empno || '</td>'
                     || '<td>' || rec.ename || '</td>'
                     || '<td>' || rec.deptno || '</td>'
                     || '<td>' || rec.dname|| '</td>'
                     || '</tr>';
   end loop;
   
   -- close table
   l_tabular_data := l_tabular_data || '</table>';

   :P1_TABULAR_DATA := l_tabular_data;
end;


Feel free here and create your own style according to your wishes.


If we run the application again and send us an email with tabular data, it should look like this:




Note:
  • provide a plain text alternative in case the recipient cannot receive HTML emails
  • a reference to a CSS file is not possible in this case


Here is the demo app for reference.





APEX_MAIL API:

You can also use this solution to send email using the APEX_MAIL API.
In this case, add the placeholder "REPORT" to your PL / SQL code.

For example:


begin
    apex_mail.send(
        p_to                 => :P1_TO,
        p_from               => :APP_EMAIL,
        p_template_static_id => :P1_EMAIL_TEMPLATE,
        p_placeholders       => '{' ||
        '    "CONTACT":'     || apex_json.stringify( :P1_CONTACT ) ||
        '   ,"REPORT":'      || apex_json.stringify( :P1_TABULAR_DATA ) ||
        '}'
    );    

    apex_mail.push_queue;
end;  


Labels: , ,

7 Comments:

At January 12, 2022 at 8:47 PM , Blogger Bansi said...

Can you please share how to send email with attachment from generated report?

 
At January 14, 2022 at 1:01 PM , Blogger Timo said...

You can use the "APEX_DATA_EXPORT" API for example:

-->
DECLARE
l_context apex_exec.t_context;
l_export apex_data_export.t_export;
l_id NUMBER;
BEGIN
l_context := apex_exec.open_query_context(
p_location => apex_exec.c_location_local_db,
p_sql_query => 'select * from emp' );

l_export := apex_data_export.export (
p_context => l_context,
p_format => apex_data_export.c_format_xlsx,
p_file_name => 'employees' );

apex_exec.close( l_context );

l_id := APEX_MAIL.SEND(
p_to => XXX,
p_from => XXX,
p_subj => 'APEX_MAIL with attachment',
p_body => 'Please review the attachment.',
p_body_html => 'Please review the attachment');

APEX_MAIL.ADD_ATTACHMENT(
p_mail_id => l_id,
p_attachment => l_export.content_blob,
p_filename => l_export.file_name,
p_mime_type => l_export.mime_type);

APEX_MAIL.PUSH_QUEUE;

EXCEPTION
when others THEN
apex_exec.close( l_context );
raise;
END;

 
At January 27, 2022 at 7:38 AM , Blogger Alone Angel said...

Good Work .. Thanks

 
At July 20, 2022 at 4:56 PM , Blogger Apex_newbie said...

I would like to send Tabular data in an email using automation (scheduled to run several times daily). Any suggestions?

 
At September 14, 2022 at 4:27 PM , Blogger Holger Lehmann said...

cool, just needing this example for my current project
where I use email_templates and tabular data in the email. Thanks

 
At October 13, 2022 at 11:06 PM , Anonymous Timo Herwix said...

The workaround should be the same. First, create an email template with your tabular report. then create an automation that sends this email template daily :-)

 
At October 13, 2022 at 11:08 PM , Anonymous Timo Herwix said...

Glad to hear Holger :-)

 

Post a Comment

Note: Only a member of this blog may post a comment.

Subscribe to Post Comments [Atom]

<< Home