Re: pl/sql : using a string variable in a 'WHERE...IN' statement for a numeric column

Thomas Kyte wrote:
>
> In article <5386544a.0109231255.4a8aa57e_at_posting.google.com>, kurt77_at_swbell.net
> says...
> >
> >Can I do that? How? Here is the general form for what I am trying to
> >do:
> >
> >SELECT C FROM T WHERE T.C IN (c_list)
> >
> >I'm trying to use this statement with a cursor so that I can fill a
> >pl/sql statement with the result set. c_list is a string that is
> >created dynamically with TO_CHAR and ||. Maybe there is another way
> >to do this that I am overlooking?
> >
> >Thanks for your help
> >Kurt
>
> see
> http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:110612348061
> for a method (but in 8i, you can just query:
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select * from all_users
> 2 where user_id in ( select *
> 3 from TABLE ( cast( str2tbl( '1, 3, 5, 7, 99' ) as mytableType ) )
> 4 )
> 5 /
>
> instead of using THE and DUAL as that example has...)
>
> --
> Thomas Kyte (tkyte_at_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

Oh dear oh dear...

I was recently reading in an excellent book that we should never, ever see literals in the SQL - surely this should read

 from TABLE ( cast( str2tbl( :b1 ) as mytableType )

Hmmmm... now which book was that?

:-)

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."