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

Thank you all for your insightful advice. Looks like I 've got my work cut out for me - I need to learn a few things about PL/SQL in general and DNS in particular, as my queries contain a few of these parameters in a single SQL and also the queries themselves are pretty complex - one in particular is 4 pages (8.5" X 11'') long. Thank you agai.n

Regards, Kavous

Thomas Kyte <tkyte_at_us.oracle.com> wrote in message news:9uu0ic02b04_at_drn.newsguy.com...
> 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
>
Received on Sun Dec 09 2001 - 15:05:17 CST