Sunday, January 26, 2014

It's a drag...

It really used to be a "drag" putting together a set list for my band using my Oracle database of songs we play: the easiest way was to download all the songs to an Excel spreadsheet, manipulate them there, then re-import back into the database.  I tried various techniques within APEX but none was easier than that - until now.  I have just discovered jQuery UI's Sortable interaction, and in a couple of hour was able to build exactly what I'd always needed:
(That is is one of our recent set lists: I never claimed we were cool - we're called The Love Handles after all.)

The full list of songs (minus any already selected) appears in the first list.  Then there are 4 more lists corresponding to sets (we usually have 2 sets, with a 3rd to hold "spares" in case we under-run or get asked to play on.  Occasionally we play 3 sets.) I can now:

  • Add a song to a set by dragging it from the Songs list to the desired Set list
  • Remove a song from a set by dragging it from the Set list back to the Songs list
  • Move songs from one set to another by drag and drop
  • Move a song up and down within a set by drag and drop

It really was incredibly simple to achieve.  First I needed to include the relevant jQuery library into my page:


(This is shipped with APEX but not included in applications by default.)

Then I constructed the HTML for each list as follows:

<ul class="connectedSortable">
<li class="set1"><input type="hidden" name="f01" value="123" />Land of 1000 Dances</li>
<input type="hidden" name="f01" value="0" />


  • Each of the 5 lists has the same class "connectedSortable".  This is what allows us to drag and drop between them.
  • Each list item consists of a hidden item containing the song's ID in the database, and the song title to be displayed.  All the hidden items have name="f01" so that they will all appear in an APEX array g_f01 when the page is submitted. (The class "set1" is only there so that I can colour each list differently.)
  • After each list I put another hidden item also named "f01" with a value of 0.  This acts as a separator between the lists when processing the g_f01 array - if the value is 0 I know that I have reached the end of a list and am starting the next.
  • Originally, I built each list as a report region, with a bespoke region and report template.  But after a while I realised it was simpler to use PL/SQL regions to dynamically render the HTML.

Now for the Javascript that makes it all work:

  $(function() {
    $( ".connectedSortable" ).sortable({
      connectWith: ".connectedSortable"

That's it - just that.

Finally I just had to write some PL/SQL to process the array when the page is submitted:

   l_set_no integer := 0;
   apex_collection.create_or_truncate_collection ('SETLIST_EDITOR');
   for i in 1..apex_application.g_f01.count
      if apex_application.g_f01(i) = 0 then
         -- End of set
         l_set_no := l_set_no + 1;
         -- Song: add to current set
            ( 'SETLIST_EDITOR'
            , l_set_no
            , apex_application.g_f01(i)
      end if;
   end loop;

I'm using a collection here, which I populated on initial page load from the database. I could have worked on the set list table directly, but this allows me to submit the page if needed for other reasons without saving or losing the changes.

You can see the final result on this demo.

I think jQuery is now my second-favourite developer tool (after APEX of course).


APEX Developer In Virgina said...

So how hard would it be to get a copy of the application you did this in?

I have an idea for a client who is doing a small app for his company that would be interested in the idea of drag and drop for their users..

Would it help if I said PLEASE???

APEX Developer In Virgina said...

What would it take to get a copy of the application you built to demo drag and drop in APEX 4.x?

I have a possible future engagement that would be a perfect for what you have done here..

Would saying PLEASE help??

Thank you,

Tony Miller
LuvMuffin Software
Ruckersville, VA

Tony Andrews said...

Hi Tony,

I have created a new standalone version of the demo application that you can download and try out. The original demo was dependent on my tables and packages.

In the existing demo (see link in blog post) there is now a Download button on the top right. This downloads the new standalone demo. Let me know if you have any issues using it.

APEX Developer In Virgina said...

Sorry to be a pain, as soon as I load your sample app and try to run it I am getting an error saying it can not find page 2 of the application. Since there is only page 7 in the application I am a little confused..

Any ideas??

Thank you,

Tony Miller
LuvMuffin Software
Ruckersville, VA

APEX Developer In Virgina said...

never Mind last comment.. I can run page 7 and see exactly what I was looking for....

Thanks again!!

Thank you,

Tony Miller
LuvMuffin Software
Ruckersville, VA

Amir Mesic said...

Hi Tony,

I've tried this on APEX 5 but I can't get it to work. Any suggestions would be appreciated.


Tony Andrews said...

Hi Amir,

I've just gone back to my demo page (link in demo) which is hosted on, and that isn't working either since the upgrade to APEX 5! When I get a chance I will investigate and hopefully fix, then I'll let you know.


Tony Andrews said...


I have fixed my demo page now. The solution was to change the Javascript file reference in the page attributes to:


I have also updated the downloadable app to match.