Using Oracle collections in Dynamic SQL

Question

Hi! I need to check, if procedure parameters are null, and if not, to use it in WHERE clause. For example:

sqlquery := 'SELECT * FROM table WHERE a_col = a AND';
IF b IS NOT NULL THEN
  sqlquery := sqlquery || ' b_col = :b';
END IF;
IF c IS NOT NULL THEN
  sqlquery := sqlquery || ' c_col = :c';
END IF;

And so on.

Then I need to use OPEN-FOR-USING statement to open cursor for formed sqlquery, but previously I should decide, which values to send by USING clause. The only way I see is to use lots of IF clause:

IF b IS NOT NULL AND c IS NOT NULL THEN
  OPEN cur FOR sqlquery USING b, c;
ELSIF b IS NOT NULL THEN
  OPEN cur FOR sqlquery USING b;
ELSIF c IS NOT NULL THEN
  OPEN cur FOR sqlquery USING c;
ELSE 
  OPEN cur FOR sqlquery;

For N values I get large amount of IF clauses. How can I slove this problem without lots of IFs? I think there can be used Oracle Collections, but I didn't find any example of it.

Answer 1

You can make the SQL query do the work of checking each item for NULL.

sqlquery := 'SELECT * FROM table WHERE a_col = a'||
            ' AND (:b IS NULL OR b_col = :b)'||
            ' AND (:c IS NULL OR b_col = :c)';

OPEN cur FOR sqlquery USING b,b,c,c;

You do have to repeat the variables in the USING clause, which sadly creates an opportunity for typos to cause confusing bugs. But at least you only have to do it right once.

Answer 2

You need to construct your query such that it always references :b and :c something like this:

sqlquery := 'SELECT * FROM table WHERE a_col = a';
IF b IS NOT NULL THEN
  sqlquery := sqlquery || ' AND b_col = :b';
ELSE 
  sqlquery := sqlquery || ' AND :b IS NULL';
END IF;
IF c IS NOT NULL THEN
  sqlquery := sqlquery || ' AND c_col = :c';
ELSE 
  sqlquery := sqlquery || ' AND :c IS NULL';
END IF;

Then you can use it like this:

OPEN cur FOR sqlquery USING b, c;

Actually, I think it may be more efficient to do this:

sqlquery := 'SELECT * FROM table WHERE a_col = a';
IF b IS NOT NULL THEN
  sqlquery := sqlquery || ' AND b_col = :b';
ELSE 
  sqlquery := sqlquery || ' AND (1=1 OR :b IS NULL)';
END IF;
IF c IS NOT NULL THEN
  sqlquery := sqlquery || ' AND c_col = :c';
ELSE 
  sqlquery := sqlquery || ' AND (1=1 OR :c IS NULL)';
END IF;

... because then the optimiser can recognise that 1=1 is always true and so can omit that predicate from consideration altogether. I remember reading that somewhere (a Tom Kyte article in Oracle Mag I think) but can't find the reference now.

Answer 3

Did you try this?

select * from my_table
where a_col = :a
  and b_col = (case when :b is not null then :b else b_col end)
  and c_col = (case when :c is not null then :c else c_col end)
  and d_col = (case when :d is not null then :d else d_col end)
;

Answer 4

I had a similar situation.

I worked out what the workable execution plans were and used separate queries for them.

For example, say I'm searching on people and the parameters are first_name, last_name. gender, date_of_birth. The table has indexes on (last_name,first_name) and (date_of_birth).

IF :p_firstname IS NOT NULL and :p_lastname IS NOT NULL THEN
  OPEN cur FOR 
    'SELECT * FROM PEOPLE WHERE last_name=:a AND first_name=:b AND
     (date_of_birth = :c or :c is NULL) AND (gender = :d or :d IS NULL)' USING ....
ELSIF :p_lastname IS NOT NULL THEN
  OPEN cur FOR 
    'SELECT * FROM PEOPLE WHERE last_name=:a AND 
     (date_of_birth = :c or :c is NULL) AND (gender = :d or :d IS NULL)' USING ....
ELSIF :p_dateofbirth IS NOT NULL THEN
  OPEN cur FOR 
    'SELECT * FROM PEOPLE WHERE date_of_birth=:a AND 
     (first_name=:b OR :b IS NULL) AND (gender = :d or :d IS NULL)' USING ....
ELSE
  RAISE_APPLICATION_ERROR(-20001,'Last Name or Date of Birth MUST be supplied);
END IF;