Select IN with NULLS

Oracle Database 10g Enterprise Edition Release

Is the following SQL syntax correct ?

Select FieldA, FieldB, FieldC, FieldD, FieldD From ViewA
where FieldD in (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20);

The SQL runs correctly sometime and other times not. When all the Bind variable are defined and are NOT null it always runs. If some of the bind variables are not define or NULL it runs correctly  sometimes and others not.  It just goes into a run in Oracle and never returns.

Is there a better way to write the SQL so that BIND variables can be NULL.  
Who is Participating?

I've had similar problem, but in my case I could have a lot more then 20 values in a list (in some cases 1000 and more).
Solution was to populate global temporary table, and join with it.

Insert all values using prepared statement
insert into gtt_temp(id) values (?)

Select FieldA, FieldB, FieldC, FieldD, FieldD From ViewA v join gtt_temp g on (

See if it might help you...

Hint: in case of wrong explain plan (i.e. nested loop instead of hash join, I've analyzed table gtt_temp after insert, before select)

slightwv (䄆 Netminder) Commented:
What code are you using to call this?

I can see it failing in the bind variable is not defined but it should run if null.

For example:
select * from dual where dummy in (null, null, 'A', null, null, 'X', null);
In the both cases  of "result" and "never returns"  are you able to determine the currently used execution plan  ?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

- from my test, the WHERE - IN syntax does not process NULL. to make it work we have to use OR outside the IN statement:

SELECT FieldA, FieldB, FieldC, FieldD, FieldD From ViewA
WHERE FieldD IN (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20)
slightwv (䄆 Netminder) Commented:
Interesting.  I didn't read the question like that.
- at 1:20am, my eyes maybe fails on me again :)
- not clear if he wants to process the NULL value from the bind variable or not. if blank variable = do nothing, then slightwv's sql will work. Oracle doesn't gives error nor process anything by specifying NULL in the IN().
fpkeeganAuthor Commented:
The input is a list from the screen. Using Jave code WEB.  Some times there is 1 items input some times there is 20 items input, and any in between. Looking at the v$sql_bind_capture capture table, the real input have values and WAS_CAPTURED is YES. The values not input have WAS_CAPTURED is NO. .
In my example above FieldD is never null. The input (bind) data can be null and sometimes is null.

The problem with the SQL running forever is caused by the ORACLE optimizer. Change the optimizer_features_enable parameter and change the behavior of the SQL.  It is not possible to create a repeatable example on line that can demonistrate the Oracle bug. with one set of input it will run in less than 3 seconds. Another set of input I kill it after 12 hours.

The QUESTION is What is the best sql, most correct, most effective way to do a variable list of input data ?

For example is this better then the first set of code.
Select FieldA, FieldB, FieldC, FieldD, FieldD From ViewA
where FieldD in
(select distinct * from table(sys.dbms_debug_vc2coll(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20));

The TWO sql generate the same results but behave very differently.
slightwv (䄆 Netminder) Commented:
Sorry, not a Java person but in .Net you can do similar things with arrays.

A quick Google provided an article that shows something very similar.  It still leverages the power of bind variables and the power of arrays.
slightwv (䄆 Netminder) Commented:
>>The values not input have WAS_CAPTURED is NO

Just curious:  When you pass in empty values are they set to an actual database (Oracle) NULL or are they set to something similar to 'undefined'?

Again, using .Net (it's what I know) and Oracle's ODP.Net data provider I can set unassigned values to DBNull.Value.

Does Java have anything similar?
I think you can check the values of the items if it is null then assign some value to it in code. Value will be something which is not possible for that item say -1,-9999,'-NULL' etc

when bind variable in "IN" clause will have not null value, query should work fine and as such value won't exist in db your results will be correct.
fpkeeganAuthor Commented:
slightwv -- the values are not passed.  Just and FYI - WAS_CAPTURED is sometimes NO even when the values are passed in a number of different SQLs.

The explain plain that is posted in ORACLE is not relevant as bind variable peeking will change the explain plan on the fly. The changed explain plan from bind variable peeking is not stored.  
fpkeeganAuthor Commented:
Thanks.. In fact I have more than 100...  That Is the best solution I have heard.  I will give it a try..  The Problem also changes as the OPTIMIZER_FEATURES_ENABLE  value changes.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.