Writing Fast Queries in APEX

Tip #9 of my Writing Fast Queries blog post states: Avoid writing one query when it really should be two (or more) queries. It’s worth checking out that blog post in its entirety (as a refresher if you have already seen it).

To briefly recap tip #9, this is bad:

select EMPNO,
       ENAME,
       JOB,
       MGR,
       HIREDATE,
       SAL,
       COMM,
       DEPTNO
  from EMP
  where deptno = nvl(:P5_DEPTNO, deptno)
    and instr(upper(ename), upper(nvl(:P5_NAME, ename))) > 0

It’s bad because the where clause is trying to do too much. It will be parsed once and the execution plan will be cached and re-used. This should be multiple queries depending on whether or not the bind values are null.

One way to accomplish this is to use the APEX report feature “Function Body returning SQL Query”. You can then write a function that builds the query based upon the bind variables.

return q'~
select EMPNO,
       ENAME,
       JOB,
       MGR,
       HIREDATE,
       SAL,
       COMM,
       DEPTNO
  from EMP
  where 1=1 
  ~' ||
  case when :P5_DEPTNO is not null then ' and deptno = :P5_DEPTNO'
       else null
       end ||
  case when :P5_NAME is not null then ' and instr(upper(ename), upper(:P5_NAME)) > 0'
       else null
       end
       ;

Of course, this means those extra lines of code over and over, remembering that exact syntax each time you do it. And, as has been pointed out to me, if you make a mistake the “Check if it compiles” button doesn’t catch it at design time because the case statement won’t return anything.

Wouldn’t it be great if you could do something like this?

return 
    get_query(
        p_base_query        => 
q'~select EMPNO,
       ENAME,
       JOB,
       MGR,
       HIREDATE,
       SAL,
       COMM,
       DEPTNO
  from EMP ~',
    p_columns_aliases   => apex_t_varchar2('ENAME','DEPTNO'),
    p_page_items        => apex_t_varchar2('P5_NAME','P5_DEPTNO'),
    p_comp_method       => apex_t_varchar2('i','e')
    );

You just have to call “get_query” and pass in a list of columns, bind variable names, and if you want (i)nstring, (e)qual, (l)ike (not shown), or (in). You can do this for any number of columns and bind variables. The base query can be super complicated. The bind variables are handled by the get_query function. Oh, and it would be great if the “Check if it compiles” code would check the inputs of p_column_aliases, etc., as well.

Well, here it is, get_query below does just that. Note: Please put this into a package—don’t use it as a stand-alone function.

create or replace function get_query(
    p_base_query        in clob,
    p_columns_aliases   in apex_t_varchar2,
    p_page_items        in apex_t_varchar2,
    p_comp_method       in apex_t_varchar2   -- comparison method: i = instring, e = equal (=), l = like, in = in (select)
    ) return clob
as
l_final_query       clob;
l_lf                varchar2(32) := chr(10);
l_comp_method       varchar2(32);
begin

    l_final_query := 'select * from (' || l_lf || p_base_query || l_lf ||') where 1=1 ';

    if p_columns_aliases.count != p_page_items.count or p_columns_aliases.count != p_comp_method.count then
        raise_application_error(-20001, 'p_columns_aliases, p_page_items, p_comp_method do not have the same number of elements.');
    end if;

    for i in 1..p_columns_aliases.count loop

        if v(p_page_items(i)) is not null or v('APP_ID') = 4000 then
            l_comp_method := trim(lower(p_comp_method(i)));

            if l_comp_method not in ('i','e','l','in') then
                raise_application_error(-20001, 'p_comp_method must be i, e, l, or in');
            end if;

            l_final_query := l_final_query || l_lf || ' and '
                             || case 
                                    when l_comp_method = 'e' then 'upper(' || p_columns_aliases(i) || ') = upper(:' || p_page_items(i) ||')'
                                    when l_comp_method = 'i' then 'instr(upper('||p_columns_aliases(i) || '), upper(:' || p_page_items(i) || ')) > 0 '
                                    when l_comp_method = 'l' then 'upper(' || p_columns_aliases(i) || ') like upper(''%'' || :' || p_page_items(i) ||'|| ''%'' )'
                                    when l_comp_method = 'in' then p_columns_aliases(i) || ' in (select bvt.column_value from apex_string.split(:' || p_page_items(i) || ','':'') bvt)'
                                end;
        end if;
    end loop;

    return l_final_query;
end get_query;
/

Please let me know in the comments if you have any comments, suggestions, or just if you have used this.