Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: parameters in query
Tom wrote:
> OK
> i read about CAST function and seems i have problem with converting
string
> '1,5,9'
> select * from order where id_order in ( cast (:id_order) as
> InStrTab)..
> probably syntax isn't right or there is incompatible data types
>
>
> "DA Morgan" <damorgan_at_x.washington.edu> wrote in message
> news:1112997520.564158_at_yasure...
> > Tom wrote:
> >> i have lot of problems with letter query:
> >>
> >>
> >> select * from order where id_order in (1,5,9)
> >>
> >> oracle execute this query without problems, but
> >>
> >> select * from order where id_order in (:id_order)
> >>
> >> parametar is 1,5,9 oracle returns error
> >>
> >> any idea??
> >
> > If I understand what you are trying to do there is a very
> > simple solution.
> >
> > http://www.psoug.org
> > click on Morgan's Library
> > click on Conditions
> > scroll down to the "Complex IN Demo" using CAST
> > --
> > Daniel A. Morgan
> > University of Washington
> > damorgan_at_x.washington.edu
> > (replace 'x' with 'u' to respond)
No, it should be:
8i: FROM TABLE(CAST(collectionname AS type)) 9i: FROM TABLE(collectionname)
CAST converts between datatypes. In 8i you have to tell Oracle the type of the collection. In 9i you mostly don't have to. TABLE converts a collection into something SQL can query.
See this article:
http://www.williamrobertson.pwp.blueyonder.co.uk/documents/comma_separated.html
Received on Sat Apr 09 2005 - 14:32:24 CDT