APEX, How-To, Open Source, Oracle, Plugins

Lazy loading in the Select2 APEX plugin

Date: January 3, 2015Author: Nick Buytaert

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:

declare
  l_sql varchar2(4000);
begin
  l_sql := '
    select ename d, empno r
    from emp
    where lower(ename) like ''%' || lower(apex_application.g_x01) || '%''
    order by ename
  ';

  apex_util.json_from_sql(l_sql);
end;

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.

13 thoughts on “Lazy loading in the Select2 APEX plugin”

Add Comment

rimblas says:

January 3, 2015 at 19:21
Very nice enhancement Nick, thank you! Just recently I used the Select2 jQuery plugin (stand alone not the APEX plugin of course) on a tabular form and I also used apex_util.json_from_sql However, because I’m in 12c and to avoid SQL injection I used this construct for my In-Demand process:
```
declare
  l_sql varchar2(4000);
begin
  l_sql := 'with function param return varchar2 is begin return apex_application.g_x01; end;
    select ename d, empno r
    from emp
    where lower(ename) like ''%'' || lower(param() || ''%''
    order by ename
  ';
 
  apex_util.json_from_sql(l_sql);
end;
```
For those not in 12c, you can still create a function wrapper for apex_application.g_x01 create a stand alone function (preferably as part of a package) to return the g_x01 value.

By using this technique you get better performance because the SQL can now be re-used, but more important, there’s zero chance for SQL Injection.

LikeLiked by 2 people
1. Nick Buytaert says:
  
  January 4, 2015 at 19:53
  
  Thanks for the heads up, Jorge. SQL injection is a thread indeed. How does a PL/SQL subprogram in the query’s WITH clause help with avoiding SQL injection?
  
  LikeLike
  1. rimblas says:
    
    January 4, 2015 at 21:28
    It stops SQL Injection because you never actually concatenate the value, you just refer to it as a bind variable in the SQL.
    If you search for KING, the concatenated SQL becomes:
    
    select ename d, empno r from emp where lower(ename) like '%king% order by ename
    
    But if instead of KING you use a single quote the whole SQL becomes invalid. You could then start testing with inserting other values. For example imagine that instead of KING I use this cryptic string: X’ or exists (select 1 from user_tables where table_name like ‘DEPT%’) or ‘X%’ = ‘
    Now the SQL becomes:
    
    select ename d, empno r from emp where lower(ename) like '%X' or exists (select 1 from user_tables where table_name like 'DEPT%') or 'X%' = '%' order by ename
    
    A malicious, or curious, user can start testing for names of tables in your schema (or whatever they want). If the rows come back the table exists, if nothing comes back it doesn’t. Obscure, yes, but SQL Injection is about patience. Plus there are some known things and constructs to try for to make this easier on the attacker.
    
    But what I like the most about using the function is that the SQL effectively becomes
    
    select ename d, empno r from emp where lower(ename) like '%' || :BIND || '%' order by ename
    
    An no matter what someone types in they are only searching what we allow them, which is the contents of emp.ename
    PLUS the huge benefit that every single search now uses exactly same same SQL and can be re-used. This is what allows Oracle databases to scale so well.
    
    With the other method, every search was a unique SQL:
    select * from emp where ename like ‘%K%
    select * from emp where ename like ‘%KI%
    select * from emp where ename like ‘%KIN%
    select * from emp where ename like ‘%KING%
    Instead of one:
    select * from emp where ename like :BIND
    
    Even if I was not in 12c, but was going to use json_from_sql, I would create a function to return the value of apex_application.g_x01
    This is needed because SQL cannot “see” variables from PL/SQL so we cannot just write:
    select * from emp where ename like apex_application.g_x01
    If we could this would all be a little easier.
    
    Hope this all makes sense.
    Again, awesome plugin and great enhancement! I’ll be taking it for a spin tomorrow.
    -Jorge
    
    LikeLiked by 1 person
Jeffrey Kemp says:

January 4, 2015 at 07:27

Can’t wait to try this out when I get back to work – we used the select2 plugin quite a bit but we’ve had some performance issues with large lists.

