Oracle APEX Tips & Techniques

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. T...

In my previous blog , I have explained how you can get started with Oracle APEX Email templates. However, what if you want to show "some part of email" conditionally. Let's consider an example of ticketing system. Maybe you want to display "Service-level Agreement" (SLA) details in email notification for P1 tickets alone, and you want to hide it for all other cases. How can you do it? Creating multiple templates and using different templates in different scenarios? Yes, it will work, but are there any better ways? Yes, there are. Email templates are static. You can't write any if-else logic in the templates. You need to manage all your requirements using substitution strings. Luckily, we can implement conditional logic using substitution strings alone. Let's say you want to display below text conditionally based on ticket priority, i.e. display it for P1 tickets and hide it for all other cases. <div>Note: SLA for P1 tickets is 4 hours. Please ensur...

In Oracle APEX version 18.1, new feature "Email Templates" has been introduced. Before 18.1, either you have to embed html code in pl/sql code or you have to store HTML templates in one of your tables (or as html files in network share) with "substitution strings" (place holders) and then replace those "substitution strings" with actual values before sending email. If you are using latter approach, then it's easy to migrate your code to new "Email Templates" framework. Creating Email Templates: You can find email templates at "Shared Components > Other Components > Email Templates" section. When you are creating new email template, you may need to fill data for below fields. Template Name: Name you want to give to your template. It can't contain special characters. Only characters a-Z, 0-9 and spaces are allowed. Static Identifier: Unique template name with in the application. This will be used to call the...