Oracle APEX Tips & Techniques

Remembering Joel #JoelKallmanDay

2023-10-11T09:00:00.011+05:30

Around the end of 2008, a little over a year after I had started working with Oracle APEX, I had the opportunity to make some enhancements to an existing APEX application. I had numerous questions about APEX, but back then, it wasn't as popular as it is today. Most of my senior colleagues who worked with Oracle technology were either unaware of APEX or had only heard of it without much knowledge about it. During that time, one of my seniors introduced me to Oracle Forums, where I could ask my questions. He also shared a couple of blogs I could follow for the latest news and technical content. One of those blogs was Joel's. That was the first time I came to know about Joel. Little did I know at that time, one day I would join his team.

Joel R. Kallman

Fast forward to 2020, I had the privilege of presenting in a Zoom conference along with Joel. It was the first Office Hours session for the Oracle APEX India community. During this session, Joel had a special message for the attendees, and it wasn't about APEX.

Use your talents to help others
Donate of your excess
Buy Local

These messages were shared during a time when the world had come to a standstill because of COVID-19. Especially the first two messages, they hold true at any given point in time.

I also recall another quote from Joel:

Service Above Self

You can listen to Joel's message in the below video

All of this speaks to one thing about Joel, he is not just an Oracle Database Expert and APEX co-founder; more importantly, he is a great human being.

A year later, in March 2021, I had the incredible opportunity to join the Oracle APEX product development team. It was a significant milestone in my career, and joining this team meant working alongside some of the best minds in the industry, including Joel.

When I have joined the team, these are the messages I have received from Joel.

In the recent Oracle Cloud World 2023 event, APEX was prominently featured in several important keynotes. It was evident that APEX has become a cornerstone of Oracle's application development strategy. APEX is now the first choice for application development within Oracle.

Joel's unwavering commitment and passion for Oracle APEX were always apparent. He had poured decades of hard work and dedication into its success. The recognition and acclaim that APEX receives today are a testament to his efforts. I can't help but wish that Joel could be with us today to witness Oracle APEX reaching new heights every day.

Joel, we miss you dearly. Your legacy lives on in the continued success of Oracle APEX.

Adding Copyright information to application export files

2021-11-14T17:20:00.001+05:30

Oracle APEX 21.2 has introduced a new feature using which we can include copyright information in the application exports. There is a new application property Copyright Banner. We can enter copyright text here. We can use substitution strings #APP_NAME# and #YEAR# in the copyright text. When writing copyright text into the export files, APEX will replace these substitution strings with respective values. #APP_NAME# will be replaced with the current application name and #YEAR# will be replaced with the current calendar year in YYYY format.

So, let's enter some dummy text into Copyright Banner for testing.

Name: #APP_NAME#
All Rights Reserved.
Copyright (c) 1999, #YEAR#, XYZ-Dummy Company.
For further details please refer www.xyz-dummy.com/copyright

Now, if we export the application, then we can see the below copyright information at the top of the application export file as below. Observe, substitution strings are replaced with their values in the generated copyright text.

If we export the application as a zip file, then there will be a text file with the name copyright.txt under the exported zip file. This file will contain copyright information.

Copyright information will also be included in Component Exports. However, when exporting components as a zip file, then copyright.txt will be included in the zip file only when Include installation files flag is enabled.

What do you think about this little feature added to APEX?

Thank you.

Inline Attachments for Oracle APEX Emails

2021-11-08T13:05:00.009+05:30

Prior to APEX 21.2, when you add any attachments to APEX email using apex_mail.add_attachment procedure, then those attachments are always added with Content-Disposition as attachment. This behavior can be verified when we view the received emails as plain text, For e.g. refer to the below screenshot of an email taken using the "Show Original" option in the Gmail web client.

APEX 21.2 has introduced a new feature that enables us to send attachments as inline attachments. There is a new parameter p_content_id added to apex_mail.add_attachment procedure. We can specify content_id for attachments and we can use this content_id to refer to the image in cid: scheme in img tags of HTML emails.

In one of my previous blogposts, Including Images in Oracle APEX Emails, I have discussed a few options using which we can include images in emails. One of the options is attaching images to an email and referring to attached images by file name in cid: scheme. This works in desktop email clients, but not in web email clients like Gmail. The reason is, APEX was always using Content-Disposition: attachment; for all attachments and there was no way to change it. Now, in APEX 21.2, when we specify p_content_id for any attachments, APEX will specify Content-Disposition as inline for those attachments and it will also specify the Content-ID. This change enables embedding images in email content in more reliably way and it would work in all email clients that support cid: scheme approach.

So, let's build a quick demo page to test this new feature.

Create a new page and add a text item. In the demo, I have created page 42 and the item name is P42_EMAIL_TO. We will use this text item to enter the email address.
Add button next to the text item. Ensure, Button > Behavior > Action is set as "Submit Page". In the demo, I have created a button with the name SEND_EMAIL.
Upload any image file that you want to include in the emails to the "Static Application Files" section. In the demo, I have uploaded a file with the name ironman.jpg
Create a page process as below

Name: Testing Inline Attachments (or any other proper name)
Type: Execute Code
Server-side Condition

When Button Pressed: Select SEND_EMAIL button.

Source

Language: PL/SQL
PL/SQL Code: As below

DECLARE
    l_body        CLOB;
    l_body_html   CLOB;
    l_img_html    CLOB;
    l_id          NUMBER;
BEGIN
-- build html email body
    l_body_html := '<!DOCTYPE HTML>'
                   || '<html>'
                   || '<head>'
                   || '<meta http-equiv="Content-Type" content="text/html; charset=utf-8">'
                   || '<meta name="viewport" content="width=device-width">'
                   || '</head>'
                   || '<body style="background-color: #efefef; line-height: 1.5;">';

-- sending image as CID attachment
-- observe image src attribute src="cid:ironman.jpg"
-- email client will get the image from email attachments based on the content id specified
    l_img_html := '<h1>Below image is sent as inline attachment using 21.2 new inline attachments feature.</h1><img src="cid:ironman.jpg" alt="ironman"/>';
    l_body_html := l_body_html || l_img_html;
    l_body_html := l_body_html
                   || '</body>'
                   || '</html>';
    l_body := 'Please use HTML enabled email client to view this message.';
    l_id := apex_mail.send(
        p_to          => :P42_EMAIL_TO
        ,p_from        => 'noreply@oracle.com'
        ,p_body        => l_body
        ,p_body_html   => l_body_html
        ,p_subj        => 'Images as inline attachments'
    );

-- here I am taking one image from "Static Application Files" uploaded in shared components 
-- p_content_id value should match with CID value specified at img src
-- p_content_id is new parameter added in APEX 21.2
    FOR img IN(
        SELECT
            filename
            ,blob_content
            ,mime_type
        FROM
            apex_application_files
        WHERE
            filename = 'ironman.jpg'
            AND flow_id = :APP_ID
    )LOOP
        apex_mail.add_attachment(
            p_mail_id      => l_id
            ,p_attachment   => img.blob_content
            ,p_filename     => img.filename
            ,p_mime_type    => img.mime_type
            ,p_content_id   => 'ironman.jpg'
        );
    END LOOP;
-- optionally push email
    apex_mail.push_queue;
END;

Here is the link for the demo.

Thank you.

Interactive Grid - Process Filtered Data on Server Side

2021-10-24T20:44:00.002+05:30

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 the name P41_FILTERED_ROWS. We will use this page item to store filtered employees empno data
Create a button to submit the page
Create a page process as below

Name: Process Filtered Data (or any other proper name)
Type: Execute Code
Execution Options > Point: Processing
PL/SQL Code: As follows

declare
    l_report_id     number;
    l_region_id     apex_application_page_regions.region_id%type;
    l_context       apex_exec.t_context;
    l_empno         emp.empno%type;
    l_idx_empno     pls_integer;
    l_filtered_rows varchar2( 4000 );
begin
    -- Get the internal region id based on the "Static ID" given for the IG region
    select region_id
      into l_region_id
      from apex_application_page_regions
     where application_id = :APP_ID
       and page_id        = :APP_PAGE_ID
       and static_id      = 'emp';
    -- Get the last viewed report id based on region id
    -- this will have all the filters applied etc.
    l_report_id := apex_ig.get_last_viewed_report_id (
                       p_page_id   => :APP_PAGE_ID,
                       p_region_id => l_region_id );
    -- Open query context for the region and report using region id and report id
    l_context := apex_region.open_query_context (
                     p_page_id      => :APP_PAGE_ID,
                     p_region_id    => l_region_id,
                     p_component_id => l_report_id );
    -- get column position based on column name
    l_idx_empno := apex_exec.get_column_position( l_context, 'EMPNO' );
    -- Fetch and loop through the rows using the query context
    while apex_exec.next_row( l_context )
    loop
        -- get empno using index we have derived above
        -- you can use similar logic to get other columns data
        l_empno := apex_exec.get_number (
                       p_context     => l_context,
                       p_column_idx  => l_idx_empno );
        -- once you get all columns data you need, you can write your business logic below
        -- code specific to your business logic
        -- for demo purpose, just append l_empno to some APEX item
        l_filtered_rows := l_filtered_rows || ', ' || l_empno;
    end loop;
    -- And finally, close the query context
    apex_exec.close( l_context );
    :P41_FILTERED_ROWS := ltrim( l_filtered_rows,', ' );
exception
    when others then
        -- your general exception handing code
        apex_exec.close( l_context );
        raise;
end;

Please go through the inline comments and correlate them with the steps specified above for explanation. For the demo purpose, we are just reading data from one column empno and appending it to page item P41_FILTERED_ROWS.

That's it and here is the link for the demo.

Thank you.

SVG Pattern fill for Gantt Charts

2021-06-27T20:10:00.000+05:30

In one of the previous applications I have developed, there was a requirement to show several types of entries in the Gantt chart and I must use visually distinct colors so it would be easy for users to understand which task bar is referring to which task type. Even though there are lot of different colors available technically, distinguishing nearby colors, without keeping them side by side is not easy. Users can recognize basic colors, beyond that it gets tricky. In such cases, we need something beyond colors to fill our charts.

SVG Patterns

SVG Patterns are somewhat similar to HTML5 templates. HTML5 template tag can contain some HTML, but it will not be rendered when page is loaded. Later, we can use the template using JavaScript. In similar fashion, SVG Patterns holds graphic object, but it will not be rendered. However, the pattern can be used for other elements by referencing the pattern in the fill and/or stroke attributes. You can have quick look here to understand more about pattern element.

Now, to use SVG pattern as fill (background color), we first need to define a pattern. So, let's put below pattern code in Page > Header and Footer > Footer Text section.

<svg xmlns="http://www.w3.org/2000/svg" version="1.1">
    <defs>
        <pattern id="honeycomb" patternUnits="userSpaceOnUse" width="56" height="100">
            <rect width='56' height='100' fill='#f8d203' />
            <path d='M28 66L0 50L0 16L28 0L56 16L56 50L28 66L28 100' fill='none' stroke='#fff629' stroke-width='2' />
            <path d='M28 0L28 34L0 50L0 84L28 100L56 84L56 50L28 34' fill='none' stroke='#ffe503' stroke-width='2' />
        </pattern>
    </defs>
</svg>

This code gives us honeycomb pattern. If you want to use any other patterns, you can refer this website. If you like any pattern, just click on the get code button. That should take you to a JSBIN page. There, you can see the SVG code. If you want, you can tweak it according to your needs. After that, copy the SVG code and paste it into APEX page. When you are copying code from JSBIN page, you can use below SVG template. Basically, you have to copy pattern code from JSBIN page and then you need to copy it into pattern tag. Please read inline comments in below SVG code.

<svg xmlns="http://www.w3.org/2000/svg" version="1.1">
    <defs>
        <!--
          give unique id for this pattern, we will use id later to refer this pattern
          copy the SVG element width and height from JSBIN example to here.
        -->
        <pattern id="some_unique_id" patternUnits="userSpaceOnUse" width="56" height="100">
            <!-- copy SVG code from JSBIN to here, excluding SVG tags,   -->            
        </pattern>
    </defs>
</svg>

Using SVG Patterns for Gantt Charts

We can define classes for Gantt chart task bars. Using CSS classes, we can refer SVG patterns. For e.g. if Gantt chart task has demo-honeycomb class, then you can use below CSS code to use SVG pattern as background. Observe, we are using the pattern id honeycomb which we have defined in the above step.

