A long-requested feature for the Select2 APEX plugin is the ability to lazy load the LOV data. Lazy loading is an AJAX-driven technique that improves page performance by not executing the LOV query until the point at which it is actually needed. This feature can play an important role when dealing with large data sets. Therefore, I am glad to announce that version 2.5 of the Select2 APEX plugin finally includes the lazy loading ability. In this blog post I describe how you can apply lazy loading to a Select2 page item.

1. Create a Select2 page item

I have applied the following settings.

  • Page item: P10_EMPLOYEE
  • Select List Type: Single-value Select List
  • Minimum Input Length: 2
  • Remote Data Process: get_employees
  • List of Values Definition:
select ename, empno
from emp

2. Create an On Demand page (or application) process

  • Right-click on AJAX Callbacks in the Page Processing part of the page and click Create
  • Select the category of the process you wish to create: PL/SQL
  • Make sure the Process Point is: On Demand – Run this process when requested by AJAX
  • Process name: get_employees
  • Type: PL/SQL anonymous block
  • Process Point: On Demand – Run this process when requested by AJAX
  • Process:
  l_sql varchar2(4000);
  l_sql := '
    select ename d, empno r
    from emp
    where lower(ename) like ''%' || lower(apex_application.g_x01) || '%''
    order by ename


Some pointers

  • Lazy loading can be applied to every Select List Type (single-value, multi-value and tagging).
  • It is advisable to enter a value for the Minimum Input Length setting. That way you prevent the complete data set from being returned by the Remote Data Process.
  • The Remote Data Process can be either an application process or an AJAX callback page process.
  • The page item’s LOV definition should contain the same query as in the Remote Data Process, but without the filtering WHERE clause. This is needed to translate return values into display values for any preselected options in session state on page load.
  • The search string entered by the end user is accessible in the Remote Data Process by referring to apex_application.g_x01.
  • The Remote Data Process should return JSON with the following structure:
  "row": [
      "D": "KING",
      "R": 7839
      "D": "MARTIN",
      "R": 7654
  • It’s a good idea to use the APEX_UTIL.JSON_FROM_SQL subprogram as it automatically returns the appropriate JSON structure.
  • Always use the aliases R and D for respectively the return and display column.
  • Watch out for SQL injection in the Remote Data Process. Jorge Rimblas wrote an excellent article on how to avoid SQL injection vulnerability and improve query performance. Read more about it here.

On this page you can download the latest version of the Select2 APEX plugin and see a live demo of the lazy loading feature.