“Substitution Strings, Bind Variables, and APEX Links” oh my.

Have you ever build an APEX link manually and perhaps it looked something like this:


We should talk.

I used to make this mistake and I’ve seen it made too many times. It’s time to address it head on.

In this video, I will show you why there may be a much better way and why you should be careful. Of course, there are other ways of building links dynamically, like using APEX_UTIL.PREPARE_URL which could still be affected. Perhaps a better approach would be to use the “new” (in APEX 5.0) APEX_PAGE.GET_URL

In the video I comment on these links:

TL;DR: Don’t concatenate variables or columns in your SQL

Don’t do this in SQL:



'f?p=' || :APP_ID || ':PAGE:' || :APP_SESSION || '::' || :DEBUG || ':'

You’ll avoid flooding your shared pool memory with unique SQL statements that cannot be re-used.

Picture a Report and Form pages used to edit records. The Report is an Interactive Report and the edit page is Modal. When the modal closes the IR refreshes, but also, the page jumps to the top after the refresh. This happens in APEX If your report is small this is never an issue, but with longer reports, this is pretty annoying to the user.

I wouldn’t be surprised if this gets fixed in a not so distant new version of APEX, but in the meantime, here’s a simple workaround. Oh and if you have any recommendations, improvements, questions, or suggestions I would love to hear about them.

Check out the demo.

I think my friend Gemma Wood first told me about this fix. I couldn’t find the actual code she shared with me, but the basic approach is pretty simple. Save the scroll position of the page before you open the modal page. Restore the scroll position after the refresh.

Here are the more detailed steps or, if you like, watch the video below:

  • Add a JavaScript variable to your page (use the “Function and Global Variable Declaration” field). We’ll use it to remember our scroll position.
var savepos;
  • Add a DA on click of the link used to invoke a modal page. It will save the current scroll position, so call it “Save Scroll Position”
savepos = $(window).scrollTop();
  • Create an “After Refresh” Dynamic Action to restore the postion.

005 – Save screen position after refresh from Jorge Rimblas on Vimeo.

Do you want to add Drag & Drop functionality to your APEX applications?
That’s what this video is all about.

You’ll see me working on the list created in the previous episodes (Part 1, Part 2 and Part 3) and add the necessary JS to implement.

During the video, I follow this companion blog post (rimblas.com/blog/2016/08/implementing-drag-drop-in-your-apex-applications). Bookmark it and use it as a reference in the future.

In this 41 minute video you’ll find:

  • 02:08 to 03:22 — Add the jQuery UI library to the page.
  • 03:23 to 04:40 — Explaining how elements get re-arranged.
  • 04:40 to 08:02 — Make region/report sortable.
  • 08:02 to 16:04 — Identify the elements to sort and explaining how we actually capture the new order.
  • 16:05 to 24:17 — The AJAX call to save changes (plus some debugging), wrapping up.
  • 24:18 to 42:25 — Implementing the same thing on a regular Classic Report.

Part 4: Adding Drag & Drop functionality from Jorge Rimblas on Vimeo.

* Music licensed from Envato’s AudioJungle.

I first learned how to implement Drag & Drop functionality from Doug Gault’s presentation at Kaleidoscope 2010 (yes before it was Kscope) “Replicating NetFlix Queue Drag-and-Drop Functionality with Oracle Application Express/jQuery” (Membership required). I mean, I wasn’t actually there, but the presentation has excellent step by step instructions. My goal is for this post to become a useful quick reference guide (as much for me as others).

With the proliferation of mobile apps and being fully into (probably the end of) Web 2.0, users come to expect this sort of feature more and more. When we implement it in our Oracle Application Express applications, it brings a nice “wow” factor. That said, I don’t think it’s for every scenario, and I would not overuse it. Also, keep in mind, on mobile devices, you may need some extra libraries to fill in for the lack of a mouse.