path.demo-honeycomb {
  fill: url(#honeycomb);  
}

If you are not sure how to specify custom CSS classes for Gantt Chart task bars, you may refer "Colors and Borders" section in Few tips on Gantt Charts blog post.

Using Patterns for Other Chart Types

You can use patterns in other chart types in relatively easy way. For e.g. if you have a Pie chart with multiple defined series and you want to give pattern fill for series 2, then you can do it using below code. You need to put this code in Chart > Attributes > Advanced > JavaScript Initialization Code section.

function( options ){
    // Setup a callback function which gets called when data is retrieved, it allows to manipulate the series
    options.dataFilter = function( data ) {
        data.series[2].pattern = "largeCrosshatch";
        return data;
    };
    // Set chart initialization options
    return options;
}

You can also refer "Area Chart (Color JavaScript Code Customization)" chart in Page-2 in Sample Charts application for similar example. You can refer JET Documentation for list of patterns supported (check DataItem section, look for pattern property).

Demo link

Thank You.

Oracle APEX Tree Search

2021-06-20T21:35:00.006+05:30

Implementing search feature for the tree region is little tricky. Consider we have tree region created on the employee table EMP as below.

If we search for employee JAMES and we want to show JAMES node, then we must show everyone up in the hierarchy till the root node, i.e. KING > BLAKE > JAMES. Luckily, we can easily get hierarchy (traverse path) using sys_connect_by_path.

For e.g. consider below query

 select ename,
        empno,
        mgr,
        sys_connect_by_path(empno, ':')||':' as emp_path
   from emp
  start with mgr is null
connect by prior empno = mgr
  order siblings by ename

This query gives all employee rows from EMP table, along with path data, from the root node to current node. Now, we can use this path data to filter rows that are required to show any node. For e.g. we can use below query to get all the required nodes to show JAMES node.

with emp_tree as (
 select ename,
        empno,
        mgr,
        sys_connect_by_path(empno, ':')||':' as emp_path
   from emp
  start with mgr is null
connect by prior empno = mgr
  order siblings by ename ),
emp_filtered_data as (
 select ename,
        empno,
        mgr
   from emp
  where exists (select *
                     from (
                         select emp_tree.emp_path
                           from emp_tree 
                          -- searching for employees with name like JAMES
                          where upper(emp_tree.ename) like '%JAMES%') ep
                    where ep.emp_path like '%:'||emp.empno||':%')
)
select *
  from emp_filtered_data

Query output:

ENAME	EMPNO	MGR
KING	7839	-
BLAKE	7698	7839
JAMES	7900	7698

Now, we can use this filtered data to generate tree. So, let's build a demo page using this technique.

Let's create an empty page and create a text field for entering search term. In demo application, I have used P40_SEARCH as item name. Next, create tree region using below query.

with emp_tree as (
 select ename,
        empno,
        mgr,
        sys_connect_by_path(empno, ':')||':' as emp_path
   from emp
  start with mgr is null
connect by prior empno = mgr
  order siblings by ename ),
emp_filtered_data as (
 select ename,
        empno,
        mgr
   from emp
  where :P40_SEARCH is null
        OR exists (select *
                     from (
                         select emp_tree.emp_path
                           from emp_tree 
                          where upper(emp_tree.ename) like '%'||upper(:P40_SEARCH)||'%') ep
                    where ep.emp_path like '%:'||emp.empno||':%')
)
/*
  If we derive status, level in emp_tree query and if we try to use it here, tree region (sometimes) will throw error
  because filering can change the order of rows and tree region expects correct order (depth first traversal) of rows as generated by hierarchical query
*/
 select case when connect_by_isleaf = 1 then 0 when level = 1 then 1 else -1 end as status,
        level,
        ename as title,
        'icon-tree-folder' as icon,
        empno as value,
        ename as tooltip,
        null  as link
   from emp_filtered_data
  start with mgr is null
connect by prior empno = mgr
  order siblings by ename

Also, please specify

P40_SEARCH as Page Items to Submit
emp_tree as Static ID for the tree region. We will use this later in dynamic actions

Now, let's create a dynamic action, which will listen to "Enter Key Press" event of the search field as below.

Name: Search (or any proper name)
When:

Event: Key Down
Selection Type: Item(s)
Item(s): P40_SEARCH

Client-side Condition:

Type: JavaScript expression
JavaScript Expression: this.browserEvent.which === 13
True Action-1: (to refresh tree region)

Action: Refresh
Affected Elements:

Selection Type: Region
Region: <Tree Region>

That's it. Now, if we type anything in search field and hit enter key, tree will show filtered nodes as per the search term. However, it may not be clear to users which nodes are matching the search criteria. Because, we are showing matching nodes and their parent nodes till the root node. So, it would be helpful, if we can highlight the nodes that are matching the search criteria. We can do this using find method of treeView widget.

To implement this, let's keep below JavaScript code in Page > JavaScript > Function and Global Variable Declaration section (preferably in your application specific JavaScript file).

function treeFind ( pTree$, pSearchString, pClassName ) {
    // find the string in tree nodes
    var lMatchedNodes$ = pTree$.treeView( "find", {
        depth: -1,
        findAll: true,
        match: function( n ) {
            return n.label.toLowerCase().indexOf( pSearchString.toLowerCase() ) >= 0;
        }
    } );
    // add CSS class to matching nodes
    lMatchedNodes$.addClass( pClassName );

    // find is used in conjunction with server side filtering
    // so when there are matching nodes, we just expand all nodes, so users can see matched nodes easily
    if ( lMatchedNodes$.length > 0 ) {
        pTree$.treeView( "expandAll" );
    }
}
    
function treeSearch  ( pSearchString, pStaticId, pClassName ) {
    // remove CSS classes from previous search, if any
    apex.jQuery( "." + apex.util.escapeCSS( pClassName ), "#" + apex.util.escapeCSS( pStaticId )).removeClass( pClassName );
    if ( pSearchString !== "" ) {
        treeFind( apex.region( pStaticId ).widget(), pSearchString, pClassName );
    }
}

Next, let's create DA like below.

Name: Highlight Matching Nodes (or any proper name)
When:

Event: After Refresh
Selection Type: Region
Region: <Tree Region>

True Action-1:

Action: Execute JavaScript Code
Settings:

Code: treeSearch ( $v("P40_SEARCH"),'emp_tree','u-color-22-bg' );

In above DA code emp_tree is Static ID specified for the tree region. u-color-22-bg is universal theme utility class for adding background color to matching tree nodes.

That's it and we are good to go. Here is the demo link.

Thank You 🙏

Interactive Grid - Freeze/Unfreeze Columns Using JavaScript

2021-02-28T20:12:00.002+05:30

In Oracle APEX forums, there was a question about "how to conditionally unfreeze interactive grid columns based on the screen size". I had bookmarked this question on my blog notes. Because, this is a good candidate for blogpost as it's a common use case and it may be helpful for other developers.

Photo by Aaron Burden on Unsplash

So, I have started building demo for this blogpost. Then, I found that freezing, unfreezing columns based on column name may not be the best solution, because whenever we change column order, then we need to change JavaScript code as-well. With further research (reading documentation thoroughly 😊), I found out that we can also pass column definition object while using freezeColumn and unfreezeColumn methods. This solves the problem with column names.

And then I did a little google search and found another similar question in APEX forums where John Snyders has provided a solution. This solution looks little different. Here, column property frozen is updated directly. Then I went back to documentation and found that freezeColumn/unfreezeColumn methods trigger the grid refresh as-well along with updating the column property. So, when we are freezing, unfreezing multiple columns, it is better to update the frozen property value first and at the end we can refresh the grid (only once).

Based on this, I have written a JavaScript function which freezes, unfreezes interactive grid columns based on screen size (window/viewport width). Here I am freezing up to first 4 columns based on screen size. If screen size is more than 1200 pixels, then I am freezing first 4 columns and if screen size is less than 600 pixels, then I am freezing only 1st column. When screen size is in between 600 and 1200 pixels, I am freezing 2 or 3 columns. I have used this logic for demo purpose and of course you can change it based on your requirements.

JavaScript function code:

function freezeUnfreezeIG(pIGStaticID) {
    apex.debug.info("BEGIN freezeUnfreezeIG");
    var grid$ = apex.region(pIGStaticID).call("getCurrentView").view$;
    apex.debug.info(grid$);
    // get column objects array
    var columnsArr = grid$.grid("getColumns");
    // get window width
    var winWidth = $(window).width();
    //apex.debug.info(`Window Width ${winWidth}`);
    // Unfeeze first 4 columns, ignoring hidden columns
    var i, counter = 0;
    for (i = 0; i < columnsArr.length; i++) {
        if (!columnsArr[i].hidden) {
            apex.debug.info(columnsArr[i]);
            columnsArr[i].frozen = false;
            //grid$.grid("unfreezeColumn", columnsArr[i]);
            counter++;
        }
        if (counter === 4) break;
    }
    var maxFrezee = 0;
    // determine number of columns to freeze based on window width
    if (winWidth <= 600) {
        maxFrezee = 1;
    }
    else if (winWidth > 600 && winWidth <= 900) {
        maxFrezee = 2;
    }
    else if (winWidth > 900 && winWidth <= 1200) {
        maxFrezee = 3;
    }
    else {
        maxFrezee = 4;
    }
    // freeze columns
    counter = 0;
    for (i = 0; i < columnsArr.length; i++) {
        if (!columnsArr[i].hidden) {
            columnsArr[i].frozen = true;
            //grid$.grid("freezeColumn",columnsArr[i]);
            counter++;
        }
        if (counter === maxFrezee) break;
    }
    grid$.grid("refreshColumns").grid("refresh");
    apex.debug.info("END freezeUnfreezeIG");
}

Demo:

For demo purpose, let's create a page with editable interactive grid on EMP table and give Static ID as emp.

Next, keep above JavaScript function code in Page > JavaScript > Function and Global Variable Declaration section.

Next, create dynamic action as below.

Name: Freeze/UnFreeze IG Columns (or any proper name)
When:

Event: Custom
Custom Event: apexwindowresized
Selection Type: JavaScript Expression
JavaScript Expression: window

True Action: Execute JavaScript Code

Code: freezeUnfreezeIG("emp");
Execution Options:

Fire on Initialization: Yes (Enable)

Here, please observe, this dynamic action is fired when window is resized, as-well as when page is initially loaded. Executing this dynamic action on window resize will be helpful in several cases, for e.g. consider users with laptop and external monitor set-up. If users move your application window from external monitor to laptop, then you may want to unfreeze some of the columns as laptop screen width may be less compared to external monitor.

That's it and here is the link for demo.

Thank you.

Handling JavaScript Errors - Part 2

2021-01-23T20:29:00.002+05:30

In my previous blogpost, I have explained about JavaScript errors and how we can handle them in general and how we can handle them at application level. If you have not read it yet, you can read it here. In this blogpost, I will explain an approach to log JavaScript errors to a database table.

error event on window object provides lot of information. I found below information is useful for debugging and worth logging into DB.

event.message: JavaScript error message
event.filename: If source of the error is from a JavaScript file, then this will give full path of JavaScript file. If error is from inline JavaScript code written in APEX page, then this will give full APEX page URL including APEX session.
event.lineno: Line number where error has occurred. Depending on event.filename, it could refer to line number from JavaScript file or from generated HTML page.
event.colno: Column number where error has occurred. It should be read along with event.lineno.
event.error.stack: Call stack information. Similar to DBMS_UTILITY.FORMAT_ERROR_STACK in PL/SQL.

Now, let's create a table to store these JavaScript errors. Please find sample table creation script below which I have used for the demo.

-- create tables
create table apex_js_error_log (
    aje_log_id                     number generated by default on null as identity
                                   constraint apex_js_error_log_id_pk primary key,
    message                        varchar2(4000 char),
    filename                       varchar2(255 char),
    lineno                         number,
    colno                          number,
    error_stack                    clob,
    app_id                         number,
    page_id                        number,
    session_id                     number,
    app_user                       varchar2(255 char),
    created_on                     date not null,
    created_by                     varchar2(255 char) not null
);

-- triggers
create or replace trigger apex_js_error_log_bi
    before insert
    on apex_js_error_log
    for each row
begin
    :new.created_on := sysdate;
    :new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
end apex_js_error_log_bi;
/

Let's create a PL/SQL function to log errors.

CREATE OR REPLACE FUNCTION log_js_error (
    p_message      IN  VARCHAR2,
    p_filename     IN  VARCHAR2,
    p_lineno       IN  NUMBER,
    p_colno        IN  NUMBER,
    p_error_stack  IN  VARCHAR2,
    p_session_id   IN  NUMBER,
    p_app_id       IN  NUMBER DEFAULT apex_application.g_flow_id,
    p_page_id      IN  NUMBER DEFAULT apex_application.g_flow_step_id,
    p_app_user     IN  VARCHAR2 DEFAULT apex_application.g_user
) RETURN NUMBER IS
    l_aje_log_id apex_js_error_log.aje_log_id%TYPE;
BEGIN
    INSERT INTO apex_js_error_log (
        message,
        filename,
        lineno,
        colno,
        error_stack,
        app_id,
        page_id,
        session_id,
        app_user
    ) VALUES (
        p_message,
        p_filename,
        p_lineno,
        p_colno,
        p_error_stack,
        p_app_id,
        p_page_id,
        p_session_id,
        p_app_user
    ) RETURNING aje_log_id INTO l_aje_log_id;

    RETURN l_aje_log_id;
END log_js_error;

Let's create an application process LOG_JS_ERROR to call and log errors.

Name: LOG_JS_ERROR
Process Point: Ajax Callback: Run this application process when requested by a page process.
Source:

Code: As shown below

DECLARE
    l_aje_log_id   apex_js_error_log.aje_log_id%TYPE;
    l_json_output  json_object_t;
BEGIN
    l_aje_log_id := log_js_error(p_message => apex_application.g_x01,
                                p_filename => apex_application.g_x02,
                                p_lineno => apex_application.g_x03,
                                p_colno => apex_application.g_x04,
                                p_error_stack => apex_application.g_x05,
                                p_session_id => :app_session);
    -- Send error log id as JSON object to JavaScript
    -- If you are using older version of DB (< 12.2), then you can use APEX_JSON APIs to generate JSON
    l_json_output := NEW json_object_t;
    l_json_output.put('aje_log_id', l_aje_log_id);
    htp.p(l_json_output.to_string);
END;

And finally, let's create a JavaScript function to make an AJAX call which logs errors into DB table.

function logError(pMessage, pFileName, pLineNo, pColNo, pErrorStack) {
    // clear the errors 
    apex.message.clearErrors();
    // make an AJAX call and log error
    // error stack could be more than 32k, limit text to 30k
    var result = apex.server.process("LOG_JS_ERROR", {
        x01: pMessage,
        x02: pFileName,
        x03: pLineNo,
        x04: pColNo,
        x05: pErrorStack.substr(0, 30000)
    });
    result.done(function (data) {
        apex.message.showPageSuccess("Thanks for reporting this error. Reference # " + data.aje_log_id);
    }).fail(function (jqXHR, textStatus, errorThrown) {
        // APEX already shows errorThrown as error message
        // Somecases jqXHR.responseText will have useful info, so show jqXHR.responseText also as error message
        apex.message.showErrors({
            type: "error",
            location: "page",
            message: jqXHR.responseText,
            unsafe: false
        });
    });
}

In the demo application, I have added this function to js_error_logging.js file and then I have referred the JS file in Application > User Interfaces > JavaScript > File URLs section. If you are not sure what's best way to include JavaScript code to APEX applications, then please do read Adding JavaScript to an Application Express application from APEX documentation.

Now, let's create Dynamic Action (DA) in Page-0 as below.

Name: Handle Errors or any proper name
Execution Options:

Sequence: 0

When:

Event: Page Load

True Action:

Action: Execute JavaScript Code
Code: As shown below

window.addEventListener('error', function (event) {
    // show APEX error message
    // provide option for users to log/report the error
    apex.message.showErrors({
        type: "error",
        location: "page",
        message: "Unhandled JavaScript Error. Please <a id='log_js_error' style='cursor: pointer;'>click here</a> to report this error.",
        unsafe: false
    });
    // log error if user clicks on the link
    $("#log_js_error").click(function () {
        logError(event.message, event.filename, event.lineno, event.colno, event.error.stack);
    });
});

If you want to enable automatic error handling only for few APEX pages, then you can achieve it by defining "Server Side Condition" for this DA.

If you see above DA code, we are using apex.message.showErrors to inform users about JavaScript errors. And then, we are providing option for users to log/report the error, instead of automatically logging the error. If we automatically log errors, then in some cases it could be overwhelming and in some cases it could cause additional problems. For e.g. if you have scheduled to execute some code repeatedly using setInterval, and if the code throws any JavaScript errors, and if we automatically log errors to DB, then it can cause additional traffic to server and in some cases can consume all DB connections at ORDS level.

Above approach, where we are just showing error message could be equally annoying with setInterval. If JavaScript throws any errors, then users will see error messages continuously. So, in such cases, we can use "Nested setTimeout" technique to simulate setInterval.

Example code for "Nested setTimeout" below.

let timerId = setTimeout(function doSomeTask() {
  // throws error  
  console.log(pValue);
  // go for next iteration only if current iteration is successful
  // below code will be executed only if all of the above code is executed successfully  
  timerId = setTimeout(doSomeTask, 5000); 
}, 5000);

You can read more about Scheduling with setTimeout vs setInterval here.

That's it and you can see the demo here.

Caution: This approach can be misused to perform Denial-of-service attack. So, I suggest to use this approach with caution. It's best to use this approach in UAT/Staging environments where users test your applications. This will help to detect any JavaScript errors and subsequently to make your applications error free.

If you are not already aware, when you are running application as Developer, APEX gives you little red color warning icon in developer toolbar, so we can find out most of JS errors during development phase itself.

Thank you.

Handling JavaScript Errors - Part 1

2021-01-01T09:27:00.002+05:30

Before APEX version 5.0, there were only handful of documented JavaScript (JS) functions in APEX. Now, there are several JavaScript namespaces and hundreds of functions and methods available in Oracle APEX. From APEX 18.1, separate documentation section "JavaScript API Reference" is also added for JavaScript. So, it's evident that usage of JavaScript in APEX is increasing every day and with every release.

But, how are you handling JavaScript errors in your APEX applications? For server-side errors, we can use EXCEPTION block and we can also define "Error Handling Function" at application level. What about JavaScript code? Can we do something similar for JavaScript errors?

In this blog, I am going to briefly explain about JavaScript errors and how we can handle them in general and how we can handle them at application level.

Introduction

Browsers have JavaScript engine inside them. Mozilla's TraceMonkey, Google's v8 are examples for JavaScript engines. JavaScript engines perform both code interpretation and execution. Browsers execute HTML code from top to bottom. When browser finds a script tag, then control is shifted to JS engine. Then JS Engine interprets (and/or executes) the code from top to bottom. When JS Engine finds any error during interpretation, then JS engine will throw the error and it will stop interpreting any further code. Errors occurred during interpretation are called "Syntax Errors" and errors occurred during code execution are called "Runtime Errors".

Syntax Errors

For e.g. below JavaScript is invalid and it will throw Uncaught SyntaxError: missing ) after argument list error after the page load.

