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 16:21:46 -0700
Message-ID: <1113088905.998100.317950@g14g2000cwa.googlegroups.com>


DA Morgan wrote:
> William Robertson wrote:
>
> > 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
>
> Consider this:
>
> conn scott/tiger
>
> Connected to:
> Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 -
Production
> With the Partitioning, OLAP and Data Mining options
>
> SQL> set serveroutput on
> SQL> DECLARE
> 2
> 3 i PLS_INTEGER;
> 4 x InStrTab := InStrTab('10','30');
> 5
> 6 BEGIN
> 7 SELECT COUNT(*)
> 8 INTO i
> 9 FROM emp
> 10 WHERE deptno IN (
> 11 SELECT column_value
> 12 FROM TABLE(CAST(x AS InStrTab)));
> 13
> 14 dbms_output.put_line(i);
> 15 END;
> 16 /
> 9
>
> PL/SQL procedure successfully completed.
>
> SQL> DECLARE
> 2
> 3 i PLS_INTEGER;
> 4 x InStrTab := InStrTab('10','30');
> 5
> 6 BEGIN
> 7 SELECT COUNT(*)
> 8 INTO i
> 9 FROM emp
> 10 WHERE deptno IN (
> 11 SELECT column_value
> 12 FROM TABLE(InStrTab));
> 13
> 14 dbms_output.put_line(i);
> 15 END;
> 16 /
> FROM TABLE(InStrTab));
> *
> ERROR at line 12:
> ORA-06550: line 12, column 16:
> PL/SQL: ORA-00904: "INSTRTAB": invalid identifier
> ORA-06550: line 7, column 3:
> PL/SQL: SQL Statement ignored
>
> Oracle seems to disagree: At least in 10gR1.
> --
> Daniel A. Morgan
> University of Washington
> damorgan_at_x.washington.edu
> (replace 'x' with 'u' to respond)

By "collectionname" I meant the name of the collection variable, so

12 FROM TABLE(InStrTab));

needs to be

12 FROM TABLE(x)); Received on Sat Apr 09 2005 - 18:21:46 CDT

Original text of this message

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