If you’ve seen some of my videos, Part 1 covers the creation of the template that is mentioned below. In video installment 004, I following these instructions. If you have any questions, hopefully, the video will cover them.

Demo app here.

Structure to Sort “Lines”

You’ll need a parent container that includes the sortable lines.
For a report think <TABLE> as the container and <TR> as the lines.
For a list think <UL as the container and <LI> as the lines.
The lines that are to be sorted require some ID that uniquely identifies them. You can use a real id tag (id="line123") attribute or a dataset attribute like data-id. The dataset approach is more robust as HTML id values cannot start with a number.

We’ll use a markup like the following:

We’ll define a “Named Column” report template to define the UL list. (To see how to do this watch Part 1)

“Before Rows”

“Row Template 1”

  • #TODO#
  • “After Rows”

    We’ll use the following table to hold our data:

    create table app_todos (
        id            number generated by default on null as identity (start with 1) primary key not null
      , display_seq   number        not null
      , todo          varchar2(32)  not null

    The DISPLAY_SEQ is the value we’ll use to sort and to re-arrange after a drag & drop action takes place.
    The following SQL will define our Classic Report with the custom Named Column template. Give the report a Static ID, we’ll use todoRegion

    select id
         , todo
      from app_todos
     order by display_seq

    Include the sortable jQuery UI library

    This library is already part of your standard APEX distribution. Select the correct one for your version of APEX

    APEX 4.2


    APEX 5.0 & 5.1


    Make the report Sortable

    var el = this.affectedElements[0];
        items: 'li'
      , containment: el
      , update: function(event,ui) { updateDisplaySeq(el); }

    Notice this.affectedElements[0] which means you’ll use this within a Dynamic Action and specify your report region as the Affected Element.
    containment (line 4) is optional, but sometimes it’s a helpful option to restrict how far the elements can be dragged. Try it without to see the effect.
    At this point, the report lines can be dragged, but their new order won’t be saved because we have not defined updateDisplaySeq()

    function updateDisplaySeq(pRegionID) {
     var results = $(pRegionID).find("ul.appTodo").sortable('toArray', {attribute: 'data-id'});
     apex.server.process ( "UPDATE_ORDER", 
        success: function( pData ) 
          // apex.jQuery.gritter.add({title: "Todos",text:"New order saved."});
          apex.event.trigger(pRegionID, 'apexrefresh');
        dataType: "text"

    NOTICE Changes made on 9/12/16 :
    I’ve made some small but important changes. Instead of using x01 the code above now uses f01. This also eliminates the need to issue a .toString() on line 2. The next big change is that now, in the PL/SQL side we can loop directly on apex_application.g_f01 and we don’t need to use apex_util.string_to_table. Thank you to Erik Diaz for pointing this out on the comments below.

    The 'toArray' method will return an array with the ID of our lines. Then .toString() makes it easier to work with in our AJAX process.
    By default toArray looks for the element ID, since we’re using data-id we need this option: {attribute: 'data-id'}
    The code for UPDATE_ORDER will follow, but notice line 8. If we were using the old Notification Plugin from Oracle (that uses Gritter) we could generate a Notification to the user that the new order has been saved. You could consider using pNotify instead.
    Finally, line 9 forces an APEX Refresh of the report to ensure all the latest data is in place and fresh.

    AJAX Callback

    This is the AJAX Callback reference in the code above as UPDATE_ORDER. Remember that the AJAX Callback name is case sensitive.
    The following code receives f01 with our array in the new order we want.

      s number;
      for i in 1..apex_application.g_f01.count loop
       s := i * 10;
       update app_todos
          set display_seq = s
        where id = to_number(apex_application.g_f01(i))
          and display_seq != s;
      end loop;
      when OTHERS then


    If you’re using a standard Classic Report (instead of a Named Column template that you can control). You’ll need to add the ID handles to your <tr> rows.
    The following code will find the ID column (it could be a link column). You’ll want to have this snippet in the attributes data-id=#ID#.
    It will extract it from a dataset attribute and add it to the TR tags.
    You will need this code defined as a function because you’ll call it after Refresh to re-insert the ID to the report.
    pRegionID is the Static ID of the report. The first selector for $r may need to be adjusted depending on the theme.

    var fixHelper = function(e, ui) {
                ui.children().each(function() {
          return ui;
    function makeSortable(pRegionID) {
      var $r = $("#report_" + pRegionID);
      var r = $r[0];
      // add ID to TR element so we know the correct position
      $r.find("[headers='ID'] a").each(function(){
            //  This selector should work for Theme 26
            // $(this).parents('.uReportBody .uReport tr').attr('data-id',$(this).data("id"));
      // finally make it sortable
            items: 'tr'
          , containment: r
          , helper: fixHelper
          , update: function(event,ui) { updateDisplaySeq(r); }

    fixHelper is very useful for a table report as it will set the width or the TR being dragged to the original width it had. Said another way, it will maintain the width of the row being dragged. Without it, the row may collapse to the width of its elements. You can see it’s being referenced in the helper parameter as part of the sortable constructor.

    The selector inside updateDisplaySeq (called above) will need to be changed to work with a table. Something like this should work for you:

     var results = $(pRegionID).find("table.t-Report-report").sortable('toArray', {attribute: 'data-id'});

    This video is all about CSS styling.

    You’ll see me working on the list created in the previous episodes (Part 1 and Part 2) and transform it to something a lot more interesting.

    In this 42 minute video you’ll find:

    • 02:30 to 10:20 — Style the list container and setup the structure.
    • 10:21 to 19:00 — Style list elements.
    • 19:01 to 37:20 — Style the controls within a list element including hover states.
    • 37:21 to 38:52 — Using HSL to find colors.
    • 38:53 to 42:25 — A little border, saving CSS with the Theme Roller, and wrap it all up including the before and after.

    Part 3: Adding styling with CSS from Jorge Rimblas on Vimeo.

    Coming next… Drag & Drop!!!

    Oh, one last thing, did you like the new video bumpers? I’m pretty excited about them.
    Music licensed from Envato’s AudioJungle.

    I’m happy to bring you installment Number 2 of this video series. If you missed the first one, you can find it here Part 1.

    We’ll call this the “Dynamic Action” edition because that’s what most of the video deals with.

    In this 39 minute video you’ll find:

    • 0:00 to 5:49 — Fix a bug from part 1.
    • 5:50 to 30:49 — Add ability to toggle the todo checkboxes by just clicking them.
    • 30:50 to 39:00 — Add a “Trash” icon to delete a todo entry.

    Part 2: APEX Classic Report Named Column Templates from Jorge Rimblas on Vimeo.

    At around 13:23 some sort of noise affected the recording. Not sure why. Unfortunately, it did affect the quality of the rest of the recording. I’ll have to look into that and resolve it before recording 003.

    Next: Part 3

    As part of my Kscope16 presentation “Hooked on Classic (Reports)
    Learn tips & techniques to make them sing” (live slides here). I demoed a Classic Report with a custom template (a Named Column template), made to look like a list. I thought it would be useful to show all the steps of how that demo was built.
    The full video is 36 min. however, only the last 16 min are for the building of the template example. When you’re done with this one, watch the follow-up video where I extend the functionality with some useful Dynamic Actions.

    Note: If you only care about the building of the template, skip to min 20 in the video.

    APEX Classic Report Named Column Templates from Jorge Rimblas on Vimeo.

    If you like this format, and you like what you see, please let me know in the comments or twitter.

    Next: Part 2

    You may be familiar with the UNION operator in Oracle SQL, but are aware of the UNION ALL operator?

    Perhaps you will be surprised to hear that more often than not when you write UNION, what you meant to use is UNION ALL.

    The UNION operator is used to combine result sets from multiple SELECT statements into a single result set. However, it eliminates duplicates in process.

    SQL> select 'ONE' col_val from dual
      2  union
      3  select 'ONE' from dual;
    SQL> select 'ONE' col_val from dual
      2  union
      3  select 'TWO' from dual;

    In order for the database to do this the database will need to join the two result sets, sort them, find the duplicates and eliminate them.

    Hey wait a minute, sort the results? Yes, the most efficient way to eliminate duplicates is to order the results and then find the duplicate entries. To make matters worse, if you have a “large” amount of data, this sort operation may not be able to be performed in memory (which would be the most efficient approach).

    UNION ALL on the other hand will combine the result sets as they are. It will return all rows.

    SQL> select 'ONE' col_val from dual
      2  union all
      3  select 'ONE' from dual;
    SQL> select 'ONE' col_val from dual
      2  union all
      3  select 'TWO' from dual;

    There’s clearly a use case for both scenarios. However, I would submit to you, more often than not, what we really want is to combine all the rows.

    Be aware, that when using UNION you’re asking the database to do more work, make sure that’s what you really want.

    I know that if you search for UNION vs UNION ALL you’ll find a gazillion results. But hey, I keep seeing UNION misused, so maybe we need one more blog about it.


    After three and half years at Enkitec, the last 1.5 of those years as Accenture Enkitec Group, it’s now time to make a change. It has been a great ride with a lot of highs, success, and accomplishments. I couldn’t be more thankful. It’s right up there with one of the best decisions I’ve made in my career. I made many new friends and learn from some of the best and brightest people in the industry.
    However, change can be powerful and positive. I’m excited to announce I’ve accepted a position with Insum Solutions. This new adventure starts on February 1st, 2016.

    The decision didn’t come easy, but a move to a company dedicated to APEX, full of APEX Experts (and Oracle Specialists) gets my blood pumping. I’m excited about the new challenges and opportunities. To me, this move is about my interest, excitement, and commitment to Oracle Application Express. #LetsWreckThisTogether !

    I love the Select2 jQuery plugin. It’s a feature rich, good looking, select list replacement. As an APEX developer, it makes sense to use the excellent plugin from Nick Buytaert. Since its announcement, in Aug 2013, you would be hard pressed to find an APEX application where I don’t use it.

    All that said, APEX doesn’t (yet) support plugins on tabular forms. This blog post is about documenting the JavaScript and Dynamic Actions (DA) you could use to use it as part of a tabular form.

    The Quick and Easy Approach

    If your tabular form contains a Select List already, this method is for you. It will work whether you used a Column Type of “Select List” or APEX_ITEM in it’s various forms: apex_item.select_list , apex_item.select_list_from_query or apex_item.select_list_from_query_xl.

    1. You include the Select2 library on the page. (more on this in a moment)
    2. You target your Select Lists with jQuery and run the .select2() method on them. Yup, that’s it.

    Step #2 may look like this:


    or if you have any options to include:

    var options={allowClear: true};

    Check out this demo. (Click on the “Apply Select2” button).

    The button runs a simple DA that runs the code above. Notice that, if you’re using the Universal Theme in APEX5 you may want to remove the select list styling applied by the class “u-TF-item–select”. Use a command like this:

    // Full effect for APEX5

    Let’s talk about #2 above. In order to include the Select2 library on the page, I like to “cheat” whenever possible and have at least one Select2 APEX item plugin on the page. Doing this will include all the JavaScript and CSS that you need to run the .select2() method. That’s exactly how the previous demo page works.

    The (slightly) Messy Approach

    However, if it’s not possible or desirable to include a regular Select2 item on your page, you’ll have to manually add the necessary libraries. This can get a little tricky if say for example you download the Select2 jQuery files, they don’t match the version used by the plugin, and then later you add a Select2 item plugin to the page.  You’ll want to be careful with that.  For this reason, I like to use the same files that the Select2 item plugin is using. That’s also not ideal because you could upgrade the plugin and then again be in conflict with the versions. My preferred approach is to move the file includes of the Select2 APEX plugin to their own procedure. Share that new procedure between the Select2 APEX plugin and my own, simple, “Manual Select2” plugin. You always move all plugin code to a package don’t you? (perhaps that’s a conversation for a different blog post).
    This new “Manual Select2” plugin is what we’ll use to initialize the select lists on a tabular form.

    My new procedure with the includes looks like this:

    -- BEGIN Select2 Helpers.  Used by tabular_select2 and select2_render
    procedure select2_includes(p_plugin in apex_plugin.t_plugin)
        p_name      => 'select2.min',
        p_directory => p_plugin.file_prefix,
        p_version   => null
        p_name      => 'select2-apex',
        p_directory => p_plugin.file_prefix,
        p_version   => null
        p_name      => 'select2',
        p_directory => p_plugin.file_prefix,
        p_version   => null
        p_name      => 'select2-bootstrap',
        p_directory => p_plugin.file_prefix,
        p_version   => null
    end select2_includes;
    -- END Select2 Helpers.

    The original Select2 plugin changes to something like this:

        end if;
        return l_render_result;
      end if;
      if (l_select_list_type = 'MULTI') then
        l_multiselect := 'multiple';
        l_multiselect := '';
      end if;
      if (l_select_list_type = 'TAG' or l_lazy_loading is not null) then

    For the new plugin, we’ll use a Dynamic Action type plugin with an Initialize category. It will have only one custom attribute for the component. The attribute will be used to contain the code that will initialize the select2 items.

    Select2 Manual Plugin

    When we use the plugin, the code “JS Function” can look like this:

    var options={allowClear: true, width: "150px"};

    Or it can be a lot more complicated, for example:

    width: "85px",
    formatResult: format_RR_R,
    formatSelection: format_RR_S,
    dropdownAutoWidth: true, 
    allowClear: true

    As you can probably tell from this code, you’re not limited to using this on tabular forms. With this technique you can use the full power of the Select2 plugin, like for example using the formatResult or formatSelection options that allow you to specify a function that formats how the values are displayed.

    This technique does require some trial an error, but hopefully it gives you a starting point to extend the capabilities of your applications.

    Check out the demo using the Manual Select2 plugin approach.
    Here’s the plugin dynamic_action_plugin_com_enkitec_manual_select2.sql.

    The code code of the Manual Select2 plugin looks like this:

      l_func_prefix        varchar2(20) := 'f_ek_sel2_';
      l_js_code            varchar2(32767) := p_dynamic_action.attribute_01;
      l_onload_code        varchar2(32767);
      -- the javascript_function is required for this type of plugin, but we won't use it.
      l_result.javascript_function := 'null';
      if l_js_code is not null then
        -- add the provided code wrapped as a function using l_func_prefix and the DA id.
        l_js_code := 'function ' || l_func_prefix || p_dynamic_action.id || '(){' || l_js_code || '}';
        apex_javascript.add_inline_code(p_code => l_js_code, p_key => p_dynamic_action.id);
        l_onload_code := l_func_prefix || p_dynamic_action.id || '();';
        apex_javascript.add_onload_code(p_code => l_onload_code);
      end if;
      return l_result;

    It basically takes the code you provide (the code to initialize select2), wraps it with a function and calls it on page load.

    If you provide the code:

    var options={allowClear: true, width: "150px"};

    The function will look something like this (additional formatting added for clarity):

    function f_ek_sel2_13421097715712641729() {
      var options={allowClear: true, width: "150px"};


    UPDATE Feb 7, 2016: I’ve added a new fully working example implementing Lazy Loading.

    UPDATE Oct 7, 2016: Github Repo with the App https://github.com/rimblas/apex-demo-app