Hi,

I am trying to use the IN clause with my bind variable but with no success.

Any ideas on how to do this?

Thanks,

-E

Ekta,

Show us what your code looks like now, and we can post code that will work for you. (Without code, we have no clue what to suggest. <smile>)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
"Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty."

If I try a simple select statement with my bind variable I do get data with just one value but if I enter multiple values I don't get any results. I m using A bind variable where user can enter multiple values for that parameter.

Select * from table where type IN :1

But if I try the statement below without the bind variable I get the right results
Select * from table where type IN ('AA', 'BB')
 

Ekta,

To achieve your desired result, instead of using the "IN" operator as you have done, I recommend your using the "LIKE" operator as I have done below:Let us know if this works for you.
 

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
"Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty."

Don't use SantaMufasa's suggestion.  It has the same flaws as a similar suggestion he made in thread186-1470738: How to pass a complex query in a variable.  SantaMufasa's code will generate spurious matches.  For example, with :X := 'AA,BB' as above, you will get matches on rows that contain TYPE = 'AA', 'BB', 'A', and 'B'.  In all probability you don't want the matches on 'A' and 'B', since they are only substrings of the values you are searching for.

It is much better to build a solution similar to Tom Kyte's suggestion, the one mentioned in thread186-1470738: How to pass a complex query in a variable.

I prefer a simple all-purpose function like:

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

Barb,

I, of course, prefer the simplicity of your function, over the complexities of Tom Kyte's example (albeit his solution is very clever).

To solve the original need that Ekta presented, what value do you assign to the bind variable to produce "Y" values on both the "AA" and "BB" rows, using your code?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
"Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty."

Of course you are correct. I neglected to post how Ekta could use the function in his/her scenario.Ekta could use:

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

I'm sorry I wasn't clear, Barb...I meant that I wasn't able to get proper results using a bind variable as the argument in your function. Could you please illustrate population of a bind variable that produces Ekta's desired results?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
"Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty."

I haven't tested BJ's suggestion, but at first glance it looks remarkably similar to the revisions SantaMufasa made to his code in thread186-1470738: How to pass a complex query in a variable after I pointed out the problem with spurious matches.  If this is indeed the case, I'm afraid it isn't really a good alternative to Tom Kite's solution.  The problem is that it doesn't entirely eliminate the problem with spurious matches.  In particular, you need to know ahead of time that the delimiter character doesn't appear in the data being searched.  If it does, you could (and very possibly will) get spurious matches on rows that contain, say, ',AA,' as a substring of a longer string.

I see the BJ incorporates SantaMufasa's other suggestion to parameterize the delimiter character in an attempt to provide a work-around to this issue with spurious matches.  But it still doesn't solve the basic issue that, in order to get the search to work properly, you have to know ahead of time a lot of detail about the contents of potentially millions of rows of data.  But I would say a search function has limited value if it only works when you have advanced knowledge of the data you're searching.

That's especially true since we already know about Tom Kyte's solution, which doesn't have this fundamental limitation.  No doubt there may be other approaches that work as well or better than Tom Kyte's, but so far we haven't come up with any.  Until that happens, I recommend against trying to reinvent the wheel and go instead with the proven solution.