Skip to main content

Oracle APEX Email Templates - Displaying Tabular Data

In my previous blog posts, I have explained about Oracle APEX Email Templates and how you can handle conditional display of Email content. In this blog post, I would like show how you can send tabular data while using Email templates. If you have requirement to include tabular data in your emails, then either you need to build your own custom solution on top of Email templates or you have to send emails using old method with out using email templates.

Here I chose former approach and I have developed generic solution which can be reused in any APEX application that uses Oracle APEX email templates. For this, I have created a wrapper function APEX_UTIL_WRAP.SEND_MAIL on APEX_MAIL.SEND function, with all parameters same as APEX_MAIL.SEND and one additional parameter p_table_placeholders.

How it works

Wrapper function has a new parameter p_table_placeholders. Here, you need to define all substitution strings used for tabular data and SQL queries which should be used to fetch data. This wrapper function loops through all the tabular substitution strings, executes the SQL query and then converts the output as HTML table. Finally, it appends tabular substitution strings to p_placeholders parameter and invokes APEX_MAIL.SEND function.

p_table_placeholders is CLOB data type, however you need to pass valid JSON data for this parameter.

e.g. input for p_table_placeholders

{
  'tables': [
    {
      'substitution_string': 'EMP_DATA_TABLE',
      'sql_query': 'select empno "Employee Number", ename "Employee Name", job "Job", hiredate "Hire Date", sal "Salary $" from emp where deptno = :DEPTNO',
      'bind_var_names': 'DEPTNO',
      'bind_var_values': '10',
      'limit_rows': '10',
      'no_data_found' : 'There are no employees for this department.',
      'more_data_found' : 'There are more employees for this department. Please log into the application to see all employees.'
    }
  ]
}

As you can see, it just has one entry with name "tables" and value as an array of JSON objects. For each tabular substitution string, you need to define one JSON object in "tables" array.

substitution_string: Name of the substitution string specified for tabular data in Email templates
sql_query: SQL query which should be used as source for fetching the data. You can also specify filter conditions using bind variable syntax. Specify user friendly and readable column aliases for columns. These will be used as column headers in table.
bind_var_names: If your query contains any bind variables, then specify them here. When there are multiple variables, then use colon as separator.
bind_var_values: Values of bind variables. When there are multiple variables, then use colon as separator. For date values, use same date format as that of APEX application date format.
limit_rows: If you query returns more rows and if you want to limit maximum number of rows included in the email, then you can specify it here. To fetch all the rows, leave it empty.
no_data_found: Message to display when query returns no rows.
more_data_found: Message to display when query returns more rows than specified in limit_rows.

How to use

Email Templates: 

As usual, first you need to create an email template. Where ever you want to display tabular data, just add an additional substitution string and use RAW filter to it. For e.g. if  EMP_DATA_TABLE is substitution string name used for tabular data, then you need to put below text in Email templates
#EMP_DATA_TABLE!RAW#
This will be replaced by tabular data.

PL/SQL Code to send email:

Below code is an example to send email notification with employee details for selected department. You can see demo of this example here.
-- example usage
DECLARE
    l_placeholders         CLOB;
    l_table_placeholders   CLOB;
    l_sql_query            VARCHAR2(4000);
    l_mail_id              NUMBER;
