The following is one approach to re-ordering rows of a standard Oracle APEX classic report. The approach could actually be applied to tables, div’s, ul’s and other structured objects.
Prerequisites:
-
Install Oracle demo tables, if not already installed
-
Alter table DEMO_CUSTOMERS to add a new field DISPLAY_ORDER as NUMBER
-
Add new APEX classic report page based on DEMO_CUSTOMERS
Step 1 – Add CSS – On the report page, navigate to CSS “Inline†section and add the following. Note that this is optional and will only only changes the cursor to a move symbol. If you change the static ID (in step 2) then you will need to change it here as well.
#sortTable tbody tr {
cursor: move;
}
Step 2 – Add a static ID to the report – Navigate to Region -> Classic report region -> Advanced -> Static ID, and add a static ID. This can be any name you like, but it must match what is in Javascript function (step 4) and CSS (step 1).
select
CUSTOMER_ID CUSTOMER_ID,
APEX_ITEM.HIDDEN(1,CUSTOMER_ID) ||
CUST_FIRST_NAME CUST_FIRST_NAME,
CUST_LAST_NAME,
CUST_STREET_ADDRESS1,
CUST_STREET_ADDRESS2,
CUST_CITY,
CUST_STATE
from
DEMO_CUSTOMERS
order by
DISPLAY_ORDER
Note, you will need to change the CUST_FIRST_NAME field to not escape special characters. Navigate to Security -> Escape special characters, and select No.
Step 4 – Include Javascript on report page – On the report page, navigate to “Execute when Page Loads†section and add the following
$(function() {
$( "#sortTable tbody" ).sortable({
stop : function(event,ui) {
var str = $("[name='f01']").serialize();
apex.server.process ( "UPDATE_DISPLAY_ORDER"
, { x01: str
, x02: 'DEMO_CUSTOMERS'
}
, { dataType: 'text'
,success: function(pData){
$('#sortTable_heading span').remove();
$('#sortTable_heading')
.append('<span style="color:green;">' + ' ' + pData + '</span>');
$('#sortTable_heading span').fadeOut(2000);}
})
}
});
$( "#sortTable tbody" ).disableSelection();
});
Step 5 – Include JQuery UI Sortable Javascript – Navigate to User Interface Attributes -> Desktop -> JavaScript -> File URLs, and add the following line. This is included in the APEX 5 library, so only needs to be referenced. If attempting this using APEX 4 you may need to manually include a reference to ui.sortable in the theme header.
/i/libraries/jquery-ui/1.10.4/ui/minified/jquery.ui.sortable.min.js
Step 6 – Add the APEX server process. Note that you may need to adjust the table name and column name used for ordering. Also, in my example this is named UPDATE_DISPLAY_ORDER, which is referenced in the Javascript function. If you change the name you will need to make appropriate adjustments in the script.
Make sure when you create the process that the run point is set at “On Demand: Run this application process when requested by a page process“.
If this report is likely to be used on an public page, then you should additionally edit the process and change the authorization scheme to “No Authorization Required“.
Important note: The name of the process UPDATE_DISPLAY_ORDER must be exactly the same as the name used int the jQuery function, it is case sensitive.
DECLARE
l_vc_arr2 APEX_APPLICATION_GLOBAL.VC_ARR2;
l_seq number := 1;
l_num number;
l_table varchar2(100) := APEX_APPLICATION.g_x02;
BEGIN
/* x01 is in the form "f01=1&f01=5&f01=2.. and so on
convert x01 to an array so we can process each entry.
x02 has the name of the table to update */
l_vc_arr2 := APEX_UTIL.STRING_TO_TABLE(
APEX_APPLICATION.g_x01,'&');
/* Loop through the array and pick out the record keys
Update the display_order field for each
record if it is not already set properly */
FOR z IN 1..l_vc_arr2.count LOOP
l_num := to_number(SUBSTR(l_vc_arr2(z),instr(l_vc_arr2(z),'=')+1));
CASE
WHEN l_table = 'DEMO_CUSTOMERS' THEN
update demo_customers a set display_order = l_seq
where customer_id = l_num
and NVL(display_order,0) != l_seq;
l_seq := l_seq + 1;
ELSE
htp.p('Unknown table name ' || l_table);
return;
END CASE;
END LOOP;
commit;
HTP.p('Re-order successful.');
END;
Testing – Now, run the report page and hover your mouse over one of the report items. The cursor should change to a move symbol. Try dragging a row to a new position. It should move and the words “Re-order successful†will appear after the title and then slowly fad away.
Note: I have created a simple APEX 5 application which you can download and try. You will see need to prepare two of the prerequisites (Install demo tables, alter demo_customers).