Re: parameters in query

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Sat, 09 Apr 2005 13:44:50 -0700
Message-ID: <1113079267.470904@yasure>

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)

Received on Sat Apr 09 2005 - 15:44:50 CDT