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.
- 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 ;-) :
- - you don’t have to learn DBMS_SQL :-)
- - you still will be using bind variables with all possible permutations
- - you can directly see what you are trying to execute (very important for complex queries!)
-- search function
create or replace function f_search_tt
' 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)||
' select empno bulk collect into :5 from emp '||chr(10)||
' where rownum <=lv_count_nr ';
if i_param_empno_nr is not null then
' and empno = lv_empno_nr ';
if i_param_ename_tx is not null then
' and ename like ''%''||lv_ename_tx||''%'' ';
if i_param_job_tx is not null then
' and job = lv_job_tx ';
execute immediate v_sql_tx
dbms_output.put_line('Total rows found:'||v_out_tt.count);
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!