Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm looking to make sure that all queries in a php web application have proper use of bind variables to minimize parsing of the queries.

I'm wondering how Oracle parses queries that compares a column to a list of values. Will Oracle consider these statements to be the same, or must the list be inside bound variables?

select char from alphabet where char not in ('a', 'b');

select char from alphabet where char not in ('c', 'd');

If the contents of the list must be in a bind variable, can it be done with a single variable, or must one put each item on the list in a separate variable?

select char from alphabet where char not in (:list);

select char from alphabet where char not in (:c1, :c2);

If the latter is true, will queries with a different number of items in the list still be considered to have the same structure?

select char from alphabet where char not in (:c1, :c2);

select char from alphabet where char not in (:c1, :c2, :c3);
share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

You must bind the elements of an IN list, otherwise Oracle will consider these separate statements. Any differences in the text of the SQL will cause a new statement to be parsed (excepting some cases when cursor_sharing = force). Therefore your examples with :a, :b and :a, :b, :c will be considered different SQL statements.

Each element must be bound to a separate variable. If you bind 'fred,nick' to one variable, Oracle will look for the string "fred,nick", instead of the separate items fred and nick.

If you have variable in-lists, but don't want to be parsing a new statement for each additional item, you'll need to pass the elements as a comma separated string into one bind variable, then parse the elements out yourself.

Tom Kyte discusses a couple of ways to do this here. Basically you can do this by writing a function which returns a nested-table which you can wrap a table function around to make it a row source. Alternatively it can be done in pure SQL by using the connect by level <= :num_of_rows trick to generate the number of elements you need and parsing the bind variable that way.

share|improve this answer
    
A clear and concise answer, thank you! –  Roy Jan 8 '13 at 21:36
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.