function doTask1(pValue)
{
    console.log(pValue;
}

When a Syntax Errors occurs, then entire JS block code is invalidated. When there is a syntax error, then all the variables, functions defined in the JS block will be invalidated. For e.g. consider below JS Code which has two blocks.

<!--JS Block-1-->
<script type="text/javascript">
    // function with-out any issues
    function noIssuesBlock1() {
        console.log('I am noIssuesBlock1');
    }
    // below function throws syntax error 
    function doTask1(pValue) {
        console.log(pValue;
    }
</script>
<!--JS Block-2-->
<script type="text/javascript">
    // function with-out any issues
    function noIssuesBlock2() {
        console.log('I am noIssuesBlock2');
    }
</script>

Create a new APEX page and put above code in "HTML Header" section. Save and run the page. We will see below error in browser console.

Uncaught SyntaxError: missing ) after argument list

Now, try to invoke noIssuesBlock1() using browser console, then we will get Uncaught ReferenceError: noIssuesBlock1 is not defined error. Try to invoke noIssuesBlock2(), it will work without any errors. So it is clear that entire block-1 code is invalidated when a syntax error is encountered.

In general, it's very unlikely that you have syntax errors in production applications. Also, we can't catch syntax errors using traditional error handling approaches in JavaScript. So, I am not going discuss about Syntax Errors further.

Runtime Errors

Run time errors are errors that are raised during execution of the code. For e.g. consider below code.

function doTask1()
{
    console.log(pValue);
}

As per JS syntax it all looks good. So we don't get any error during initial page load. But when we invoke it, then we will get Uncaught ReferenceError: pValue is not defined error.

When JS Engine gets a runtime error, then error will be thrown and code execution will be stopped. For e.g. Create a new empty page in APEX and put below JS code in "Function and Global Variable Declaration" section.

// function with-out any issues
function noIssues() {
    console.log('I am noIssues');
}
// below function throws runtime error
function doTask1() {
    console.log(pValue);
}
// function with-out any issues
function anotherNoIssues() {
    console.log('I am anotherNoIssues');
}

Now, put below code in "JavaScript > Execute when Page Loads" section.

// noIssues will be executed
noIssues();
// doTask1 will throw runtime error
doTask1();
// anotherNoIssues will not be called
anotherNoIssues();

When we save and run the page, we can see below output in error in browser console.

As we can see noIssues() is executed successfully, doTask1() is executed but ended up with an error and anotherNoIssues() is not been called. Same thing will happen with Dynamic Actions (DA).

Let's create 3 DAs that are executed during "Page Load" event as below.

DA-1:

Name: DA-1
Execution Options:

Sequence: 10

When:

Event: Page Load

True Action:

Action: Execute JavaScript Code
Settings:

Code: noIssues();

DA-2:

Name: DA-2
Execution Options:

Sequence: 20

When:

Event: Page Load

True Action:

Action: Execute JavaScript Code
Settings:

Code: doTask1();

DA-3:

Name: DA-3
Execution Options:

Sequence: 30

When:

Event: Page Load

True Action:

Action: Execute JavaScript Code
Settings:

Code: anotherNoIssues();

In such case, DA-1 will get executed successfully. DA-2 will get executed and it will throw JS error and DA-3 won't be executed. When there are multiple DAs defined with "Page Load" event, then DAs are executed based on the "Sequence" specified at DA level.

Handling Runtime Errors

Let's create an empty page (Page 1) and put below JavaScript code in "Function and Global Variable Declaration" section.

function performChecks() {
    console.log('performChecks executed successfully.');
}

function doTask1() {
    console.log(pValue);
}

Let's put below code in "Execute when Page Loads" section.

performChecks();
doTask1();

Save and run the page. If you see the browser console, then we can see below output.

We can handle these runtime errors using try-catch-finally statement.

try {
    // try to execute code specified in this block
}
catch (e) {
    // code to handle any runtime errors from the try block
}
finally {
    // clean up code that is always executed, irrespective of any runtime errors
}

We can use try-catch-finally either inside the function doTask1 or while calling this function. Let's change our "Execute when Page Loads" section code as below in Page 1.

performChecks();
try {
    doTask1();
}
catch (e) {
    console.log('Error occurred while executing doTask1: ' + e.message);
}
finally {
    console.log('Code from finally block');
}

Here, we have handled the errors inside our page code. But, what if, we have JS code in several places and in several pages? Is there any better way to handle these errors globally?

Yes, there is! To handle JavaScript errors globally, let's define an DA in Page 0 as below.

Name: Handle Errors or any proper name
Execution Options:

Sequence: 0

When:

Event: Page Load

True Action:

Action: Execute JavaScript Code
Code: As shown below

window.addEventListener('error', function (event) {
    // write code here to handle JS errors
    // data what we can get from error event
    console.log(event);
    console.log(event.type);
    console.log(event.message);
    console.log(window.location.href);
    console.log(event.lineno);
    if (apex.debug.getLevel() == 0) {
        // debug off
        // do not show errors in Browser console    
        event.preventDefault();
    }
});

JavaScript error event gives lot of information about the error. Few important error details are logged to console in the above code example.

If you trying this code in normal pages (not in Page 0), then don't put window.addEventListener code in "Execute when Page Loads" section. Because, APEX first executes DAs with event "Page Load" first and then, the code specified in "Execute when Page Loads" is executed. So, if we use "Execute when Page Loads" section, then any errors that we may get in page load DAs will not be handled.

Now, let's rollback "Execute when Page Loads" section code in Page 1 as below.

performChecks();
doTask1();

Save the changes and run the page. Now, we can see error from Page 1 is captured using DA defined in Page 0 and we can see messages logged from Page 0 DA in browser console.

That's it for now. In my next blogpost, I will provide some ideas on how we can log these errors to the database.

Thank You 🙏

💐 Wish you happy new year 2021 💐

Web Components in Oracle APEX

2020-12-07T18:58:00.003+05:30

Few years back, I have learned about Web Components. However, I did not get a chance to work on Web Components yet. It's been on my do to list to experiment Web Components, especially with in APEX. My current staycation gave me an opportunity to do this experiment.

Web Components

Web Components is a suite of different technologies allowing you to create reusable custom elements — with their functionality encapsulated away from the rest of your code — and utilize them in your web apps.

In other words, using Web Components, we can create custom elements which are completely self contained with all HTML, CSS and JavaScript (JS) encapsulated in one place. And then we can use these new custom element in our web applications with out the risk of code conflicts. Functionality wise, they are similar to jQuery widgets, however to implement Web Components we don't need any additional libraries, all modern browsers natively support underlying technologies. If you are new to Web Components, then I suggest you to go through Web Components Crash Course video or this article. Above quoted definition is taken from this article. Example we are going to discuss is inspired from the above crash course video.

For the demo purpose, let's consider an example. We want to create a new custom element country-info with an attribute countrycode. When we specify countrycode, then this element should display basic information about the country, by making call to an existing RESTful API.

HTML Source

<country-info countrycode="IN"></country-info>

Target Output

Web Components mainly consists of three main technologies.

HTML Templates
Custom Elements
Shadow DOM

HTML Templates

HTML templates uses new template tag. As the name says, these are just templates and code written inside template tag is not rendered during the page load. We can use templates to hold some HTML/CSS and later, when needed, we can use these templates using JS. You can read further about template element here.

So, let's first create a template for our example.

// JavaScript code
// create template element
const template = document.createElement('template');
// Write HTML and CSS into the template
// with Shadow DOM, CSS defined here will be applicable to custom elements alone
// CSS defined at page level won't be applicable to custom elements
template.innerHTML = `
  <style>
  div.country-data {
    background: #F5F4F2;
    width: 400px;
    margin-bottom: 10px;
    border-bottom: purple 8px solid;
    padding: 4px;
  }
  div.country-data h2{
    color: purple;
  }
  </style>
  <div class="country-data">
    <h2></h2>
    <div class="population">Population: <span></span> Millions</div>
    <div class="area">Area: <span></span> thousand sq.km.</div>
  </div>
`;

Custom Elements + Shadow DOM

Custom Elements are fully valid HTML elements with custom tag names and custom behavior defined using JS. To define a custom element, first we need to create a class that extends HTMLElement and then at the end we need to define custom element tag with the class name using window.customElements.define.

Using shadow DOM, we can isolate the custom element code from the page, so that page CSS, JS won't get conflicted (or used) for custom elements and vice-versa.

JS code for our example

// define class for the custom element
// general convention followed : if the custom element name is custom-element, then CustomElement is used as class name
// our custom element name is country-info, so lets define CountryInfo as class name
class CountryInfo extends HTMLElement {
  constructor() {
    // always call super first in constructor, so that the correct prototype chain is established.
    super();
    // attach Shadow DOM, this isolates the custom element
    this.attachShadow({ mode: 'open' });
    // clone the template content and append to shadowRoot
    this.shadowRoot.appendChild(template.content.cloneNode(true));
    // fetch country data from RESTful API
    // here, we are reading custom attribute countrycode value and using it as input for RESTful API call
    // fill template with data from RESTful API response
    fetch('https://apex.oracle.com/pls/apex/hari/country-data/country/' + this.getAttribute('countrycode')).then(response => response.json()).then(data => {
      this.shadowRoot.querySelector('h2').innerText = data.name;
      this.shadowRoot.querySelector('div.population > span').innerText = data.population;
      this.shadowRoot.querySelector('div.area > span').innerText = data.area;
    });
  }
}
// map country-info tag to use CountryInfo class
window.customElements.define('country-info', CountryInfo);

Using Web Components in APEX

To use this Web Component in APEX, let's save all this JS code to a file. I have copied template code, CountryInfo class code to a JS file. I have used same name for custom element and to the JS file. Having same name for both custom element and corresponding JS file is not a requirement, but it's a good practice. So, it our case, JS file name is country-info.js. Let's upload it in "APEX Application > Shared Components > Static Application Files" section.

Next, create an empty page in APEX and add below code in "Page > HTML Header" section.

<script type="module" src="#APP_IMAGES#country-info.js"></script>

Here, I would like to highlight two points

We are not using standard "Page > JavaScript > File URLs" section to load this JS file, instead we are writing script tag in HTML Header section.
Script tag has type attribute with module as it's value.

When we load JS file using as module, then functions, variables declared in the JS file will not be available in the global scope. For e.g. if you try to run below JS code in browser console, then you will get an error, even though template is declared in the JS file.

console.log(template.innerHTML);

As the aim of using Web Components is to completely make our code re-usable (just plug and play), it's better to use this approach to load Web Component JS files as modules. You can read more about JS Modules here.

Next, create a "Static Content" region with below source in the APEX page.

<country-info countrycode="IN"></country-info>

That's it and here is the demo. When we inspect country-info element using browser developer tools, we can see shadow-root and the template we have defined with country data.

Now, whereever we want to use our custom element, then we can simply include the country-info.js file in page and we can write country-info HTML tag. Everything will magically works.

Adding User Interaction

Let's make this more interesting by adding some flexibility for users to choose which countries data they would like to see. For this, let's create a checkbox group item for countries, with Country Name as display value and Country Code as return value. Page item name in this demo is P35_COUNTRIES.

Now, modify the "Static Content" region source as below

<div id="country_data_container"></div>

Next, Create a Dynamic Action as below

Name: Display Country Data (or any proper name)
When:

Event: Change
Selection Type: jQuery Selector
jQuery Selector: input[name=P35_COUNTRIES]

True Action:

Action: Execute JavaScript Code
Settings:

Code: As shown below

JS Code:

// get country code from the checkbox item
let countryCode = $(this.triggeringElement).val();
// get country-info tag for the countryCode, if it's previously added to the DOM
let countryNode = $("country-info[countrycode=" + countryCode + "]");
if ($(this.triggeringElement).is(':checked')) {
    // check if element already exists, then just show it
    if (countryNode.length == 1)
        countryNode.show();
    else
        // otherwise add it to DOM
        $('#country_data_container').append('<country-info countrycode="' + countryCode + '"></country-info>');
}
else {
    // unchecked, hide it
    countryNode.hide();
}

That's it and here is the demo.

As I said above, this is a little experiment I did with the Web Components. This is a simple Web Component and we are not using template slots, custom element lifecycle callbacks etc. You can refer webcomponents.org to find out some useful and advanced Web Components published by others.

Conclusion

Web Components are clean and elegant. Once we build a web component, we can re-use it any web application without any fear of conflicts. As we have seen above, we can use Web Components in APEX in relatively simple way. However, to make use of APEX framework features like server-side conditions, authorization schemes etc. it is better to wrap Web Components as APEX plug-ins. Then we can get best of the both the worlds.

Have you used any Web Components in your APEX applications? What did you like most about using Web Components and what challenges have you faced while using them in APEX? Post your comments below.

Thank you.

Oracle APEX Displaying Help Text

2020-11-28T21:51:00.002+05:30

When we define "Help Text" for page items, then APEX displays a little "?" icon next to the page item. For Interactive Grids (IG), help icon is displayed when users click on the column header. When we click help icon, then we can see the help text defined for the item. In some cases, this may not be very handy for users. Let's consider a user registration form in your website. Users might be coming for the first time to your website and users may not be aware that they must click on the little icon to see the help text. In such cases, displaying help information proactively will be helpful for users.

In this blogpost, I am going to explain a simple approach to proactively display item's help text to users.

Summary of this implementation:

Create "Help" region which displays help text based on page Item Name or IG column Static ID.
When user focuses on any field, then get the Item Name or IG column Static ID. Set these values into hidden page items and refresh the "Help" region.

So, first, let's create a function which returns item help text for a given page item. Function code below.

    CREATE OR REPLACE FUNCTION get_item_help(
        p_item_name        IN                 VARCHAR2
        ,p_page_id          IN                 NUMBER DEFAULT NULL
        ,p_application_id   IN                 NUMBER DEFAULT NULL
    )RETURN VARCHAR2 IS
        l_help_text   apex_application_page_items.item_help_text%TYPE;
    BEGIN
        SELECT
            item_help_text
        INTO l_help_text
        FROM
            apex_application_page_items
        WHERE
            item_name = p_item_name
            AND page_id = coalesce(
                p_page_id
                ,nv(
                    'APP_PAGE_ID'
                )
            )
            AND application_id = coalesce(
                p_application_id
                ,nv(
                    'APP_ID'
                )
            );

        RETURN l_help_text;
    EXCEPTION
        WHEN no_data_found THEN
            RETURN 'Invalid Item Name ' || p_item_name;
    END get_item_help;

Let's create another function to get IG column's help text. This function gives us help text defined for IG column based on IG region Static ID and column Static ID. Function code below.

    CREATE OR REPLACE FUNCTION get_ig_col_help(
        p_col_static_id    IN                 VARCHAR2
        ,p_reg_static_id    IN                 VARCHAR2
        ,p_page_id          IN                 NUMBER DEFAULT NULL
        ,p_application_id   IN                 NUMBER DEFAULT NULL
    )RETURN VARCHAR2 IS
        l_help_text   apex_appl_page_ig_columns.help_text%TYPE;
    BEGIN
        SELECT
            cols.help_text
        INTO l_help_text
        FROM
            apex_appl_page_ig_columns cols
            JOIN apex_application_page_regions reg
            ON(reg.region_id = cols.region_id)
        WHERE
            cols.static_id = p_col_static_id
            AND reg.static_id = p_reg_static_id
            AND cols.page_id = coalesce(
                p_page_id
                ,nv(
                    'APP_PAGE_ID'
                )
            )
            AND cols.application_id = coalesce(
                p_application_id
                ,nv(
                    'APP_ID'
                )
            );

        RETURN l_help_text;
    EXCEPTION
        WHEN no_data_found THEN
            RETURN 'Invalid Input. Column Static ID '
                   || p_col_static_id
                   || ', Region Static ID '
                   || p_reg_static_id
                   || ' do not exists.';
    END get_ig_col_help;

I have added these functions to my open source package APEX_UTIL_WRAP and you can download it from here.

For the demo purpose, I have created a form page with page number 33. It has few input items and an editable IG as below.

Let's change "Page Template" for this page to "Right Side Column". This will enable "Right Column" option for region "Position".

Next, create a new region as below. This region will display help text for the selected page item or selected IG column.

Title: Help
Type: Classic Report
Source:

Type: SQL Query
SQL Query: As shown below
Page Items to Submit: P33_ITEM_NAME,P33_REG_STATIC_ID,P33_COL_STATIC_ID

Layout:

Position: Right Column

Attributes:

Pagination:

Type: No Pagination (Show All Rows)

Messages:

When No Data Found: Please focus on any input field to see the help text defined for the field.

SQL Query:

SELECT get_item_help(:P33_ITEM_NAME) help_text
FROM DUAL
WHERE :P33_ITEM_NAME IS NOT NULL
UNION ALL
SELECT get_ig_col_help(:P33_COL_STATIC_ID,:P33_REG_STATIC_ID) help_text
FROM DUAL
WHERE :P33_COL_STATIC_ID IS NOT NULL

Alternatively, we can directly write the SQL query based on APEX meta data views, in that case, no need to create these functions. Also, we can avoid context switch between SQL and PL/SQL.

SQL Query:

SELECT
    item_help_text help_text
FROM
    apex_application_page_items
WHERE
    item_name = :P33_ITEM_NAME
    AND page_id = :APP_PAGE_ID
    AND application_id = :APP_ID            
UNION ALL
SELECT
    cols.help_text
FROM
    apex_appl_page_ig_columns cols
    JOIN apex_application_page_regions reg
    ON(reg.region_id = cols.region_id)
WHERE
    cols.static_id = :P33_COL_STATIC_ID
    AND reg.static_id = :P33_REG_STATIC_ID
    AND cols.page_id = :APP_PAGE_ID
    AND cols.application_id = :APP_ID

For "help_text" column, disable "Escape special characters" option. As a developer, we can write HTML for page items or IG columns help text. So, we must disable "Escape special characters" option for proper rendering of HTML.

Next, create 3 hidden page items with below names in "Help" region. Ensure "Value Protected" flag is disabled for these items.

P33_ITEM_NAME
P33_REG_STATIC_ID
P33_COL_STATIC_ID

Next, create a Dynamic Action (DA) as below. This DA is fired when user focuses on page items. This DA sets hidden page item values and triggers refresh of "Help" region. In this demo, I want to show help text for P33_FIRST_NAME, P33_LAST_NAME, P33_GENDER, P33_PROFESSION, P33_HOBBIES, P33_DATE_OF_BIRTH items. So, I have selected these items in DA.

Name: Show Items Help Text
When:

Event: Get Focus
Selection Type: Item(s)
Item(s): P33_FIRST_NAME,P33_LAST_NAME,P33_GENDER,P33_PROFESSION,P33_HOBBIES,P33_DATE_OF_BIRTH

True Action-1:

Action: Execute JavaScript Code
Settings:

Code: As shown below

JavaScript Code:

apex.item("P33_ITEM_NAME").setValue($(this.triggeringElement).attr("id"));
apex.item("P33_REG_STATIC_ID").setValue("");
apex.item("P33_COL_STATIC_ID").setValue("");

True Action-2:

Action: Refresh
Affected Elements:

Selection Type: Region
Region: Help

Let's create another DA for IG columns. This DA is fired when user focuses on IG columns. This DA sets hidden page item values and triggers refresh of "Help" region. In this demo, "Skills" is name of the IG region and "skills" is the "Static ID" specified for the IG region. Here, I want to show help text for SKILL_NAME, SKILL_LEVEL columns. So, I have selected these columns in DA.

Name: Show IG Help Text
When:

Event: Get Focus
Selection Type: Column(s)
Region: Skills
Column(s): SKILL_NAME,SKILL_LEVEL

True Action-1:

Action: Execute JavaScript Code
Settings:

Code: As shown below

JavaScript Code:

apex.item("P33_COL_STATIC_ID").setValue($(this.triggeringElement).attr("id"));
apex.item("P33_REG_STATIC_ID").setValue("skills");
apex.item("P33_ITEM_NAME").setValue("");

True Action-2:

Action: Refresh
Affected Elements:

Selection Type: Region
Region: Help

One point to note here regarding DA "True" actions. In older versions of APEX, we need to manually insert hidden item values into APEX Session State before refreshing the "Help" region. In such cases, create another true action in-between above two "true" actions.

Action: Execute Server-side Code (Or "Execute PL/SQL Code" in older versions)
Settings:

Code: NULL;
Items to Submit: P33_ITEM_NAME,P33_REG_STATIC_ID,P33_COL_STATIC_ID

That's it and here is the link to demo.

As you can see in the demo, I have not tested this with all item types. This approach may not work with all item types, but you can use similar approach for other item types as-well, if not exactly the same approach.

Few other ideas to implement this functionality

During the page load or right after page load, make an AJAX call and get all required help text from the server and store it as JSON object on client side. Use help text from JSON object every time when item focus changes.
Or When user focuses on an item, then make an AJAX call and get item help text. Store the item help text on client side and use it from client side, if user focuses same item next time.
Explore other fancy options to display help text instead of classic report.

And when you are here and if you don't know about creating help page in APEX, you may refer this documentation.

Thank you.

Interactive Grid - Displaying Master data in Detail IG Header

2020-11-01T16:08:00.000+05:30

Recently one of the users has asked a question "Would it be possible to display selected master row data in detail interactive grid region header?". Yes, why not, it's indeed a reasonable request. However, during implementation, I have found it is little tricky to implement a solution which works in all cases and finally I have managed to get it done. I am sharing this here as it may help other APEX developers.

When user selects any row using the row selector check box or when user just clicks on any row, then selectionchange event is fired. This event is fired, irrespective of whether IG is in edit mode or not. So, we can use this event to get data from selected master record.

For demo purpose, consider Interactive Grid (IG) on Department table as master and IG on Employees table as detail. And assume requirement is to display selected/current department name in Employees IG region title.

To implement this, let's create Dynamic Action (D.A.) as below

Create D.A. and give it a proper name e.g. "Sel Change - Set EMP Title"
In "When" Section, select

Event: Selection Change [Interactive Grid]
Selection Type: Region
Region: Departments (In the demo, Master IG region name is "Departments")

True Action

Action: Execute JavaScript Code
Code: Refer below code

try {
    // Static id specfied for Departments IG: dept
    var gridView = apex.region("dept").widget().interactiveGrid("getCurrentView");
    var records = gridView.getSelectedRecords();
    // other option to get selected rows is as below from event data
    // var records = this.data.selectedRecords;
    // but, not using as this approach as this event was not passing any data before APEX 18.2, 
    // other reason is, in 19.2 (and probably in 19.1 and 18.2), above code is not returning any data when IG is in edit mode
    var model = gridView.model;
    var currDept = model.getValue(records[0], "DNAME");
    if (records.length === 1 && currDept !== "") {
        // Using standard region template for Employees IG
        // Static id specfied for Employees IG: emp
        // Department Name field is simple text field. For LOV based items, you may want to use currDept.d syntax to get display value     
        $("#emp_heading").html(currDept + " Employees");
    }
    else
        $("#emp_heading").html("Employees");

} catch (error) {
    // when "Select First Row" is disabled, then after page load, this code gives JavaScript error
    // we can get error when user un checks all the rows
    // these cases, just set generic title 
    $("#emp_heading").html("Employees");
}

This works in most of the cases. But, consider user is changing department name and moving to location field or adding new department, then, in these cases, users will not see latest department name in detail IG title immediately. So, we need another D.A. to listen to department name changes and update the Employees IG region title.

So, let's create another D.A. as below

Create D.A. and give it a proper name e.g. "DNAME Change - Set EMP Title"
In "When" Section, select

Event: Change
Selection Type: Column(s)
Region: Departments (In the demo, Master IG region name is "Departments")
Column(s): DNAME (As we are using DNAME in Details IG title, we should listen changes on this column)

True Action

Action: Execute JavaScript Code
Code: Refer below code

var gridView = apex.region("emp").widget().interactiveGrid("getCurrentView");
var records = gridView.getSelectedRecords();
var currDept = $v(this.triggeringElement);
// set details IG title only when deparment name is not empty and only one row is selected
if (currDept !== "" && records.length === 1)
  $("#emp_heading").html(currDept + " Employees");
else
  $("#emp_heading").html("Employees");

That's it and here is the link for demo.

Thank you.

Generating ZIP files in Oracle APEX

2020-10-26T14:01:00.001+05:30

APEX_ZIP API was introduced in APEX version 5.0. It has made generating zip files from the database easy. Nothing has changed with this API since then. Here is the sample code to generate a zip file using these APIs.

DECLARE
    l_zip_file   BLOB;
BEGIN
    -- zip all the files uploaded to current application "Static Application Files"
    FOR l_file IN(
        SELECT
            filename       file_name
            ,blob_content   file_content
        FROM
            apex_application_files
        WHERE
            flow_id = :APP_ID
    )LOOP
        apex_zip.add_file(
            p_zipped_blob   => l_zip_file
            ,p_file_name     => l_file.file_name
            ,p_content       => l_file.file_content
        );
    END LOOP;

    BEGIN
        apex_zip.finish(p_zipped_blob   => l_zip_file);
    EXCEPTION
        WHEN value_error THEN
    -- if there is no file added so far, then apex_zip.finish raises VALUE_ERROR
    -- do nothing
            NULL;
    END;
END;

You can find similar code in Oracle APEX documentation as-well.

Creating folder or directory inside the zip file

Creating folders inside zip file is easy and strait forward. All you need to do is, prepend 'folder-name/' to file name (to p_file_name parameter). APEX will automatically create a folder for you and it will keep those files under respective folder.

For e.g. let's modify above code to include all images in one folder called "images" inside the zip file.

DECLARE
    l_zip_file   BLOB;
BEGIN
    -- zip all the files uploaded to current application "Static Application Files"
    FOR l_file IN(
        SELECT
            filename       file_name
            ,blob_content   file_content
            ,mime_type
        FROM
            apex_application_files
        WHERE
            flow_id = :APP_ID
    )LOOP
    -- move all images to images folder
    -- all other files will be directly under the zip folder
        apex_zip.add_file(
            p_zipped_blob   => l_zip_file
            ,p_file_name     => case 
                                    when mime_type like 'image%' then 'images/'||l_file.file_name
                                else 
                                    l_file.file_name
                                end
            ,p_content       => l_file.file_content
        );
    END LOOP;

    BEGIN
        apex_zip.finish(p_zipped_blob   => l_zip_file);
    EXCEPTION
        WHEN value_error THEN
    -- if there is no file added so far, then apex_zip.finish raises VALUE_ERROR
    -- do nothing
            NULL;
    END;
END;

Using APEX_UTIL_WRAP

If you are using this functionality in multiple places in your application, then you may have to repeat the code, which looks more or less same, in several places. Then I thought, why can't I create a wrapper functions on top of APEX_ZIP APIs to make developers task further easy. So, I have added new function get_zip to my open source package apex_util_wrap. To generate a zip file, you just need to pass SQL query and you will get a zip file. Sample code below.

DECLARE
    l_sql   VARCHAR2(4000);
    l_zip   BLOB;
BEGIN
  -- important: order of columns should be same as below, 1st column should be file_name and 2nd column should be file_content (BLOB)
    l_sql := q'[select filename file_name, blob_content file_content from apex_application_files where flow_id = v('APP_ID')]';
    l_zip := apex_util_wrap.get_zip(l_sql);
END;

This works well when query does not have any bind variables or when using this function with-in a valid APEX session (i.e. bind variable values can be passed using V function). But, if you want to use it in any scheduler jobs, then you may need to pass bind variable values separately. In most cases, having one bind variable would be sufficient. So, I have added another overloaded function get_zip. This function accepts SQL query and one bind variable value. Sample code below.

DECLARE
    l_sql   VARCHAR2(4000);
    l_zip   BLOB;
BEGIN
  -- important: order of columns should be same as below, 1st column should be file_name and 2nd column should be file_content (BLOB)
    l_sql := q'[select filename file_name, blob_content file_content from apex_application_files where flow_id = :APP_ID]';
    l_zip := apex_util_wrap.get_zip(
        l_sql
        ,:APP_ID
    );
END;

Well, what if you need to pass multiple bind variables to the query? Do we need to use vanilla apex_zip APIs again? May be not. I have added yet another overloaded function get_zip. This function accepts SQL query , array of bind variable names and array of bind variable values. Sample code below.

DECLARE
    l_sql   VARCHAR2(4000);
    l_zip   BLOB;
BEGIN

    l_sql := q'[select filename file_name, blob_content file_content from apex_application_files where flow_id = v('APP_ID') and filename in (:FILE1,:FILE2,:FILE3)]';
    l_zip := apex_util_wrap.get_zip(
        l_sql
        ,apex_t_varchar2(
            'FILE1'
            ,'FILE2'
            ,'FILE3'
        )
        ,apex_t_varchar2(
            'captain.jpg'
            ,'ironman.jpg'
            ,'thor.jpg'
        )
    );
END;

Another frequent use case is, generating zip file and downloading it from APEX. You can do this using apex_zip APIs and sys.wpg_docload.download_file procedure, after printing required HTTP headers etc. To make it simple, I have created another wrapper procedure download_blob in apex_util_wrap package. You can use this procedure to download any BLOB file in APEX.

Sample code to generate and download zip file. You can use put this code in "before header" page process in any APEX page.

DECLARE
    l_sql   VARCHAR2(4000);
    l_zip   BLOB;
BEGIN

    l_sql := q'[select filename file_name, blob_content file_content from apex_application_files where flow_id = v('APP_ID') and filename in (:FILE1,:FILE2,:FILE3)]';
    l_zip := apex_util_wrap.get_zip(
        l_sql
        ,apex_t_varchar2(
            'FILE1'
            ,'FILE2'
            ,'FILE3'
        )
        ,apex_t_varchar2(
            'captain.jpg'
            ,'ironman.jpg'
            ,'thor.jpg'
        )
    );

    apex_util_wrap.download_blob(
        l_zip
        ,'images.zip'
    );
  -- stop APEX Engine
    apex_application.stop_apex_engine;
END;

If you like get_zip function or download_blob procedure, then you can download apex_util_wrap package from here.

Link for demo.

Including Images in Oracle APEX Emails

2020-09-19T21:20:00.003+05:30

In this blogpost, I am going to explain different ways using which we can include images as part of the email content.

CID Attachments

In this approach images are sent as attachment to the email and then images are referred in email content using attached filename. For e.g. if you want to include image "ironman.jpg", then you need to attach this image file to the email. Then you can refer the attached image using below syntax in "img" tag "src" attribute.

<img src="cid:ironman.jpg" alt="ironman"/>

Sample PL/SQL code to use this approach

DECLARE
    l_body        CLOB;
    l_body_html   CLOB;
    l_img_html    CLOB;
    l_id          NUMBER;
BEGIN
-- build html email body
    l_body_html := '<!DOCTYPE HTML>'
                   || '<html>'
                   || '<head>'
                   || '<meta http-equiv="Content-Type" content="text/html; charset=utf-8">'
                   || '<meta name="viewport" content="width=device-width">'
                   || '</head>'
                   || '<body style="background-color: #efefef; line-height: 1.5;">';

-- sending image as CID attachment
-- observe image src attribute src="cid:ironman.jpg", here we are saying email client to get image from email attachments and look for file name ironman.jpg
    l_img_html := '<h1>Below image is sent as CID attachment.</h1><img src="cid:ironman.jpg" alt="ironman"/>';
    l_body_html := l_body_html || l_img_html;
    l_body_html := l_body_html
                   || '</body>'
                   || '</html>';
    l_body := 'Please use HTML enabled email client to view this message.';
    l_id := apex_mail.send(
        p_to          => :P30_EMAIL_TO
        ,p_from        => 'noreply@oracle.com'
        ,p_body        => l_body
        ,p_body_html   => l_body_html
        ,p_subj        => 'Including images as CID attachment'
    );

-- attach image file for CID attachment
-- here I am taking one image from "Static Application Files" uploaded in shared components 
-- filename we are attaching should match with filename specified at img src
-- if filename does not match, then below image will be displayed as normal attachment
    FOR img IN(
        SELECT
            filename
            ,blob_content
            ,mime_type
        FROM
            apex_application_files
        WHERE
            filename = 'ironman.jpg'
            AND flow_id = :APP_ID
    )LOOP
        apex_mail.add_attachment(
            p_mail_id      => l_id
            ,p_attachment   => img.blob_content
            ,p_filename     => img.filename
            ,p_mime_type    => img.mime_type
        );
    END LOOP;
-- optionally push email
    apex_mail.push_queue;
END;

Since images are included as attachments, your email size will grow based on size of images used. This approach works well in most of the desktop clients but may not work well in web-based email clients. In my testing, this approach is working fine with Microsoft Outlook client, but not working as expected with Gmail web client. In Gmail, I can see it as normal attachment, but not as inline image.

Note: If you are using Oracle APEX 21.2 or later, then please refer Inline Attachments for Oracle APEX Emails blogpost.

Inline embedding using base64 encoding

In this approach images are converted to text format using base64 encoding and then they are directly used in "img" tag "src" attribute.

<img src="data:image/png;base64,<base64_image_text>"  alt="ironman"/>

Sample PL/SQL code to use this approach

DECLARE
    l_body         CLOB;
    l_body_html    CLOB;
    l_img_html     CLOB;
    l_img_base64   CLOB;
    l_mime_type    VARCHAR2(255);
    l_img_blob     BLOB;
BEGIN
-- build html email body
    l_body_html := '<!DOCTYPE HTML>'
                   || '<html>'
                   || '<head>'
                   || '<meta http-equiv="Content-Type" content="text/html; charset=utf-8">'
                   || '<meta name="viewport" content="width=device-width">'
                   || '</head>'
                   || '<body style="background-color: #efefef; line-height: 1.5;">';

-- here I am taking one image from "Static Application Files" uploaded in shared components 
-- convert image to base64 encoding
    SELECT
        blob_content
        ,mime_type
    INTO
        l_img_blob
    ,l_mime_type
    FROM
        apex_application_files
    WHERE
        filename = 'ironman.jpg'
        AND flow_id = :APP_ID;

    l_img_base64 := apex_web_service.blob2clobbase64(l_img_blob);

-- sending image as text using base64 encoding   
-- observe image src attribute src="data:image/png;base64,<base64_image_text>" 
    l_img_html := '<h1>Below image is sent as inline image by converting image to text using base64 encoding.</h1><img src="data:'
                  || l_mime_type
                  || ';base64,'
                  || l_img_base64
                  || '" alt="ironman"/>';
    l_body_html := l_body_html || l_img_html;
    l_body_html := l_body_html
                   || '</body>'
                   || '</html>';
    l_body := 'Please use HTML enabled email client to view this message.';
    apex_mail.send(
        p_to          => :P30_EMAIL_TO
        ,p_from        => 'noreply@oracle.com'
        ,p_body        => l_body
        ,p_body_html   => l_body_html
        ,p_subj        => 'Embedding images inline using base64 encoding'
    );

-- optionally push email
    apex_mail.push_queue;
END;

Again, since images are included as part of email content, your email size will grow based on size of images used. In my testing, this approach is working fine with Microsoft Outlook client, but not working at all with Gmail web client. I can only see image alt text in Gmail.

Hosted Images

This is simple approach where images are hosted in a server and those images are referred in email using "img" tag, just like how we refer them in any web-page.

Sample PL/SQL code to use this approach

DECLARE
    l_body        CLOB;
    l_body_html   CLOB;
    l_img_html    CLOB;
BEGIN
-- build html email body
    l_body_html := '<!DOCTYPE HTML>'
                   || '<html>'
                   || '<head>'
                   || '<meta http-equiv="Content-Type" content="text/html; charset=utf-8">'
                   || '<meta name="viewport" content="width=device-width">'
                   || '</head>'
                   || '<body style="background-color: #efefef; line-height: 1.5;">';

-- referring to image hosted in server
-- here I am taking one image from "Static Application Files" uploaded in shared components 
-- filename ironman.jpg
    l_img_html := '<h1>Below image is referred from external server. Size of this email should be small compared to other two approaches.</h1><img src="'
                  || apex_mail.get_instance_url
                  || :app_images
                  || 'ironman.jpg" alt="ironman"/>';
    l_body_html := l_body_html || l_img_html;
    l_body_html := l_body_html
                   || '</body>'
                   || '</html>';
    l_body := 'Please use HTML enabled email client to view this message.';
    apex_mail.send(
        p_to          => :P30_EMAIL_TO
        ,p_from        => 'noreply@oracle.com'
        ,p_body        => l_body
        ,p_body_html   => l_body_html
        ,p_subj        => 'Referring hosted images'
    );
    
-- optionally push email
    apex_mail.push_queue;
END;

When you are using this approach, images are not included as part of your email. So, email size should be much smaller compared to other two approaches. In my testing, this approach is working fine with Microsoft Outlook client as-well as with Gmail web client.

This is probably most popular approach used for including images in email content. For that matter, APEX uses this approach in all email communications that are being sent from apex.oracle.com. You can also find those images in "\email\img" folder under APEX "images" folder. However, one point to note here is, "images should be accessible to email recipients". For e.g. if images are hosted on a server which is behind corporate firewall and if your recipients are outside the firewall, then images will not be displayed in email. I think, this is one of the reasons why you find recommendation to "Avoid images" in Oracle APEX APIs documentation.

In this blogpost, I have tried to provide example PL/SQL code to include images in emails, using different approaches. However, it is not easy to ensure images are always displayed without any issues for all your users, especially when you don't know which email clients your users may use. But an age-old adage says, "A Picture Is Worth a Thousand Words", so sometimes it's worth to put all that effort. 😀

To understand pros and cons of above approaches and compatibility with different email clients, please refer Embedding Images in HTML Email: Have the Rules Changed? blogpost from mailtrap.

Link for Demo

Thank you.

Defining Link for Gantt Chart

2020-08-16T13:02:00.001+05:30

If you have worked with Gantt Chart, you may have noticed that it does not support "Link" feature where as other chart types support it. This is because Gantt chart is based on ojGantt API which does not support link feature, where as other chart types uses ojChart API and ojChart does support "Link" feature. Luckily, Gantt charts supports "selection" feature and we can use this feature to implement (mimic) link feature.

To demonstrate this, let's considered a simple use case "Whenever user clicks on task bar, then we need to take user to 'Edit Task' page".

To implement this, first, we need to enable "selection" feature on Gantt chart. By default, this option is disabled. For this, we need to write below JavaScript code in "Gantt Chart > Attributes > Advanced > JavaScript Initialization Code" section.

Gantt Chart JavaScript Initialization Code

function (options)
{
  options.selectionMode = "single";
  return options;
}

Next, create a hidden page item to store value of TASK_ID. Ensure "Value Protected" option is switched off for this item. In this e.g. item name used is P28_TASK_ID.

Next, we need to capture the click event and we need to mimic the link behavior. For this create a Dynamic Action like below.

D.A. "Handle Gantt Chart Link"

Create DA and give it a proper name e.g. "Handle Gantt Chart Link"
In "When" Section, select

Event: Custom
Custom Event: ojoptionchange
Selection Type: Region
Region: Gantt Chart Region

In "True" section, choose "Execute JavaScript Code" action and put below JavaScript code in "Code" section.

try {
  var data = this.data;
  //check which data is avilable from Gantt Chart
  //console.log(data);
  var lTaskId;
  // check if selection is changed
  if (data.option === "selection") {
    // get current selection
    // This gives column value selected at "Gantt Chart > Series > Column Mapping > Task ID"
    lTaskId = data.value[0];
    // Set Task id to hidden page item and submit the page with EDIT_TASK request
    apex.page.submit({
      request: "EDIT_TASK",
      set: {
        "P28_TASK_ID": lTaskId
      },
      showWait: true
    });
  }
} catch (err) {
  apex.message.clearErrors();
  apex.message.showErrors({
    type: "error",
    location: "page",
    message: "JavaScript Error" + err.message,
    unsafe: false
  });
}

And finally, create a "Branch" at "After Processing" point to navigate to "Edit Task" page. Here we can pass P28_TASK_ID to target page.

Here, I have chosen to submit page instead of redirecting to "Edit Task" page using apex.navigation.redirect. Because,

If the target page needs checksum, and if we use "Submit + Branch" approach, then APEX will take care of checksum automatically. We can't use "Redirect" approach with out valid checksum value. In this example, I have set "Page Access Protection" to "Arguments Must Have Checksum" for "Edit Task" page. We can see this in demo.
In general, generating URLs at client side or hard coding URLs in JavaScript is never a good idea.

However, there is a catch with this approach. If someone uses apex.page.submit with EDIT_TASK as request and sets P28_TASK_ID value to any other value which they are not supposed to view/edit, then they may be able to open "Edit Task" page for the task. We can prevent this by adding server side "Validation" in "Gantt Chart" page. Create validation to ensure current user has privilege to view/edit the task specified in P28_TASK_ID item. We can make this conditional so that this validation only fires when :REQUEST = 'EDIT_TASK' AND :P28_TASK_ID IS NOT NULL

When you are here, you may check out another work around provided by Pierre Yotti for this issue. You can see this in Oracle APEX Forum.

Link for Demo

You may be also interested in my previous blog posts on Gantt Chart topic.

Few tips on Gantt Charts

Few Tips on Gantt Charts - Part 2

Custom Tooltips

Thank you.

Adding custom quick filter to Interactive Grid column

2020-07-26T20:43:00.005+05:30

When we click on IG column header and if filtering is enabled for that column, then depending on column type, quick filtering "options" (distinct values or date filters) will be displayed. In a recent demo, one of the users has asked, is it possible to show any control to quickly filter for rows having no value for the selected column, like "(Blanks)" filter in Excel. First thing that came to my mind was to use COALESCE for nullable columns and display "(Blanks)" text instead of NULL. This works well for string columns, but not for columns with DATE or NUMBER types. Also, if we use this approach, we need to ensure "(Blanks)" text won't be displayed in downloaded files.

We can solve our problem if we

Somehow add new "control" in column header menu
Apply "Is Empty" filter, preferably using JavaScript, when user selects/clicks new "control"

When we click on column header, then APEX appends dynamic DIV element with ID as "<ig_region_id>_ig_column_header_menu" and displays the DIV element below column header. Optionally APEX makes an AJAX call to get distinct column values for quick filtering. This DIV element contains UL element another DIV element with class a-IRR-sortWidget-search, as direct childs. UL element contains buttons for actions like control break, aggregate etc. and child DIV contains "Search" related elements. We can see sample HTML generated for column header menu below.

For point 1, we can add new icon button (as li element) to UL element at the end. We can do this easily using jQuery.

For point 2, Roel Hartman has already shared one technique using which we can apply filters on IG using JavaScript.

And here is the JavaScript function which will add new control button for "Is Empty" filter. Put this JS code in "Page > Function and Global Variable Declaration" section. This will add new icon button (empty cart icon) to the column header menu.

function addNullFilter(pRegionId, pColumnName) {
 // check if pColumnName has filtering enabled or not
 // to do - there should be better way to check this. data.column does not have filter option (as true/false)
 if ($("#" + pRegionId + "_ig_column_header_search").length > 0) {
  // copied li tag from generated HTML and modified title, aria-label and class
  // removed data-option so APEX IG JS will ignore this li click
  liObj = $('<li class="a-IRR-sortWidget-actions-item"><button type="button" class="a-Button a-IRR-button a-IRR-sortWidget-button" title="Empty Filter" aria-label="Empty Filter"><span aria-hidden="true" class="a-Icon fa fa-cart-empty"></span></button></li>');
  // define click behavior for new li object
  $(liObj).click(function() {
   // add is Empty filter for pColumnName
   apex.region(pRegionId).widget().interactiveGrid("addFilter", {
    type: 'column',
    columnType: 'column',
    columnName: pColumnName,
    operator: 'N',
    value: '',
    isCaseSensitive: false
   });
   // hide column header menu
   $("#" + pRegionId + "_ig_column_header_menu").hide();
  });
  // append li object to existing ul element under column header menu
  $("#" + pRegionId + "_ig_column_header_menu ul").append(liObj);
 }
}

And we need to call this function addNullFilter when user clicks on column header. So put below code in "Page > Execute when page load" section.

// emp is IG static id
$("#emp").on("gridactivatecolumnheader", function(event, data) {
 // column header activated, call addNullFilter function with region id and column name
 setTimeout(addNullFilter, 0, $(this).attr("id"), data.column.property);
});

Alternatively, we can create dynamic action with custom event as "gridactivatecolumnheader" for IG region which executes below JS code.

// column header activated, call addNullFilter function with region id and column name
setTimeout(addNullFilter, 0, $(this.triggeringElement).attr("id"), this.data.column.property);

IG - with (Blanks) Quick Filter Option

After publishing this post, I got another idea. Why can't I just add new quick filter option "(Blanks)" along with DISTINCT values or other quick filter options that APEX displays when we click on column header? With little trial & error and modifying above JavaScript function addNullFilter, I was able to achieve this.

Here is the new updated JavaScript function

// function to add (Blanks) filter
function addNullFilter1(pRegionId, pColumnName) {
 // check if pColumnName has filtering enabled or not
 // todo - there should be better way to check this. data.column does not have filter option (as true/false)
 if ($("#" + pRegionId + "_ig_column_header_search").length > 0) {
  // copied anchor tag from generated HTML
  // removed data-return-value for anchor tag and modified class name (appended -copy)
  // if we use same class name, then APEX will trigger its own filtering when we click it
  var nullFilter = $('<a class="a-IRR-sortWidget-row-copy">(Blanks)</a>');
  // append nullFilter object next to search input item
  $("#" + pRegionId + "_ig_column_header_search").after(nullFilter);
  // define click behavior for new anchor object
  $(nullFilter).click(function() {
   event.preventDefault();
   // add is Empty filter for pColumnName
   apex.region(pRegionId).widget().interactiveGrid("addFilter", {
    type: 'column',
    columnType: 'column',
    columnName: pColumnName,
    operator: 'N',
    value: '',
    isCaseSensitive: false
   });
   // remove column header menu
   $("#" + pRegionId + "_ig_column_header_menu").hide();
  });  
 }
}

And we need to call this function addNullFilter1 when user clicks on column header. So put below code in "Page > Execute when page load" section.

// emp1 is IG static id
$("#emp1").on("gridactivatecolumnheader", function(event, data) {
 // column header activated, call addNullFilter function with region id and column name
 setTimeout(addNullFilter1, 0, $(this).attr("id"), data.column.property);
});

However, an additional step is required for this approach. For APEX to ignore this new filter option, I have modified anchor tag class from "a-IRR-sortWidget-row" to "a-IRR-sortWidget-row-copy". So I have copied style definitions as-well for this class and copied them in "Page > CSS > Inline" section.

Based on demo application theme style, here is the CSS code I have copied. You may have to change this CSS code based on "a-IRR-sortWidget-row" class definition for your application theme style.

.a-IRR-sortWidget-row-copy {
    display: block;
    text-decoration: none;
    color: #FFF;
    font-size: 12px;
    line-height: 16px;
    white-space: nowrap;
    overflow: hidden;
    text-overflow: ellipsis;
    padding: 8px;
    -webkit-box-shadow: 0 -1px 0 rgba(0, 0, 0, 0.05) inset;
    box-shadow: none;
    background-clip: border-box;
    cursor: pointer;
}

Link for demo. I have updated demo page to showcase both the approaches discussed here.

What do you think about these approaches? Do you know any better ways of implementing this requirement? Let me know your thoughts.

Thank you.

Oracle APEX - Few Interesting Points

2020-07-12T20:56:00.001+05:30

In this blog post, I am going to touch upon few interesting points I have discovered in my Oracle APEX journey. If you are new to Oracle APEX development, you may find this blogpost useful.

PL/SQL Expression vs SQL Expression

APEX offers us several condition types for server-side conditions. However, there are two condition types which looks almost same. They are "PL/SQL Expression" and "SQL Expression". So, are there any differences between these two condition types or are they actually same?

Here is simple definition for these condition types, in my own words.

PL/SQL Expression: As the name says, any condition which evaluates to TRUE or FALSE and which can be used as condition with IF statement in PL/SQL can be specified as "PL/SQL Expression".
SQL Expression: Similarly, any condition which evaluates to TRUE or FALSE and which can be used in SQL Query can be specified as "SQL Expression".

However, there are few "PL/SQL Expression" conditions that won't work as "SQL Expression" conditions. Few examples are

Just TRUE or FALSE: TRUE or FALSE are BOOLEAN types. We can use them in PL/SQL Expression, but not as SQL Expression condition.
Conditions using APEX_APPLICATION.G_X01 or APEX_APPLICATION.G_F01: G_X01 to G_X10 are variables and G_F01 to G_F50 are arrays declared in APEX_APPLICATION package. These variables and arrays can be used only in PL/SQL Expression condition type. e.g. condition APEX_APPLICATION.G_F01.COUNT > 0

Similarly, there is a "SQL Expression" condition which won't work for "PL/SQL Expression", that is DECODE. e.g. condition DECODE(:P1_GENDER,'M','Male','F','Female','Others') = 'Male'

I generally go with "PL/SQL Expression" condition type, instead of "SQL Expression" and I use CASE statement instead of DECODE. You may refer "Available Conditions" section in documentation for complete list of condition types.

APEX Collections vs Global Temporary Tables

When we have Global Temporary Tables (GTT) in Oracle DB, then why do we need APEX Collections? Can't we just use GTT in APEX? Before answering this question, first we need to understand Oracle APEX Architecture. When a request is sent from browser, then ORDS picks any free DB session lying in the pool or creates new DB session (when all existing sessions are busy) to connect to DB. That means, there is no guarantee that all requests from same APEX Session will be mapped with same DB session. Also APEX Session != DB Session.

As we know, data in GTTs can be preserved either for the whole session (ON COMMIT PRESERVE ROWS), or just for the current transaction (ON COMMIT DELETE ROWS). If we know that given APEX session always maps with a specific DB Session (i.e. there is 1-1 mapping between APEX and DB Session), then using GTTs with ON COMMIT PRESERVE ROWS clause makes sense. However, it's not the case. So, we can't use GTTs in Oracle APEX. If we use GTTs in APEX, then we may get unpredicted results.

When you are here, I recommend reading "Correlating APEX Sessions to Database Sessions" post from Joel Kallman on related topic.

Before Header vs After Header

In APEX, Page Designer > Rendering > Pre-Rendering section, we can use three rendering points. Before Header, After Header and Before Regions. "Before Regions" is clear, but what about "Before Header" and "After Header"? Are there any differences between these two? To understand this, first let’s find out what is "header".

HTML Header

In APEX, in general, below content is rendered into HTML header section.

Menu Toggle Icon
Application Logo
Application Name
Navigation bar list items
Navigation menu items (if menu position defined as "Top")

We can also observe it in the above screenshot. Page debug is another good place to see what is being executed before header, after header and in between these two rendering points.

So, if the process code should affect any of components rendered in HTML header section, then we should define process point as "Before Header". Otherwise, we can define it as "After Header". I generally go with "Before Header" process point and I can't recollect when I have used "After Header" process point.

Display Only Item vs Read Only Item

What is the difference between "Display Only" item and item with read only condition set as "Always"? We may get this question when choosing item type for "Primary Key" field. Because we don't want users to enter or modify primary key value, but in some cases we may want to display it in UI. So, in those cases, which approach can we choose? Which one is better?

Both "Display Only" item (with "Send On Page Submit" as "ON") and "item with read only condition set as Always" behaves exactly the same way. Once the page is loaded, if we try to modify these page item values using JavaScript and submit the page, then we will get "Session state protection violation" error. So, we can use either of these approaches for the primary key field example.

Note: For "Display Only" item, when we set "Send On Page Submit" as "OFF", then that page item value will not be submitted to server. However, when we create "Display Only" item, by default, "Send On Page Submit" is set to "ON". If you are using "Display Only" item for displaying primary key, then you may want to ensure "Send On Page Submit" flag is set to "ON".

Page Item Source vs Item Default

Martin Giffy D'Souza has written an excellent blogpost on this topic. You may read it here

Why APEX_AUTHORIZATION.IS_AUTHORIZED returns BOOLEAN?

There is APEX API APEX_AUTHORIZATION.IS_AUTHORIZED using which we can verify whether current user passes specific authorization scheme or not in PL/SQL code. However, this returns a BOOLEAN value. That means, we can't use it inside SQL query. But, why can't it return Y or N, so we could use it in SQL as-well?

Answer to this question lies in "Validate authorization scheme" option of "Authorization Schemes". By default, "Validate authorization scheme" is set as "Once per session". That is, when "Authorization Scheme" is called for the first time, then APEX executes "Authorization Scheme" logic and stores the result in APEX internal tables (kind of server cache) for current APEX Session. On the subsequent calls to the same authorization scheme, in the same APEX session, APEX will get "Authorization Scheme" result from server cache, instead of executing the code again. This makes perfect sense. Because, in most of the cases, result of "Authorization Scheme" may not change through out the APEX session.

So, if APEX_AUTHORIZATION.IS_AUTHORIZED returns Y or N, then we may use it in SQL queries. If we use it in SQL queries, then we will get "ORA-14551 cannot perform a DML operation inside a query" error, whenever the given "Authorization Scheme" is called for the first time and call is being made via a SQL query.

Well, that's it for now and I hope you may find this blog post interesting, if not useful 😀

Thank you.

Interactive Grid - Conditional Enable/Disable

2020-07-05T20:34:00.010+05:30

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 proper name e.g. "Enable/Disable Commission - 1"
In "When" Section, select

Event: Change
Selection Type: Column(s)
Region: Select IG Region (In the demo, IG region name is "Client Side Condition")
Column(s): JOB (Our condition is based on JOB column. So we have to select JOB column here)

In "Client-side Condition" Section, select

Type: Item / Column = Value
Component Type: Column
Column: JOB
Value: SALESMAN

Our requirement is to enable "Commission" column when JOB is equals to SALESMAN. We have defined this condition as "Client-side Condition" for the DA. Actions defined as "True" action will be fired when "Client-side Condition" evaluates to true. So we need to define "true" action to enable "Commission" column.

DA "Enable/Disable Commission - 1" True Action

True Action

Action: Enable
Affected Elements

Selection Type: Column(s)
Column(s): COMM (COMM is column name for "Commission" in IG query level)

Execution Options

Fire on Initialization: ON (to fire this action when the Interactive Grid row is activated for editing.)

Similarly create two "False" actions. One to disable COMM column (right click on "true" action "Enable" and select "Create Opposite Action") and other to clear COMM column value. Item values will be submitted to server even though item is disabled. So we should clear existing value while disabling the item.

DA "Enable/Disable Commission - 1" False Action "Clear"

That's it. This is straight forward and easy, both in terms of requirement and implementation.

Let's make the requirement more complex. Let's consider the requirement as - Enable "Commission" column, only when JOB is equals to 'SALESMAN' and employee rolls up to BLAKE (i.e. employee belongs to BLAKE organization) and disable "Commission" column in all other cases.

We can implement this requirement in several ways.

Using Dummy Column & Two Dynamic Actions technique

First, lets modify IG SQL query to add new dummy column ENABLE_COMM_FLAG, which will have either Y or N value. When this column value is Y, then we want to enable "Commission" column otherwise we want to disable it.

WITH blake_org AS(
    SELECT
        empno
    FROM
        emp
    START WITH
        ename = 'BLAKE'
    CONNECT BY
        PRIOR empno = mgr
)
SELECT
    emp.empno
    ,emp.ename
    ,emp.job
    ,emp.mgr
    ,emp.hiredate
    ,emp.sal
    ,emp.comm
    ,emp.deptno
    ,CASE
        WHEN blake_org.empno IS NOT NULL
             AND job = 'SALESMAN' THEN
            'Y'
        ELSE
            'N'
    END enable_comm_flag
FROM
    emp
    LEFT OUTER JOIN blake_org
    ON blake_org.empno = emp.empno

Next, create DA "Enable/Disable Commission - 2" to enable/disable "Commission" column based on ENABLE_COMM_FLAG column value. (similar to "Enable/Disable Commission - 1" DA, using ENABLE_COMM_FLAG column instead of JOB column)

DA "Enable/Disable Commission - 2"

This works well for existing rows. When we activate any row for editing, then based on ENABLE_COMM_FLAG value, "Commission" column will be displayed as enabled or disabled. However, when we change any data or add new row, then enable/disable logic will not work well. To cover these cases, we need to add another DA.

DA "Calculate ENABLE_COMM_FLAG"

DA "Calculate ENABLE_COMM_FLAG" True Action

Create another DA and give it a proper name e.g. "Calculate ENABLE_COMM_FLAG"
In "When" section, specify this DA to fire when "JOB,MGR" column values changes.
Define a "True" action

Action: Set Value
Settings

Set Type: PL/SQL Function Body
Code: As below PL/SQL code
Items to Submit: JOB,MGR
Suppress Change Event: OFF (we need APEX to trigger change event so that DA "Enable/Disable Commission - 2" will be invoked)

Affected Elements

Selection Type: Column(s)
Column(s): ENABLE_COMM_FLAG

Execution Options

Fire on Initialization: OFF (Because, when row is initialized, then ENABLE_COMM_FLAG value will be derived at server side at IG SQL query level. For new rows, "Commission" column will be disabled, because ENABLE_COMM_FLAG will be empty. So, there is no need to execute this D.A. when row is activated for editing.)

PL/SQL Code:

DECLARE
    l_enable_comm_flag   VARCHAR2(1);
BEGIN
    SELECT
        'Y' enable_comm_flag
    INTO l_enable_comm_flag
    FROM
        (
            SELECT
                empno
            FROM
                emp
            START WITH
                ename = 'BLAKE'
            CONNECT BY
                PRIOR empno = mgr
        )
    WHERE
        empno = :mgr
        AND :job = 'SALESMAN';
    RETURN l_enable_comm_flag;
EXCEPTION
    WHEN no_data_found THEN
        RETURN 'N';
END;

That's it. However, this approach has a downside. We will end up with additional column ENABLE_COMM_FLAG. We can't change ENABLE_COMM_FLAG column type to "Hidden", because change event is not triggered for "hidden" columns. However, we can uncheck this from displaying in the front-end, so by default users won't see this column.

Other downside of this approach is, if we have multiple columns to enable/disable and if conditions are different for different columns, then we will end up with multiple dummy columns.

Using Custom AJAX Call

In this approach, we don't change the IG query. There won't be any additional columns at IG level. There will be one D.A. which will be fired on change of JOB or MGR columns and that DA executes JavaScript code. In the JavaScript code, we make an AJAX call to check whether to enable or disable COMM column based on JOB or MGR column values.

First, lets define static IDs for IG columns as below (IG Region > Column > Column Attributes > Advanced > Static ID)

Static ID for JOB column emp-job
Static ID for MGR column emp-mgr
Static ID for COMM column emp-comm

When we activate any row for editing, then these Static ID will be used as HTML "id" attribute of IG column items. Using these IDs, we can get JOB and MGR column values and we can enable/disable COMM column.

Next create DA "Enable/Disable Commission - 3" which will fire on change of JOB or MGR columns.

DA "Enable/Disable Commission - 3"

Add a "True" action which executes below JavaScript code

// get column values for current row
let job = apex.item("emp-job").getValue(),
 mgr = apex.item("emp-mgr").getValue();
// define promise object to make AJAX call
let result = apex.server.process("GET_ENABLE_COMM_FLAG", {
 x01: job,
 x02: mgr
});
// AJAX call is success
result.done(function(data) {
 if (data.enable_comm_flag === "Y") {
  // static id specified for column "Commission" is emp-comm
  // enable "Commission" field
  apex.item("emp-comm").enable();
 } else { // disable "Commission" field and clear its value
  apex.item("emp-comm").setValue("");
  apex.item("emp-comm").disable();
 }
});
// AJAX request is failed
result.fail(function(jqXHR, textStatus, errorThrown) {
 // handle error
 apex.message.alert("Error in Page Process GET_ENABLE_COMM_FLAG. Please contact support.");
});

Above JavaScript code calls page process GET_ENABLE_COMM_FLAG to check whether to enable or disable COMM column for given combination of JOB and MGR.

On the same page, We have to define "AJAX Callback" process with name "GET_ENABLE_COMM_FLAG" which executes below PL/SQL Code.

DECLARE
    l_enable_comm_flag   VARCHAR2(1);
    l_job                emp.job%TYPE;
    l_mgr                NUMBER;
BEGIN
    -- read values from JavaScript call
    l_job := apex_application.g_x01;
    l_mgr := apex_application.g_x02;
    -- check if COMM can be enabled for given combination of JOB and MGR
    SELECT
        'Y' enable_comm_flag
    INTO l_enable_comm_flag
    FROM
        (
            SELECT
                empno
            FROM
                emp
            START WITH
                ename = 'BLAKE'
            CONNECT BY
                PRIOR empno = mgr
        )
    WHERE
        empno = l_mgr
        AND l_job = 'SALESMAN';
    -- return result as JSON object
    apex_json.open_object;
    apex_json.write(
        'enable_comm_flag'
        ,'Y'
    );
    apex_json.close_object;
EXCEPTION
    WHEN no_data_found THEN
    -- COMM column should be disabled
    -- return result as JSON object
        apex_json.open_object;
        apex_json.write(
            'enable_comm_flag'
            ,'N'
        );
        apex_json.close_object;
END;

Note: With little JavaScript, users can enable the items and they can enter values. So, ensure to handle (ignore) disabled item values on server side as-well.

There is a bug 30801170 in APEX 19.2/20.1 with respect to "enable/disable" dynamic actions for IG columns. When we perform actions to enable > disable > enable any IG column, then it will not be possible to focus cursor on the enabled column. It will be wiggling continuously.

Work around for Bug 30801170

Thanks to Anthony Rayner for sharing below work around. Please note, these work arounds only works for simple items like text field, textarea and select lists.

Instead of using "enable" dynamic action:

Change DA action to 'Execute JavaScript Code', with the following code

  this.affectedElements
    .prop('readonly', false )
    .removeClass('apex_disabled');

Ensure "Fire on Initialization" still "ON" and "Affected Elements" still has column "COMM"

Instead of using "disable" dynamic action:

Change DA action to 'Execute JavaScript Code', with the following code

  this.affectedElements
    .prop('readonly', true)
    .addClass('apex_disabled');

Ensure "Fire on Initialization" still "ON" and "Affected Elements" still has column "COMM"

Similarly in "Using Custom AJAX Call" approach

replace below code

apex.item("emp-comm").enable();

with

$("#emp-comm").prop('readonly', false).removeClass('apex_disabled');

and replace below code

apex.item("emp-comm").disable();

with

$("#emp-comm").prop('readonly', true).addClass('apex_disabled');

Link for demo.

Do you know any other approaches to handle these cases? Do you have any requirements that can't be achieved using approaches discussed here? Let me know your thoughts using comments section below.

Thank you.

Oracle APEX Gantt Charts - Custom Tooltips

2020-06-20T19:17:00.001+05:30

For showing tooltips on Gantt Charts, just go to "Gantt Chart > Attributes > Tooltip" and select "Yes" for "Show" option. However, this will show standard tooltip and there is no option for customization. In APEX ver. 20.1, there are few more granular options to control on data we want to display in the tooltip. However, still we need to choose from existing options.

Luckily, we can implement custom tooltips in relatively easy way.

If you are new to Gantt charts, I recommend you to read Few tips on Gantt Charts blogpost first.

Gantt Chart Options

First, we need to specify JavaScript function name, which will return a tooltip element, in the Gantt chart options. In below code "showCustomTooltip" is JavaScript function which return the tooltip element.

function (options)
{
  options.tooltip = {"renderer":showCustomTooltip};
  // with older APEX (JET) versions use below code
  // options.tooltip = showCustomTooltip;
  return options;
}

We need to keep this code in "Gantt Chart > Attributes > Advanced > JavaScript Initialization Code" section. Now, whenever we hover mouse on any task bar, then this JS function will be invoked.

"dataContext" Object

By default, "dataContext" object is passed as input parameter for the tooltip function. Using this object, we can access current task data and as-well as current row data. For e.g. if we put below code as function definition for "showCustomTooltip" function,

function showCustomTooltip(dataContext)
{
  var taskData = dataContext.data
    ,rowData = dataContext.rowData;  
  console.log(dataContext.data);
  console.log(dataContext.rowData);
  return "Custom Tooltip Test";
}

and then, if we hover mouse on any task, then we will see below output (something similar based on the Gantt chart) in browser console log.

Output for "console.log(dataContext.data);"

end: "2020-06-17T23:59:00"
id: "TK1"
label: "Employee 1 Task"
labelPosition: "none"
start: "2020-06-13T00:00:00"
svgClassName: " "

Output for "console.log(dataContext.rowData);"

id: "EMP1"
label: "employee1@xyz.com"
tasks: [<emp1-task-1>,<emp2-task-2>..]

I also suggest, to try "console.log(dataContext);" and observe all the information available for the tooptip function, especially "dataContext.parentElement" object.

Gantt Chart Series SQL Query

As explained above, "dataContext" Object gives us some information about current task and current row. However, if we want to pass additional information to tooltip function, then we can use "task_name" column (column selected at "Gantt Chart > Series > Column Mapping > Task Name").

For the current example, my query is something like this..

SELECT
    'TK' || task.task_id task_id
    ,'EMP' || emp.emp_id parent_task_id
    ,emp.email_address   email_address
    /* all the data required for tooltip function is stuffed into JSON object */
    ,'{ '
	 || '"task_name": "'||apex_escape.json(task.task_name)||'"'
	 || ',"task_type": "'||apex_escape.json(task.task_type)||'"'
	 || ',"start_date": "'||apex_escape.json(TO_CHAR(task.start_date,'Dy, DD-Mon-YYYY'))||'"'
	 || ',"end_date": "'||apex_escape.json(TO_CHAR(task.end_date,'Dy, DD-Mon-YYYY'))||'"'
	 ||'}' task_name
    ,task.start_date     start_date
    ,task.end_date + 1 - 1 / 1440 end_date
    ,task.task_type
    ,CASE
        WHEN task.task_type = 'LEAVE' THEN
            'demo-f-orange'
    END || ' '
	|| CASE WHEN task.billable_flag = 'N' THEN 'demo-b-red' ELSE NULL END css_class
FROM emp
JOIN blog_gantt_tasks_v task ON task.email_address = emp.email_address

Observe "task_name" column code. Here, we are preparing a JSON object, with all the data that is required in the tooltip function. "task_name" column data is passed as "label" for "dataContext.data" object. i.e. we can access task_name data using "dataContext.data.label" syntax in the tooltip function. If we don't need any additional data in the tooltip function, then we can just use "task.task_name task_name" for task_name column.

Define Template

Next, let's define a HTML template, which we can use to show the tooltip. In this case, I am using a simple HTML table and I am also using template tag to define the template.

<template id="tooltip-template">
  <div id="tooltip-main">
    <table>
      <tr id="emp-name"><th align="center" colspan="2"></th></tr>
      <tr id="task-name"><th align="right">Task Name</th><td></td></tr>
      <tr id="task-type"><th align="right">Task Type</th><td></td></tr>
      <tr id="start-date"><th align="right">Start Date</th><td></td></tr>
      <tr id="end-date"><th align="right">End Date</th><td></td></tr>
    </table>
  </div>
</template>

We can put above template code in "Page > Header and Footer > Footer Text" section.

There is another approach where we don't need to define template and we can dynamically create tooltip template using "document.createElement" JavaScript function. We can observe this approach in JET documentation example.

Tooltip Function

Here is the tooltip function "showCustomTooltip" code. For explanation, please go through the inline comments.

function showCustomTooltip(dataContext)
{
  // get task and row data from dataContext object
  var taskData = dataContext.data
    ,rowData = dataContext.rowData;
  // we are passing custom data required for tooltip as taskData.label and in JSON format
  // convert label text to JSON object
  var taskDataCustom = JSON.parse(taskData.label);
  // get the template element defined in HTML footer section
  var tooltipTemp = document.getElementById("tooltip-template");
  // clone it to make a copy. don't modify tooltipTemp object as it will impact the template defined in HTML footer section.
  var clon = tooltipTemp.content.cloneNode(true);
  // add data to the cloned template
  // observe, we are reading custom data we have passed as JSON object, in series SQL query (column task_name)
  // and using that data to fill tooptip template
  $("tr#emp-name > th",clon).html(rowData.label);
  $("tr#task-name > td",clon).html(taskDataCustom.task_name);
  $("tr#task-type > td",clon).html(taskDataCustom.task_type);
  $("tr#start-date > td",clon).html(taskDataCustom.start_date);
  $("tr#end-date > td",clon).html(taskDataCustom.end_date);
  // finally return the cloned template filled with task data
  return clon;
}

Note: In 20.1, there is new option "Custom Tooltip" under "Gantt Chart > Series > Column Mapping". This looks like what exactly we need, however, I was not able to get this working.

Here is the demo link

Thank you.

Export Application Search Results to CSV file

2020-06-17T10:14:00.003+05:30

Whenever I need to modify any backend object (altering table, modifying view or changing function/procedure specifications etc.), then I search for object usage in APEX using "Application Search". Sometimes, I find several references for the object and I wish if I could just export them into a excel/csv file, so that I can review them at later point. However, there is no "export" functionality available in "Application Search" pop-up page (APEX Builder Application 4000, Page 8000).

So, I have written below JavaScript code to export search results into a CSV file.

(function () {
    // CSV delimiter
    var csvDelimiter = ",";
    var encloseChar = '"';
    // define array to hold search results
	var refArr = [];
    var refPath, refAttr, refPathPrev;
    // add headers
    refArr.push("Reference Path" + csvDelimiter + "Attribute");
    // append search results to array
	$("table.htmldbStandard3").each(function(){
        refPath = $("td#PATH span strong",this).text();
        refAttr = $("td.header:contains(Attribute)",this).next().text();
        // if refPath is null, then use path from previous iteration
        if (refPath)
          refPathPrev = refPath;
        else
          refPath = refPathPrev;
        // push row to array
        if (refAttr)
          refArr.push(encloseChar+refPath+encloseChar+csvDelimiter+encloseChar+refAttr+encloseChar);
    });
    // convert array to string, seperated by new line character
	var refCSVData = refArr.join("\n");
    // create dummy anchor tag for downloading data as CSV
	var downloadLink = document.createElement("a");
	downloadLink.setAttribute("href", "data:text/csv;charset=utf8," + encodeURIComponent(refCSVData));
	downloadLink.setAttribute("download", "search_results.csv");
	document.body.appendChild(downloadLink);
    // trigger click event
	downloadLink.click();
    // remove dummy anchor
	document.body.removeChild(downloadLink);
})();

In the "Application Search" pop-up page, right click anywhere and choose "inspect element" (or similar option). This will open browsers developer toolbar. Next, choose "Console" tab and paste above JS code and run. That's it.

I have been using this technique from long back. If I remember correctly, I have first used it with APEX ver 4.2. I have tested it with APEX version 20.1 and it's working fine. So, it should work with any APEX version between 4.2 and 20.1. It may work in future APEX versions as-well, if nothing gets changed in APEX "Application Search" page.

Note: If you use different separator for CSV file, then you can change "csvDelimiter" variable value accordingly. CSV column values are enclosed with "double quote" and its specified in "encloseChar" variable. You may need to change it, if either reference paths or attribute values have "double quote" in it.

Thank you.

Few Tips on Gantt Charts - Part 2

2020-06-07T19:53:00.001+05:30

In one of my previous blog post, Few tips on Gantt Charts, I have explained how you can create Gantt chart to show employees calendar data. In this blog post, I am going to explain two more tips on the Gantt chart. This is continuation of previous blog post. So, if you have not read it yet, I recommend to read it first and then come here.

Vertical Grid for Minor Axis

If you go to Gantt Chart > Attributes, there you can enable vertical and horizontal grid lines using "Show Vertical Grid" and "Show Horizontal Grid" options. In our example, horizontal grid lines displays a line after every employee row. This looks good. However, vertical grid lines are displayed based on "Major Axis". So if "months" are displayed as scale for major axis, then vertical grid line is displayed after every month. However, it will be helpful if we display vertical grid line after every day (minor axis), especially when there are several employees in the Gantt chart. So how can we display grid lines for minor axis?

We can achieve this using little trick. Just swap the axes definitions, so minor axes displays on the top and major axes is displayed below minor axes. Here is the JS code to do this. You need to keep this code in "Gantt Chart > Attributes > JavaScript Initialization Code" section.

function(options) {
 // number of days displayed in Gantt Window
 var windowDays = $v("P14_GANTT_DAYS");
 // axes options
 var minorAxisOptions = {},
  majorAxisOptions = {};
 // set scale and zoom order for minor and major axes 
 // depending on number of window days
 if (windowDays > 0 && windowDays <= 90) {
  minorAxisOptions = {
   "scale": "days",
   "zoomOrder": ['quarters', 'months', 'weeks', 'days']
  };
  majorAxisOptions = {
   "scale": "months",
   "zoomOrder": ['months', 'weeks', 'days']
  };
 } else if (windowDays > 90 && windowDays <= 270) {
  minorAxisOptions = {
   "scale": "months",
   "zoomOrder": ['quarters', 'months', 'weeks', 'days']
  };
  majorAxisOptions = {
   "scale": "quarters",
   "zoomOrder": ['quarters', 'months', 'weeks', 'days']
  };
 } else if (windowDays > 270 && windowDays <= 730) {
  minorAxisOptions = {
   "scale": "months",
   "zoomOrder": ['quarters', 'months', 'weeks', 'days']
  };
  majorAxisOptions = {
   "scale": "years",
   "zoomOrder": ['years', 'quarters', 'months', 'weeks', 'days']
  };
 } else {
  minorAxisOptions = {
   "scale": "quarters",
   "zoomOrder": ['quarters', 'months', 'weeks', 'days']
  };
  majorAxisOptions = {
   "scale": "years",
   "zoomOrder": ['years', 'quarters', 'months', 'weeks', 'days']
  };
 }
 // to display vertical grind lines for minor axis, just reverse the axes
 // minor axis will display first and then major axis will be displayed
 options.minorAxis = majorAxisOptions;
 options.majorAxis = minorAxisOptions;
 return options;
}

Dynamic Height for Gantt Chart

You can define Gantt Chart height in "Gantt Chart > Attributes > Layout > Height" section. However, you have to specify "height" in pixel. i.e. height of the Gantt chart is static. So, how can you make it dynamic? How can we adjust Gantt chart height automatically based on data?

You can do this with little JS code.

1) As usual, define "Height" in Gantt chart attributes. Here, specify maximum height that should be used for the Gantt chart. In the case of demo, it's been specified as 600 pixel.