BEGIN
    -- build normal placeholders
    apex_json.initialize_clob_output;
    apex_json.open_object;
    FOR dept IN(
        SELECT
            deptno
            ,dname
        FROM
            dept
        WHERE
            deptno = :p16_deptno
    )LOOP
        apex_json.write(
            'DEPTNO'
            ,dept.deptno
        );
        apex_json.write(
            'DNAME'
            ,dept.dname
        );
    END LOOP;
    apex_json.close_object;
    l_placeholders := apex_json.get_clob_output;
    apex_json.free_output;
    --
    -- build tabular placeholders
    apex_json.initialize_clob_output;
    apex_json.open_object;
    apex_json.open_array('tables');
    -- begin tabular code
    -- repeat below for each tabular substitution_string defined in email template
    --
    -- EMP_DATA_TABLE -- start
    apex_json.open_object;
    l_sql_query := 'select empno "Employee Number", ename "Employee Name", job "Job", hiredate "Hire Date", sal "Salary $" from emp where deptno = :DEPTNO';
    apex_json.write(
        'substitution_string'
        ,'EMP_DATA_TABLE'
    );
    apex_json.write(
        'sql_query'
        ,l_sql_query
    );
    -- colon seperated bind variable names
    -- Optional if your query does not have any bind variables
    apex_json.write(
        'bind_var_names'
        ,'DEPTNO'
    );
    -- colon seperated bind variable values
    -- Optional if your query does not have any bind variables
    apex_json.write(
        'bind_var_values'
        ,:P16_DEPTNO
    );
    -- Specify '' to display all the rows
    -- Optional if you don't want to limit output rows
    apex_json.write(
        'limit_rows'
        ,'5'
    );
    -- Optional if you don't want to use custom message for no_data_found
    apex_json.write(
        'no_data_found'
        ,'No employees found for this department.'
    );
    -- Optional if you have not specified any value for limit_rows
    -- Optional if you don't want to use custom message for more_data_found
    apex_json.write(
        'more_data_found'
        ,'There are more employees for this department. Please log into the application to see all employees.'
    );
    apex_json.close_object;
    -- EMP_DATA_TABLE -- end
    --
    apex_json.close_array;
    apex_json.close_object;
    l_table_placeholders := apex_json.get_clob_output;
    apex_json.free_output;
    -- Send Email
    -- Email template static id DEPT_EMP_LIST
    -- EMail template has 3 substitution strings 
    -- 1) DEPTNO (normal)
    -- 2) DNAME (normal)
    -- 3) EMP_DATA_TABLE (tabular)
    -- l_placeholders will have JOSN for normal substitution strings, it should not have any entry for tabular substitution strings
    -- l_table_placeholders is for tabular substitution strings
    l_mail_id := apex_util_wrap.send_mail(
        p_to                   => 'email_address@domain.com'
        ,p_template_static_id   => 'DEPT_EMP_LIST'        
        ,p_placeholders         => l_placeholders
        ,p_table_placeholders   => l_table_placeholders
    );
END;
That's it. Here is sample email sent using above code.

example email with tabular data

Output Table Structure:

Below is the table structure which will be generated for each tabular substitution string defined. In this example, it has just 5 columns. 

<table class="tab-dynamic-data" id="#substitution_string_name#">
   <tbody>
      <tr class="tr-header">
         <th id="col1">#COLUMN_1_ALIAS#</th>
         <th id="col2">#COLUMN_2_ALIAS#</th>
         <th id="col3">#COLUMN_3_ALIAS#</th>
         <th id="col4">#COLUMN_4_ALIAS#</th>
         <th id="col5">#COLUMN_5_ALIAS#</th>
      </tr>
      <tr class="tr-odd">
         <td>#DATA#</td>
         <td>#DATA#</td>
         <td>#DATA#</td>
         <td>#DATA#</td>
         <td>#DATA#</td>
      </tr>
      <tr class="tr-even">
         <td>#DATA#</td>
         <td>#DATA#</td>
         <td>#DATA#</td>
         <td>#DATA#</td>
         <td>#DATA#</td>
      </tr>
      <tr class="tr-note">
         <td colspan="#NUMBER_OF_COLUMNS#" align="center">#NO_DATA_FOUND# or #MORE_DATA_FOUND#</td>
      </tr>
   </tbody>
</table>

  • All tables will have "class" as "tab-dynamic-data" and "id" same as substitution string name in lower case.
  • Header "tr" will have class "tr-header"
  • Each "th" will have "id" as coln (e.g. col1, col2 etc.), n represents column sequence number in SELECT statement.
  • Each data "tr" will have either "tr-even" or "tr-odd" as class depending on whether its even or odd row.
  • In some cases, additional "tr" will be appended at the end of the table with special class "tr-note". It will be added only when NO_DATA_FOUND (query returns no rows) or MORE_DATA_FOUND (query returns more rows than specified in limit_rows) messages are displayed.