LikeLike
1. Nick Buytaert says:
  
  January 4, 2015 at 20:11
  
  Let me know if it fixed your performance issues. I haven’t been able to test out the lazy loading solution in a real-life situation. Feedback is always welcome.
  
  LikeLike
  1. Carlos Pereira says:
    
    January 6, 2015 at 15:29
    
    Hi Nick,
    
    Let me thank you for the excellent plugin and the addition of Lazy loading is fabulous.
    
    Regarding big lists, in my case I had to limit the number of results returned by the ajax call back. I set the number of rows returned to 25 and solved my performance problems.
    
    As future enhancement would be great if you could add the possibility of using pagination, getting N rows per call. If the user gets to the end of the list get the next block of rows.
    
    LikeLike
    1. Nick Buytaert says:
      
      January 9, 2015 at 08:27
      
      Hey Carlos,
      
      That would be a great addition indeed. The Select2 jQuery plugin supports this, so it wouldn’t be too difficult to add it to the APEX plugin. I’ll see what I can do as time permits.
      
      Thanks,
      Nick
      
      LikeLike
Pingback: 12c WITH inline PL/SQL, json_from_sql, Bind Variables and AJAX | A little bit of everything
fmenne says:

January 9, 2015 at 13:31

Hi,

implemented te complete lazy loading in the plugin. SQL query is taken from apex
definitions so security is ok. Some optimitizations still have to be done ….

this is not the place to paste a lot of source code

Modified by Nick Buytaert.

LikeLike
1. Nick Buytaert says:
  
  January 9, 2015 at 15:33
  
  Can you please create a pull request on GitHub? Reading a lot of source code in this comments section isn’t really pleasant. Your code looks pretty interesting though. It can make things a lot more straightforward.
  
  https://github.com/nbuytaert1/apex-select2
  
  LikeLike
2. rimblas says:
  
  January 9, 2015 at 16:09
  Nice solution. But there’s no need to do
  v_search:=’%’||nvl(apex_application.g_x01,”)||’%’;
  
  You just need to specify the correct p_search_type to apex_plugin_util.get_data
  I think p_search_type should just be derived from the existing “Search Logic” attribute (#8). If you see the code for apex_plugin_util there’s an appropriate constant for the desired behavior.
```
c_search_contains_case    constant varchar2(20) := 'CONTAINS_CASE';   -- uses INSTR
c_search_contains_ignore  constant varchar2(20) := 'CONTAINS_IGNORE'; -- uses INSTR with UPPER
c_search_exact_case       constant varchar2(20) := 'EXACT_CASE';      -- uses LIKE value%
c_search_exact_ignore     constant varchar2(20) := 'EXACT_IGNORE';    -- uses LIKE VALUE% with UPPER
c_search_like_case        constant varchar2(20) := 'LIKE_CASE';       -- uses LIKE %value%
c_search_like_ignore      constant varchar2(20) := 'LIKE_IGNORE';     -- uses LIKE %VALUE% with UPPER
c_search_lookup           constant varchar2(20) := 'LOOKUP';          -- uses = value
```
  I think we can move further discussions to GitHub. ;)
  
  LikeLike
  1. Frank Menne says:
    
    January 9, 2015 at 16:13
    
    Hi Rimblas,
    
    first I used “c_search_contains_ignore”. That works for any search string except NULL.
    I wanted to display the first 100 Items nevertheless wether the user already entered a search criteria or not. But if you place “NULL” ,”%”, “” into the search string it just returns nothing
    (unwrapped apex_plugin_util package and checked source also….).
    
    LIKE works. And might also be more performant .. ;-)
    
    //
    Frank
    
    LikeLike
Pingback: Improved the Select2 lazy loading functionality | APEXPLAINED

Comments are closed.

	Oracle APEX 系列文章10：O… on Chunked multi-file upload with…
	Badge List in Oracle… on Creating a badge list query in…
	APEX-poznámky… on Recover a deleted APEX page or…
	tienthanhpham on HTML5 camera integration in Or…
	Dirk on Version 3.0 of the Select2 APE…

1. Create a Select2 page item

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

Some pointers

Share this:

Related

13 thoughts on “Lazy loading in the Select2 APEX plugin”