2) Create a D.A. which will fire "After Refresh" of the Gantt chart region. Create "True" action with type "Execute JavaScript Code" and put below JS code. If you are refreshing Gantt chart region using AJAX call (Using "Refresh" D.A.), then you have to specify "Event Scope" as "Dynamic" for this dynamic action. In case of demo, when you click "Go" button then page is being submitted. So I have specified "Event Scope" as "Static".

D.A. JavaScript Code:

try {
 // static ID specified for Gantt Chart Region is emp_gantt_chart
 var ganttContainer = document.querySelector("#db_outer").getBoundingClientRect();
 // 70 pixel is for the axes displayed
 var ganttHeight = ganttContainer.height + 70;
 // limit max height for gantt to 600
 ganttHeight = (ganttHeight < 600) ? ganttHeight : 600;
 // container div will have _jet appended to region static id
 $('#emp_gantt_chart_jet').css("height", ganttHeight);
} catch (err) {
 console.log('Error in "Gantt Chart Dynamic Height" D.A.' + err.message);
}

Here is the link to demo.

Thank you.

Oracle APEX Stylish Radio Buttons

2020-05-31T20:15:00.002+05:30

Do you know you can convert your radio buttons into something stylish and fancy almost declaratively in Oracle APEX? If you are not aware about this, then you may find this blog post useful.

