Thursday, February 16, 2017

Comma separated search and search with checkboxes in Oracle APEX

When you have a classic report in Oracle Application Express (APEX) and want to make it searchable you typically add a Text Item in the region, enable Submit on Enter and add a WHERE clause to your SQL statement.

Here’s an example:

Classic Report with Search (text item)

Your SQL statement probably looks like this:

select CUSTOMER_ID,
       CUST_FIRST_NAME,
       CUST_LAST_NAME,
       CUST_STREET_ADDRESS1,
       CUST_CITY,
       CUST_STATE,
       CUST_POSTAL_CODE,
       CUST_EMAIL,
       CREDIT_LIMIT
  from DEMO_CUSTOMERS
 where CUSTOMER_ID = :P4_SEARCH

When you want to search for multiple customers separated by a comma, how do you do that?
So in my search field I add for example: 1,2,3 and expect to see 3 customers.

There’re a couple of options you have, I’ll list three below:

  1. INSTR

    where INSTR(','||:P4_SEARCH||',', ',' || CUSTOMER_ID || ',') > 0
    
  2. REGEXP_LIKE

    where REGEXP_LIKE(CUSTOMER_ID, '^('|| REPLACE(:P4_SEARCH,',','|') ||')$')
    
  3. REGEXP_SUBSTR

    where customer_id in to_number((
      select regexp_substr(:P4_SEARCH,'[^,]+', 1, level) 
        from dual
     connect by regexp_substr(:P4_SEARCH, '[^,]+', 1, level) is not null 
    ))
    

Which one to choose? It depends what you need… if you need readability, maybe you find INSTR easier to understand. If you need performance, maybe the last option is the better choice… so as always it depends. If you want to measure the performance you can look at the Explain Plan (just copy the SQL in SQL Workshop and hit the Explain tab).

The Explain Plan for the first SQL looks like this:

Explain Plan INSTR

The Explain Plan for the last SQL looks like this:

Explain Plan REGEXP_SUBSTR

The above technique is also useful when you use want checkboxes above your report, so people can make a selection. For example we select the customers we want to see:

Classic Report with checkbox selection

The where clause would be identical, but instead of a comma (,) you would use a colon (:), so the first statement would be:

where INSTR(':'||:P4_SEARCH||':', ':' || CUSTOMER_ID || ':') > 0

Happy searching your Classic Report :)

No comments: