Re: Passing a parameter to a Where Clause in PL/SQL !

In article <G5uQ7.17484$L51.38783_at_rwcrnsc54>, "Jim says...
>
>Don't use dynamic sql at runtime if you don't have to it limits the
>scalability of the database.
>Jim

no more then using a static ref cursor, which is what you were suggesting here weren't you:

...
Have 2 queries and pick the one that you need. If it is null then use the query that does not specify the d_column otherwise use the other. ......

static and dynamic ref cursors are parsed on each open (they have to be). If you have a choice, pick "regular" cursors over ref cursors.... a dynamically opened ref cursor is roughly equivalent to a static one though.

I think we all agree tho that "where d = nvl( bind_variable, d )" is a bad approach in most cases...

>"Marc Blum" <marc_at_marcblum.de> wrote in message
>news:3c12692a.93204_at_news.online.de...
>> With your solution you're in trouble with rows where d_column actually
>> is NULL, because
>>
>> WHERE NULL = NULL evaluates to false!
>>
>> better use native dynamic SQL to construct your SQL-Statement at
>> runtime.
>>
>>
>> On Sat, 08 Dec 2001 18:20:53 GMT, "Kavous Niamir" <KNiamir_at_home.com>
>> wrote:
>>
>> >You will have to excuse me if this sounds a bit elementary, but I'm new
>to
>> >this stuff and have to find out ASAP. I have a VB application that passes
>> >several parameters to a PL/SQL package. These are used in a WHERE clauses
>of
>> >a SQL query. For example :
>> >
>> >Select a_column
>> >from a_table
>> >where b_column = ai_str1
>> >and c.column = ai_str2
>> >and d.column = ai_str3 -- the line I'm having problem with
>> >
>> >Now my problem is that a user can either send a string for ai_str3 or a
>null
>> >value. How can I structure the query such that if the user sends a null
>> >value, the third condition would be simply ignored.. I have been thinking
>> >along the lines of having :
>> >
>> >d_column = nvl(ai_str3, d_column)
>> >
>> >Would that work or what do you think I should do?
>> >
>> >Regards, Kavous
>> >
>> >
>> >
>> >
>>
>> regards
>> Marc Blum
>> mailto:marc_at_marcblum.de
>> http://www.marcblum.de
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp