Advanced email techniques (APEX Mail #9)

   

We have described the most important things about "Emails with Oracle APEX" in the previous blogs. To wrap up this series now, we would like to list a few more techniques, tips, and tricks that we have come across over time. So let's move on to the last topic number nine.

#9 Advanced email techniques

First of all, it should be mentioned that the following points are only based on experience and do not necessarily have to be implemented. But maybe it will help you to create your own email templates. The biggest problem with HTML emails is that the rules for HTML have not yet been standardized for all email clients. This sets limits and calls for creativity during development and should be tested as best as possible. Ideally with as many clients as possible.

Use HTML Tables

It is better to use a <BR> or <Table> tag instead of <P> or <SPAN>. This avoids inconsistent spacing since spacing has different margins in each email client. The safest way to have consistent spacing is to use tables. If you still need to use a <SPAN> tag, end it with a <BR> tag.

Use Inline CSS

If you're wondering why your CSS isn't working, it could be because most email clients remove the <header> of an HTML page so that external style sheets aren't rendered properly. As a solution, you can put a CSS inline with each element instead. Manage links by using an inline style for each link color. Add the colors in hexadecimal, using the longhand version #000000 instead of #000. Keep in mind that not every CSS element is supported in every email client.

In the following HTML code example, you can see how tables and inline CSS can be used:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

<table border="0" width="100%" cellspacing="0" cellpadding="0" style="padding: 20px 0 30px 0;"> <tbody> <tr> <td style="color: #153643; font-family: Arial, sans-serif; font-size: 24px;"><strong>Lorem ipsum dolor sit amet!</strong></td> </tr> <tr> <td style="color: #153643; font-family: Arial, sans-serif; font-size: 16px; line-height: 20px; padding: 20px 0 30px 0;">Lorem ipsum dolor sit amet, consectetur adipiscing elit. In tempus adipiscing felis, sit amet blandit ipsum volutpat sed. Morbi porttitor, eget accumsan dictum, nisi libero ultricies ipsum, in posuere mauris neque at erat.</td> </tr> <tr> <td> <table border="0" width="100%" cellspacing="0" cellpadding="0"> <tbody> <tr> <td valign="top" width="260" style="padding: 20px 20px 20px 20px;"> <table border="0" width="100%" cellspacing="0" cellpadding="0"> <tbody> <tr> <td><img style="display: block;" src="https://bit.ly/3rmktSc" alt="" width="100%" height="auto" /></td> </tr> <tr> <td style="color: #153643; font-family: Arial, sans-serif; font-size: 16px; line-height: 20px; padding: 25px 0 0 0;">Lorem ipsum dolor sit amet, consectetur adipiscing elit. In tempus adipiscing felis, sit amet blandit ipsum volutpat sed. Morbi porttitor, eget accumsan dictum, nisi libero ultricies ipsum, in posuere mauris neque at erat.</td> </tr> </tbody> </table> </td> <td style="font-size: 0; line-height: 0;" width="20">&nbsp;</td> <td valign="top" width="260" style="padding: 20px 20px 20px 20px;"> <table border="0" width="100%" cellspacing="0" cellpadding="0"> <tbody> <tr> <td><img style="display: block;" src="https://bit.ly/3IdtjsA" alt="" width="100%" height="auto" /></td> </tr> <tr> <td style="color: #153643; font-family: Arial, sans-serif; font-size: 16px; line-height: 20px; padding: 25px 0 0 0;">Lorem ipsum dolor sit amet, consectetur adipiscing elit. In tempus adipiscing felis, sit amet blandit ipsum volutpat sed. Morbi porttitor, eget accumsan dictum, nisi libero ultricies ipsum, in posuere mauris neque at erat.</td> </tr> </tbody> </table> </td> </tr> </tbody> </table> </td> </tr> </tbody> </table>

The HTML body should look like this:

Use LOOPS for mass distribution

If you need to send many emails at once, you can solve this with a loop. So write a SQL query that selects all recipients and execute the procedure or function "apex_mail.send" in the loop. You can find a very good description here (Creating Email Campaign App with Oracle APEX).

A tiny example could be the following code snippet:

1 2 3 4 5 6 7 8 9 10 11 12 13

begin for rec in (select contact_name, contact_email from contacts) loop apex_mail.send ( p_to => rec.contact_email, p_template_static_id => 'HELLO_WORLD', p_placeholders => '{' || ' "CONTACT":' || apex_json.stringify( rec.contact_name ) || '}' ); end loop; apex_mail.push_queue; end;

Use different SQL techniques for dynamic content

As we explained in a previous blog, dynamic HTML content can be created by using PL/SQL. This can be very handy in many ways to make the content of your email dynamic, personalized, or even random.

For example, the following SQL snippet can be used to display a personalized order.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56

declare l_order_table VARCHAR2(32767 CHAR); begin l_order_table := l_order_table || '<table style="font-family: arial, sans-serif; border-collapse: collapse; width: 100%;">' || '<tr>' || '<th style="background-color: #eeeeff; border: 1px solid #dddddd; text-align: left; padding: 8px" >Position</th>' || '<th style="background-color: #eeeeff; border: 1px solid #dddddd; text-align: left; padding: 8px" >Article</th>' || '<th style="background-color: #eeeeff; border: 1px solid #dddddd; text-align: left; padding: 8px" >Quantity</th>' || '<th style="background-color: #eeeeff; border: 1px solid #dddddd; text-align: left; padding: 8px" >Unit price</th>' || '<th style="background-color: #eeeeff; border: 1px solid #dddddd; text-align: left; padding: 8px" >Total price</th>' || '</tr>'; for rec in ( select ROW_NUMBER() OVER (ORDER BY article) AS position, article, sum(quantity) as quantity, sum(price) as price, sum(quantity * price) as total from ( select 'Article 1' as article, round(dbms_random.value(1, 10),0) as quantity, round(dbms_random.value(1, 100),2) as price from dual union select 'Article 2' as article, round(dbms_random.value(1, 10),0) as quantity, round(dbms_random.value(1, 100),2) as price from dual union select 'Article 3' as article, round(dbms_random.value(1, 10),0) as quantity, round(dbms_random.value(1, 100),2) as price from dual union select 'Article 4' as article, round(dbms_random.value(1, 10),0) as quantity, round(dbms_random.value(1, 100),2) as price from dual union select 'Article 5' as article, round(dbms_random.value(1, 10),0) as quantity, round(dbms_random.value(1, 100),2) as price from dual ) group by rollup(article) ) loop if rec.article is not null then l_order_table := l_order_table || '<tr>' || '<td style="border: 1px solid #dddddd; text-align: left; padding-left: 8px;">' || rec.position || '</td>' || '<td style="border: 1px solid #dddddd; text-align: left; padding-left: 8px;">' || rec.article || '</td>' || '<td style="border: 1px solid #dddddd; text-align: right; padding-left: 8px;">' || rec.quantity || '</td>' || '<td style="border: 1px solid #dddddd; text-align: right; padding-left: 8px;">' || rec.price || '</td>' || '<td style="border: 1px solid #dddddd; text-align: right; padding-left: 8px;">' || rec.total|| '</td>' || '</tr>'; else l_order_table := l_order_table || '<tr>' || '<td style="background-color: #eeeeff; border: 1px solid #dddddd; text-align: left; padding-left: 8px; font-weight: bold;">Sum</td>' || '<td style="background-color: #eeeeff; border: 1px solid #dddddd; text-align: left; padding-left: 8px; font-weight: bold;"></td>' || '<td style="background-color: #eeeeff; border: 1px solid #dddddd; text-align: right; padding-left: 8px; font-weight: bold;"></td>' || '<td style="background-color: #eeeeff; border: 1px solid #dddddd; text-align: right; padding-left: 8px; font-weight: bold;">' || rec.price || '</td>' || '<td style="background-color: #eeeeff; border: 1px solid #dddddd; text-align: right; padding-left: 8px; font-weight: bold;">' || rec.total || '</td>' || '</tr>'; end if; end loop; l_order_table := l_order_table || '</table>'; :P1_ORDER_TABLE := l_order_table; end;

And this is how the email output could be:

So, as you have seen and learned, there are many ways to create and send emails. However, you should always pay attention to some details and think in advance who the recipient group will be and what email clients you can expect.

Here is the demo app for reference.

Quellen:

• https://bit.ly/3njZtKP

Column groups heading and styling (Interactive Grid #6)

 

It´s time to continue our tips and tricks series about the Interactive Grid, that have often helped us in the development process.

Let's start with tip six :-) 

#6 Column groups heading and styling

This tip is actually very simple and comes mostly out of the box. In the past we added column groups by using CSS and JavaScript. But APEX also offers a direct low code solution that we would like to share with you. With a little bit of CSS styling on top we can design the column headers and groups as we would like. Let's start with a small demo app.

For this example we use the sample dataset "EMP / DEPT", in which we have the tables "EMP" and "DEPT" and also a view "EMP_DEPT_V" which combines both tables. Then we create an application with an interactive grid or create a new page in an existing application. As source we use the view "EMP_DEPT_V".

Now we have an interactive grid where we can add column groups. To do this, we go to the page designer, right-click under the column group regions (rendering tree) and select "Create Column Group" from the context menu. Set the Heading attribute for this new group to „Employee“. Then create another column group and set the heading attribute to „Department“.

Next, each column must be linked to a group. To establish this assignment, click on any column and in the Layout section set the "Group" property.

Done. Now we've added column groups to the interactive grid without writing any code.

But next we want to style the column groups and column headings and for that we need a little bit CSS and JavaScript code.

First we give the interactive grid a static id, for example "my_ig". Then we add some CSS code in the CSS Inline Code-Editor to style the column groups. In our example we are changing the background color of the column groups, but feel free to style anything you need.

#my_ig  .a-GV-headerGroup[data-idx="0"] { 

  background-color: #cdcdcd;

}

#my_ig  .a-GV-headerGroup[data-idx="1"] { 

  background-color: #31869b;

}

