Query-By-Example and IN lists

Currently using 11.2.0.2 but will be going to 12.1.0.2 soon.

I’m rewriting some old code that uses dynamic SQL and string concatenation.

The code is pretty much a Query-By-Example (QBE) procedure.

I'm rewriting it to NOT use dynamic SQL and concatenation.

I can do this easily as long as the parameters are single column equality/like:  
col1=some_value
or
col1 like ‘%’ || some_value || ‘%’

I’m stuck when it comes to lists of values.  Consider the list to be unbounded (up to the 1000 limitation of IN lists).  The reality is probably never more than 5 to 20.

I initially created a Global Temporary Table (GTT) thinking I could use IN with a SELET and not have to create a TYPE outside of the procedure and cast the associative array in the main SELECT.

I cannot figure out how to get everything in one select if there are no values in the lookup.

I know I can do another select to get a count of the rows in the GTT into a variable and use that variable in the main select or some other kludge.  I've included this work-around at the bottom.

The Challenge:
Single select.


Setup (the var is for my sqlplus testing):

drop table main_table purge;
create table main_table(col1 char(1), col2 number);

insert into main_table values('a',1);
insert into main_table values('a',2);
insert into main_table values('a',3);
insert into main_table values('b',4);
commit;

drop table lookup_table purge;
create global temporary table lookup_table(col2_val number) on commit delete rows;

var qbe_col1 varchar2(1);

Select allOpen in new window

Example of single value columns which works well:

exec :qbe_col1 := null;
--return all
select * from main_table
where
( :qbe_col1 is null or col1=:qbe_col1 )
/

--return only 'a'
exec :qbe_col1 := 'a';

select * from main_table
where
( :qbe_col1 is null or col1=:qbe_col1 )
/

Select allOpen in new window

Now, I need to pass in a list in addition to the single columns.

For example:  1,2

Works well if I have values:

--qbe_col1 is still 'a'
insert into lookup_table values(1);
insert into lookup_table values(2);

select * from main_table
where
( :qbe_col1 is null or col1=:qbe_col1 )
and
( col2 in (select col2_val from lookup_table) )
/

Select allOpen in new window

and I get the expected results:
a 1
a 2

This is where it fails:
commit;  -- clears out the GTT

I need ALL three 'a' rows back


Yes, I know this works:

var qbe_col2_count number

insert into lookup_table values(1);
insert into lookup_table values(2);

select count(*) into :qbe_col2_count from lookup_table;

select * from main_table
where
( :qbe_col1 is null or col1=:qbe_col1 )
and
( :qbe_col2_count = 0 or col2 in (select col2_val from lookup_table) )
/

commit;


select count(*) into :qbe_col2_count from lookup_table;

select * from main_table
where
( :qbe_col1 is null or col1=:qbe_col1 )
and
( :qbe_col2_count = 0 or col2 in (select col2_val from lookup_table) )
/

Select allOpen in new window