Using Drag’n’Drop in APEX with jQuery

Mouse gestures like Drag’n’Drop are an easy and widely spread way to realize an “add to list” or reorder functionality. In APEX this can be implemented easily with jQuery UI as we already explained at the “APEX Connect” in Düsseldorf last week. However, we only explained their frontend implementation and did not explain, how to connect these changes with the database to persist the user actions. So this is, what we are going to do within this post. And don’t mind if you did not attend our talk at “APEX Connect”, be will explain all prerequisites beforehand. (If you just want to see the example, have a look at our workspace)

Create the table

As we mentioned our post builds upon our conference talk. Our example there was an email inbox. Because of this, we first need to create a table EMAILS using this SQL:

CREATE TABLE "EMAILS" (
  "NR" NUMBER(38,0) NOT NULL ENABLE,
  "FROM_USER" VARCHAR2(50),
  "TO_USER" VARCHAR2(50),
  "SUBJECT" VARCHAR2(200),
  "TIME_SENT" TIMESTAMP (6), 
  "TIME_RECEIVED" TIMESTAMP (6), 
  "CONTENT" VARCHAR2(1000),
  CONSTRAINT "EMAILS_PK" 
  PRIMARY KEY ("NR") USING INDEX ENABLE 
)

Create a page and embed jQuery files

We want to use the droppable feature of jqueryUI. Therefore you will need to download if from: https://jqueryui.com/download/ and upload it on your server. After this you can create a new Page and embed the uploaded file.

Now we can create a new Report Region displaying our emails. Therefore use this SQL:

select
  "NR"  id,
  "SUBJECT" subject,
  "FROM_USER" from_user,
  "CONTENT" content
from EMAILS

Create the templates

We want to allow users to delete mails from their inbox just by dropping them in their recycle bin. Therefore use the droppable feature of jQuery UI. As we can see in the examples (https://jqueryui.com/droppable/) the feature requires the usage of the HTML <ul>  and <li>  tags.  This is why we will need to create a custom template for our report.

First we create a template based on the Report Region Class which we call sortableListRegion :

After this, we also need to create the content for the #BODY# , so we create the custom Template emailListItem :

Now we still need to apply the emailList Region template to our created Report.

Furthermore, add some CSS to our inbox-page.

li.emailListItem { 
    padding: 0.3em 1em; 
    background-color: #eee; margin-left: -1em; 
} 

span.from_user { 
    display: inline-block; 
    min-width: 120px; 
}

Create the droppable effect

Now we can see our inbox, but cannot interact with it – yet. First, add a static content region which will serve as recycle bin. Don’t forget to give it an ID to reference it later. In the example it’s called P20_TRASH.

Now let’s add some JavaScript:

$("#P20_TRASH").droppable({
  drop: function() {
    $( ".ui-sortable-helper").toggle("highlight");
  }
});

Ok, what is this code doing: First, we create a jQuery Object using the ID we gave our recycle bin. Then we apply the droppable widget to the element and pass an options object with it. This object only contains one attribute, which assigns a callback function to the drop event. This callback simply toggles the visibility of the helper element using some kind of highlight effect.

Add Persistence

Now we need to add persistence, so that the next time the user opens his inbox the removed emails will still be gone. Therefore we first create a hidden page item P20_REMOVE_MAIL_ID which will act as a buffer storage.

Then we modify the just written JavaScript by removing the options object:

$( "#P20_TRASH" ).droppable();

Instead of assigning a static callback function we will listen to the drop event within a Dynamic Action. We could do all we are going to do in pure JavaScript here as well, but using a Dynamic Action is way more comfortable.

The mentioned Dynamic Action will listen to the drop event and execute to actions, one JavaScript Code and one PL/SQL Code. The JavaScript Code is:

$(".ui-sortable-helper").toggle("highlight");
$("#P20_REMOVE_MAIL_ID").val(this.browserEvent.srcElement.id);

Maybe you recognize, that we used the first statement before already to hide the dropped element. The second statement assigns the id of the dropped element to the application item we just created. It gets the element ID through the Drop event, which is this and its properties browserEvent  which contains a reference to the source Element and its ID.

Only one part of the puzzle is left. The PL/SQL Block. This one uses the just assigned value of the application item within its DELETE Statement. Make sure that you add the right ID to the “Page Items to submit” field, otherwise the database will not know, that the JavaScript assigned a new value to this field and therefore fail executing the delete.

That’s it, you can check out the final result at or workspace. We hope this will post will help you to implement cool UI-Designs yourself. You will be able to create different actions such as sortable in the same style, even if you need more than one information. Just use multiple application items and you are good to go.