Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: parameters in query

Re: parameters in query

From: William Robertson <william.robertson_at_bigfoot.com>
Date: 9 Apr 2005 12:32:24 -0700
Message-ID: <1113075144.849368.318840@o13g2000cwo.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US