Tuesday, 18 April 2017

Oracle Apex Plug-in for restricting shuttle choices

The Oracle Apex shuttle item is a neat way to select a number of values. It is however not so user friendly when the list of choices is very long. In this case it is useful to be able to limit the list of choices.
When you implement the limitation in the LOV-query of the item, the limitation will also be applied to the selected values. Usually this is not the desired behavior.
This behavior is avoided when the restricting of the left shuttle pane using JavaScript. I have created a Dynamic Action plug-in to do this. The choices in the left shuttle pane are evaluated case insensitive against the content of the search item. If the search item contains more than one string ( separated by spaces) all the strings should occur in the shuttle value. The search string 'INVOICE 2013' returns only values that contain 'INVOICE' (independent of case) and '2013'.

The plugin attributes are the shuttle item and the select item. The plugin needs to know the select item to disable submit when enter is pressed.
Furthermore the select all button is disabled once a selection is applied because this button copies everything from left to right irrespective of visibility. Once no selection is applied the button is activated again.

You can see the plug-in in action on:


As usually you can find the plugin on apex.world

Update 22-04-2017: A new version has been released with support for Safari and IE. Also the triggering item is not anymore restricted to the search item. 

Update 22-06-2017: In a new blogpost I describe a way to have direct filtering after each keystroke: https://dickdral.blogspot.nl/2017/07/restricting-shuttle-choices-even-faster.html

Update 22-07-2017: A new version 1.2 has been released with a bug fix.This bug prevented the plug-in form functioning. 

Happy apexing


Anonymous said...

I've tried your plug in but so far I have not managed to get it to work. I've created a static content page with two items, a text item named :P50_ZOEK and a shuttle item named :P50_SHUTTLE.
I've created a dynamic action on :P50_ZOEK with event 'Change' and event scope 'Static'. For the true action I chose 'Restriced Shuttle Values (Plugin)', search item :P50_ZOEK and affected item :P50_SHUTTLE.
But when I enter text in the :P50_ZOEK item and press enter nothing happens.
What am I missing?
Thank you,

Dick Dral said...

Hi Jolande,

would you be able to reproduce this on apex.oracle com.
Then I can have a look at.


Dick Dral said...

Hi Jolande,

You could try two more things:
- I think I may have disabled the Enter key, because sometimes it causes the page to be submitted. So if you could using the Tab key?
- In a more recent post I described a way to have the shuttle react on individual key strokes so the user gets immediate response. You might try that.


Anonymous said...


I am having the same issue. I have set up an example using your key release example. However, nothing happens when I type in the search item. I feel like there is one step I'm missing. Have you posted a complete setup anywhere that I can look at?

I will try to put up an example on apex.oracle.com

Thank you

Anonymous said...


I have uploaded an example to apex.oracle.com (page 5).

workspace TESTBRETT
user: demo
pw: demo

Thank you,

Dick Dral said...

Hi Jolande and Brett,

I found an error in the JavaScript code of the plugin. This was the cause of the behavior you observed.
You can find the new version on apex.world.


PS Brett, I used your application to test the new version, so you can see it working there

Anonymous said...


I tried importing you Plugin into my Apex app, but it always fails in line 141 of the sql code

set verify on feedback on define on

prompt ...done

74656D293B0A202020202020202020202020636F6E736F6C652E6C6F67282752; -- Always fails execution in this line

wwv_flow_api.g_varchar2_table(22) := '657374726963742053687574746C652056616C7565733A2073687574746C655F6974656D3D272C2073687574746C655F6974656D293B0A20202020202020207D0A2020202020202020617065785F72657374726963745F73687574746C655F76616C7565';

wwv_flow_api.g_varchar2_table(23) := '732E72657374726963745F76616C756573287365617263685F6974656D2C73687574746C655F6974656D293B0A202020207D0A7D3B0A';



I am new to Apex, I don't really get how the exportfiles are processed right now.



Anonymous said...

@ Timo,

You need to go to Export/Import -> Import -> Plug-in. Then select the sql file.

For others,

You need to remove the search component from the Cascaded LOV parent item for the Affected Shuttle.
Also, the dynamic action should be defined on Key Release of the search component.
Then select Action as "Restrict Shuttle Values"