Oracle Dynamic SQL: generic search - simple case

It is a very common problem - how do you implement generic search over the table? By generic search I mean that users are provided with tons of different options that could be used in all possible permutations.

It leads to a standard problem - how do you make sure that all of those searches are reasonably optimized. There are multiple schools of thoughts about it - a lot of contemporary database experts claim that CBO became so good that it can figure out everything. To be fair, I belong to the other group of people who are a bit more skeptical and suggest that we should help Oracle at least somewhat.

Of course, there is always DBMS_SQL package that can handle anything you can imaging, but a lot of developers (especially envisioning future maintenance) prefer to stay with Native Dynamic SQL. It is definitely understandable, and sometimes ago I came up with the trick to generic search without DBMS_SQL.

In the example below I wrote a function that would do a search on SCOTT.EMP table:

• three possible conditions (yes, I understand that a number of conditions may be unknown - I have a bit crazy example with passing XMLType as a list of parameters, but that is a bit more advanced. Let's start with the most direct case):
• EMPNO - direct ID check
• JOB - direct match
• ENAME - like condition
• Return limit is defaulted to 50, but can be overwritten
• Please, keep in mind that it is just a sample - if you need to do real pagination, I would rather recommend using the same approach, but return REF CURSOR as output. I will show this approach also as a separate post.
• Of course, keep in mind that loading thousands of objects into the memory may of may not be a good thing :-)
• Output with the list of primary keys
• Because I wanted to have that function usable in both SQL and PL/SQL, I decided to implement this output as a collection of numbers.

From the conceptual standpoint, the idea is very simple (but a bit strange :-) ):

• As the initial stage, I am trying generate not SQL, but PL/SQL block where ALL possible parameters become local constants. Each constant gets default value that will be passed in as a bind variable.
• At the same time I (in this example) am building SELECT and FROM clauses of the query. Sometimes this step also has to be conditional, because depending on parameters you may or may not need to do extra joins. To simplify the case for now we have a single-table search, so there is no need to do any checks here.
• Also I initialize WHERE clause (so later I can just do AND without worrying about the syntax) with mandatory condition.
• The second step involves spinning through all passed parameters and building extra conditions as needed. Here is where I have a main trick - instead of referencing real bind variables I reference my local variables that were created at the initial stage.
• The last step is to close PL/SQL block and fire EXECUTE IMMEDIATE. Please, notice, that I am passing ALL parameters - but in the SQL statement only SOME local variables will be used.

Overall described approach has a number of merits to be considered ;-) : 

1. - you don’t have to learn DBMS_SQL  :-)  
2. - you still will be using bind variables with all possible permutations
3. - you can directly see what you are trying to execute (very important for complex queries!)

create type id_tt is table of number;
/
-- search function
create or replace function f_search_tt 
    (i_limit_nr number:=null,
     i_param_empno_nr   number:=null,
     i_param_ename_tx varchar2:=null,
     i_param_job_tx varchar2:=null)
return id_tt
is
    v_sql_tx varchar2(32767);
    v_out_tt id_tt;
begin
    -- opening
    v_sql_tx:=
        'declare '||chr(10)||
        '  lv_count_nr constant number:=:1;'||chr(10)||        
        '  lv_empno_nr constant number:=:2;'||chr(10)||
        '  lv_ename_tx constant varchar2(50):=:3;'||chr(10)||
        '  lv_job_tx  constant varchar2(50):=:4;'||chr(10)||
        'begin '||chr(10)||
        ' select empno bulk collect into :5 from emp '||chr(10)||
        ' where rownum <=lv_count_nr ';

    -- i_param_empno
    if i_param_empno_nr is not null then
        v_sql_tx:=v_sql_tx||chr(10)||
                 ' and empno = lv_empno_nr ';
    end if;

    if i_param_ename_tx is not null then
        v_sql_tx:=v_sql_tx||chr(10)||
                 ' and ename like ''%''||lv_ename_tx||''%'' ';
    end if;

    if i_param_job_tx is not null then
        v_sql_tx:=v_sql_tx||chr(10)||
                 ' and job = lv_job_tx ';
    end if;

    -- closing
    v_sql_tx:=v_sql_tx||';'||chr(10)||
              'end;';

    dbms_output.put_line(v_sql_tx);

    execute immediate v_sql_tx
        using nvl(i_limit_nr,50),
              i_param_empno_nr,
              i_param_ename_tx,
              i_param_job_tx,
              out v_out_tt;

    dbms_output.put_line('Total rows found:'||v_out_tt.count);

    return v_out_tt;
end;
/

And now I can use this function in whatever pattern I want - exactly as specified!

-- get first 10 rows

select * from table(f_search_tt(10,null,null,null))
-- check ID
select * from table(f_search_tt(null,7566,null,null))
-- get only name search
select * from table(f_search_tt(null,null,'A',null))
-- get only job search
select * from table(f_search_tt(null,null,null,'MANAGER'))
-- get two conditions together
select * from table(f_search_tt(null,null,'A','MANAGER'))

Hope, it helps! And thanks a lot to my Collab'13 friends who constantly keep me thinking about better ways of solving already well-known problems!