Pill Buttons

You can easily convert radio buttons into pill buttons using "Template Options". Select Radio button item, and in item properties, go to "Appearance > Template Options > Advanced". Look for option "Item Group Display" and choose the option "Display as Pill Button". That's it. You radio buttons will be converted as cool "Pill" buttons.

Display Radio Buttons as Icons

You can display icons instead of label text. For this, all you need to do is

In item LOV, write "icon html" as "Display Value". e.g. icon html code

     <span aria-hidden="true" class="fa fa-emoji-big-eyes-smile fa-3x"></span>

In item properties, set "Escape special characters" as "Off"

That's it. With this, icons will be displayed for labels instead of text. However, you will still see the "radio buttons" before the icons. To hide these radio buttons, you can go to template options and you can choose "Item Group Display" as "Display as Pill Button". Then you would only see the icons.

You can make them further stylish with little CSS. For e.g. when user selects a icon, you can change icon color, so it will be easy to identify that it's been selected.

/* Page item name P18_RADIO_ICON, change selected icon color */
div#P18_RADIO_ICON input:checked + label span.fa-emoji-big-eyes-smile::before {
  color: #00b533;
}

Display Radio Buttons as Images

You can use similar technique to display images instead of text.

In item LOV, write "empty span" as "Display Value" with predefined classes. We can use these class names to specify image details etc. e.g. empty span code

     <span class="ironman"></span>

In item properties, set "Escape special characters" as "Off"

Next, you need to write CSS to display images in those empty spans. Sample CSS code.

/* Page item name P18_RADIO_IMAGE  */
/* display empty span as block element and specify width, height etc. */
div#P18_RADIO_IMAGE label span{
  display:inline-block;
  width:90px;
  height:120px;
  background-size: contain;
  background-repeat: no-repeat;
}
/* specify background image */
div#P18_RADIO_IMAGE span.ironman {
  background-image: url(#APP_IMAGES#ironman.jpg);
}
/* add border for selected option */
div#P18_RADIO_IMAGE input:checked + label span{
  border: 1px solid #0076df;
}

Again, radio buttons will be displayed before images. To hide these radio buttons, you can go to template options and you can choose "Item Group Display" as "Display as Pill Button". Then you would only see images.

Here is the link to demo. Hope it is helpful.

Thank you.

Oracle APEX Email Templates - Displaying Tabular Data

2020-05-23T23:17:00.007+05:30

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.

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.

<tbody>

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

</tr>

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

Oracle APEX Email Templates - Conditional Display

2020-05-08T17:07:00.003+05:30

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 ensure you resolve the ticket within 4 hours.</div>

We can do this in different ways.

1) Send values to substitution string conditionally

Add below text to email template

<div>#SLA_TEXT#</div>

Then you can pass SLA_TEXT text value conditionally while calling APEX_MAIL.SEND

PL/SQL Code to Send Email:

DECLARE
    l_sla_text VARCHAR2(100);
BEGIN
    -- display text only for P1 tickets
    l_sla_text := CASE WHEN :P15_PRIORITY = 'P1' THEN 'Note: SLA for P1 tickets is 4 hours. Please ensure you resolve the ticket within 4 hours.' ELSE NULL END;    
    APEX_MAIL.SEND(
            p_to                 => 'firstname.lastname@xyz.com',
            p_template_static_id => 'SIMPLE_EMAIL_TEMPLATE',
            p_placeholders       => '{' ||
            '   "DESCRIPTION":'    || apex_json.stringify(:P15_DESCRIPTION) ||
            '   ,"PRIORITY":'      || apex_json.stringify(:P15_PRIORITY) ||
            '   ,"TICKET_NUMBER":' || apex_json.stringify(:P15_TICKET_NUMBER) ||
            '   ,"SLA_TEXT":'      || apex_json.stringify(l_sla_text) ||
            '}' 
        );
END;

Simple, correct? This works best for simple cases like discussed here. However, if you have complex cases and you want to show/hide large part of email template conditionally, then this approach is not good. You will end up writing lot of HTML code inside PL/SQL again (pre email template approach)

