Re: Need help for IN operator in Oracle

On Dec 14, 2:09 pm, sangu_..._at_yahoo.co.in wrote:
> Hi,
> I need some help in using the IN operator with Oracle 9i. The issue is
> I will get a data in variable (Lets say the variable is "A") with this
> below foramt
>
> 'var1','var2','var3'
>
> The word is a combination of 3 words where each word enclosed in a
> single quotes and seperated by a comma operator. This is how i will get
> the string to my SP. Now i have to seach a table to see whether the
> column content of a table matches with the any of the words ( i.e.
> var1, var2 or var3) in the above string. Then my query looks like
>
> SELECT * FROM TEMP WEHRE Col IN A;
>
> The above query will treat the string 'var1','var2','var3' as a one
> single string not as a combination of words. So my requirement won't be
> solved with the above query.
>
> I am able to retireve the data correctly in SQL Server by using the
> late binding concept. The code for that
>
> declare @b varchar(20)
> declare @sql nvarchar(200)
> set @b = '''var1'',''var2'',''var3'''
> set @sql = 'select * from temp where a in ('+ @b +')'
> exec sp_executesql @sql
>
> My temp table contains 2 values : var1 and var2
>
> The above query returns me var1 and var2 from Temp table.
>
> Any idea how can get the same effect in Oracle 9i. I am very much
> thankfull for your suggestion as we are nearing to release it is very
> urgent for me.
>
> Thanks in advance
> Rao

http://asktom.oracle.com, search for 'dynamic in list'. The above code is just another example why sqlserver is utter crap, and people brainwashed by sqlserver should stop 'porting' their bad habits to Oracle.
The approach above is utterly unscalable in Oracle.

-- 
Sybrand Bakker
Senior Oracle DBA