To find the right CSS object, right- click on the column group you want to style and click on inspect in the context menu to open the browser development tool.

Indeed, we want to give the column headings a background color too. But there is no declarative CSS attribute for the column header cell! So we need advanced column JavaScript Initialization Code like this.

function(config) {

    config.defaultGridColumnOptions = {

        headingCssClasses: "EmployeeHeader"

    }

    return config;

}

Enter this code to all columns assigned to the "Employees" group. Then repeat this step for all columns assigned to "Department" and enter the following in the JavaScript Code Editor.

function(config) {

    config.defaultGridColumnOptions = {

        headingCssClasses: "DepartmentHeader"

    }

    return config;

}

After that we can add a CSS rule for the column headings in the inline editor on the page. For example, enter the following to apply the colors of the column groups to the headers as well.

#my_ig .EmployeeHeader { 

  background-color: #cdcdcd;

}

  

#my_ig .DepartmentHeader { 

  background-color: #31869b;

}

That´s it. With a little bit extra Code of CSS and JavaScript, we could easily add colomn groups to an interactive grid and format them as we like.

Optional hint:

The better solution would be to condense the column javascript code into a single function so that you can link these functions to the columns you need. But in our tiny example, this quick and easy method is enough to show you the way you can hande it.

And here is the demo app.

We hope the tips and tricks will help you. 

If you have questions or suggestions, please leave us a comment

Show an additional scrollbar on top (Interactive Grid Cheats #2)

As we said in the previous blog, we want to share a few little tips and tricks with you that help us again and again and appear useful in developments.

Let's get to the second tip :-) 

#2 Show an additional scrollbar on top

It happens from time to time that a grid contains a lot of columns and therefore becomes wider than the screen. If the grid contains a lot of records, it can be difficult to see the columns that are at the right end of the grid. So you have to scroll vertically to the bottom of the grid before you can scroll the horizontal bar to the right. Then scroll up again vertically to get to the desired position. We believe that this can be annoying for every user. A possible solution is to insert an additional scroll bar above the data records. Again, only one command is enough for us. This time we add a CSS class to integrate the scrollbar.

But first of all we assign a "CSS-Class" to the grid, so that we can select it via CSS. In our example we name it "my_grid".

Then we enter the following code in the CSS inline code editor of the page:

.my_grid .a-GV-w-hdr{

    overflow-x: auto !important;

}

That´s it! An additional horizontal scrollbar was added :-)

So easily, or?

And here is the demo app.

We hope the tips and tricks will help you. If you have questions or suggestions, please leave us a comment ;-)