2) Using Cascading Style Sheets (CSS)

Let's continue with our previous example. To achieve the same thing using CSS, you just need to add an additional substitution string, for e.g. SLA_STYLE and change the template code as below

HTML Code in Email Template:

<div #SLA_STYLE!RAW#>Note: SLA for P1 tickets is 4 hours. Please ensure you resolve the ticket within 4 hours.</div>

Here, you don't want APEX to escape any values you pass for SLA_STYLE. So use !RAW filter (it's not a filter though).

Now, when sending emails, pass style definition to SLA_STYLE conditionally.

PL/SQL Code to Send Email:
DECLARE
    l_sla_style VARCHAR2(100);
BEGIN
    -- add style attribute only for those cases where you don't want to display SLA note
    l_sla_style := CASE WHEN :P15_PRIORITY != 'P1' THEN 'style="display:none;"' ELSE NULL END;
    APEX_MAIL.SEND(
            p_to                 => 'firstname.lastname@xyz.com',
            p_template_static_id => 'SIMPLE_EMAIL_TEMPLATE',
            p_placeholders       => '{' ||
            '   "DESCRIPTION":'    || apex_json.stringify(:P15_DESCRIPTION) ||
            '   ,"PRIORITY":'      || apex_json.stringify(:P15_PRIORITY) ||
            '   ,"TICKET_NUMBER":' || apex_json.stringify(:P15_TICKET_NUMBER) ||
            '   ,"SLA_STYLE":'     || apex_json.stringify(l_sla_style) ||
            '}'
        );
END;

This works well. However, when users forward/reply the email, then they may see the hidden HTML as-well in some email clients. I have observed this behavior in Microsoft Outlook, Mozilla Thunderbird email clients. However, it is working fine in Gmail web client. So, before you use this approach, test it with your targeted email clients.

3) Using HTML Comments

Other approach is to comment HTML text conditionally. To use this approach, you need to add two substitution strings, one to start the comment and other to end.

HTML Code in Email Template:

#SLA_COMMENT_BEGIN!RAW#
<div>Note: SLA for P1 tickets is 4 hours. Please ensure you resolve the ticket within 4 hours.</div>
#SLA_COMMENT_END!RAW#

PL/SQL Code to Send Email:

DECLARE
    l_sla_comment_begin VARCHAR2(100);
    l_sla_comment_end   VARCHAR2(100);
BEGIN
    -- display HTML comments only for those cases where you don't want to display SLA note
    IF :P15_PRIORITY != 'P1' THEN
      l_sla_comment_begin := '<!--';
      l_sla_comment_end := '-->';
    END IF;
    APEX_MAIL.SEND (
            p_to                 => 'firstname.lastname@xyz.com',
            p_template_static_id => 'SIMPLE_EMAIL_TEMPLATE',
            p_placeholders       => '{' ||
            '   "DESCRIPTION":'        || apex_json.stringify(:P15_DESCRIPTION) ||
            '   ,"PRIORITY":'          || apex_json.stringify(:P15_PRIORITY) ||
            '   ,"TICKET_NUMBER":'     || apex_json.stringify(:P15_TICKET_NUMBER) ||
            '   ,"SLA_COMMENT_BEGIN":' || apex_json.stringify(l_sla_comment_begin) ||
            '   ,"SLA_COMMENT_END":'   || apex_json.stringify(l_sla_comment_end) ||
            '}' 
        );
END;

This approach is working fine with all the three email clients (Microsoft Outlook, Mozilla Thunderbird & Gmail web client) I have tested.

That's it for now and stay tuned for more posts on Email templates topic.

Thank you.

Update on 10-MAY-2020:

If you are wondering why do you need approach 2 or 3 and where can you use it, then consider below email template.

Here, if the requirement is to show "Previous Dues" section only when previous dues exists, then you can use either approach 2 or 3 to show "Previous Dues" section conditionally. You can't use approach 1 here. Because if you just pass NULL values to "previous dues" substitution strings, then "heading" (Previous Dues) and "labels" (Due Amount, Penalty etc.) would still be displayed and that will not look great!

Oracle APEX Email Templates

2020-05-05T17:42:00.003+05:30

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 template from APEX_MAIL.SEND

Email Subject: Subject of the email. You can use substitution strings here.

Header: Header of the email. You can use substitution strings here. You can also use HTML to format the content.

Body: Main Body of the email. You can use substitution strings here. You can also use HTML to format the content.

Footer: Any footer notes to the email. You can use substitution strings here. You can also use HTML to format the content.

Advanced > HTML Template: By default, APEX uses HTML template which looks like as below

If you want to modify, you can click on "Load Default HTML" and then you can modify the template. You can also completely replace the HTML template with your own HTML. In these cases, you can use #HEADER#, #BODY# and #FOOTER# substitution strings to refer to "Header", "Body" and "Footer" content.

Plain Text Format > Content: If users are using email clients that does not support HTML content, then this content will be displayed. So if you can show your email content in plain text format, then you can write that content here. If it's not possible, then its better to put some meaningful message here, like "Please open this email from email client which supports HTML content". If you store the emails sent from your system, then you can also give link to the email using which user can see full email content online.

Comments: Developer comments for the email template.

Previewing & Using Email Templates:

Once you have created email template, you can preview your email output using APEX_MAIL.PREPARE_TEMPLATE API.

Also, if you go to email template details page, at the bottom, APEX gives you customized "Sample API Usage" code which includes your template static id and all placeholders you have used in the template. (Pretty cool, right?).

To use the template while sending email, just copy this code, replace "some_value" with actual values and replace email_address_of_user with actual email address and then you are good to go.

You may also refer APEX_MAIL.SEND for other supported parameters while using email templates.

Points to Note:

Sample Templates: There are few sample templates provided by APEX Team. You can find them on the right-side of "Template Details" page. Click on the sample template name to load the sample template and then you can modify it as per your requirement.

Use # with caution: For e.g. if you have below html code in your template, then #000000;color:# will be considered as substitution string and it will be shown in "Sample API Usage".

As alternative, you can use RGB syntax or color names for specifying colors. Or you can also define your CSS in "Advanced > HTML Template" section. However, you can skip these invalid substitution strings while calling APEX_MAIL.SEND and it does not throw any error and it will not have any impact.

You may refer https://www.campaignmonitor.com/css/ for CSS support in email clients.

Dynamic HTML using substitution Strings: By default, all HTML data is escaped from substitution string values. For e.g. If you pass '<span style="color:red;">data here</span>' for a substitution string, then user will get email with "span" tags. In these cases, if you want HTML to get parsed and show the text in red color, then you can use #SUBSTITUTION_STRING_NAME!RAW# in the email template.

Using Built-in Substitution Strings: Built-in Substitution Strings that support template syntax would also work in email templates. However, looks like not all of them work, but only few. For e.g. APP_IMAGES, IMAGE_PREFIX works in email templates. You no need to pass values for these substitution strings, APEX will do it for you.

Process type "Send E-Mail": This process does not support sending emails using email templates as of APEX version 20.1.

That's it for now and stay tuned for more posts on Email templates topic.

Thank you.

If you like watching videos over reading, you can watch the video here which covers several points on email templates topic.