You can use these "classes" and "ids" to define additional CSS styles for your tabular data tables. For e.g. I have used below CSS code for email shown in above pic. I have defined this CSS code in "Advanced > HTML Templates" section of the email template.
    table.tab-dynamic-data {width:100%;}
    table.tab-dynamic-data td, table.tab-dynamic-data th
    {
      border: 1px solid #f1efed;
      padding-left: 4px;
    }

Limitations:

As explained above, p_table_placeholders will be used to generate additional substitution strings and their values dynamically and they will be appended to p_placeholders parameter. However when size of p_placeholders exceeds 32k, then APEX_MAIL.SEND raises VALUE_ERROR exception. That means, you can't use this wrapper function to include large chunks of HTML tables. You can specify limit_rows and more_data_found options as work around to this limitation.

I would like to set up it as new project on github with more the details (other limitations, examples and features) on this wrapper package APEX_UTIL_WRAP. Until then, if you want to have a look or use this package, you can download it from here.

I have added this to GitHub. However, documentation and examples are still pending. 

Let me know what do you think about this solution.

Thank you.

Comments

Girish said…
Thank you so much for sharing. I have found it extremely helpful… Appreciate it.
Simon J said…
Great post. Will certainly give it a go. Thanks
N Patel said…
Thank you for sharing, how can you align department number in right using CSS?
Hari said…
Hi N Patel,

You could try something like below.

- Enclose number data with some div element and give it a class name, e.g. ralign
- Next, edit email template and add CSS to use right side text alignment for the div.
e.g. css
div.ralign {text-align:right;}

This should give right alignment of the data for the columns you need.

Regards,
Hari
Greate work :-), very welcome what you did, thank you for giving help to APEX developers
Pavi said…
Great post.. Does this have any version limitations?
I tried it in version 22.2 and it was working but not in 19.2.

Popular posts from this blog

Interactive Grid - Conditional Enable/Disable

In this blogpost, I am going to discuss few approaches using which we can conditionally enable/disable Interactive Grid (IG) column(s) based on other column(s) values. Note:    There is a bug  30801170  in APEX 19.2/20.1 with respect to "enable/disable" dynamic actions for IG columns. Workaround for this bug is provided at the end of this blogpost . This bug has been fixed in APEX version 20.2. Client Side Only Conditions If conditions to enable/disable are simple, then we can check those conditions easily on the client side. For e.g. let's consider IG on EMP table with following SQL Query. SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM EMP Let's consider the requirement as - Enable "Commission" column only when JOB is equals to 'SALESMAN' and disable "Commission" column in all other cases. This can be done declaratively using dynamic actions (DA) DA "Enable/Disable Commission - 1" Create DA and give it a prope

Interactive Grid - Bulk Operation on Selected Rows

What's the Problem? Let's say you have an IG on employee table and you want to update selected employees commission based on user's input. Ideally, it should be very simple, where you can write UPDATE statement in page process and select IG region as "Editable Region" under "Execution Options" of the page process. But, when you select rows and submit page, you can see that, this process won't get executed! The reason is  Selection of 'Row Selector' check-boxes is not considered as row-change. Thus selected rows are not submitted to server. So, is there any work around?  Yes! Luckily there are lot of JavaScript (JS) APIs available to work with IG. If you are not already aware, you can refer "APEX IG Cookbook"  or  JavaScript API Reference documentation. If we continue with above Employee IG example, when user selects IG rows, enters "Commission %" and clicks on "Update Commission" button, then we can writ

Interactive Grid - Process Filtered Data on Server Side

Recently one of the APEX developers has reached out to me and asked if it's possible to capture filtered rows data of the Interactive Grid on the server-side and do some processing. In APEX 20.1, there is a new API APEX_IG , using which we can achieve this. Photo by Jakub Kapusnak on Unsplash The approach is very simple and straightforward. Get the internal region id based on the Static ID given for the IG region Get the last viewed report id based on region id Open query context for the region and report using region id and report id Fetch and loop through the rows using the query context Do something with fetched rows And finally, close the query context If you have already done this for interactive reports, then you should be already aware of these steps. The only difference here is, we use APEX_IG APIs instead of APEX_IR APIs. For the demo purpose, let's Build an Interactive Grid on EMP table and let's give it a Static ID as emp Create a Textarea page item with