| send values to IN query with parameter [message #209313] |
Thu, 14 December 2006 04:15  |
roy_mm
Messages: 3
Registered: December 2006
|
Junior Member |
|
|
Subject: send values to IN query with parameter
Hello. Hope I'll explain my problem well enough:
I have a query:
-- select id from table where id in ('100')
Now I used parameter for the IN part:
-- select id from table where id in (:param)
and I gave the parameter the value 100.
now I want to have the query with two values:
-- select id from table where id in ('100','200')
I tried to use the parameter and give him the value of: '100','200'.
It didn't worked 
Is there a syntax to give the parameter more then one value for the IN query (with out using another table or function etc')?
Thanks in advance,Roy.
|
|
|
|
|
|
|
|
| Re: send values to IN query with parameter [message #209358 is a reply to message #209313] |
Thu, 14 December 2006 07:48  |
orafaqer
Messages: 48
Registered: July 2006
|
Member |
|
|
As described on T. Kyte blog, you can pass a collection of values, and then use something like
select id from table where id in
(select /*+ cardinality(t 0)*/ from table(cast(:collection as my_collection)) t)
But there is a bug (reproducible on 9.2.0.7) with passing a collection to a query with subquery factoring clause and temp table transformation step in execution plan, ex.:
SET SERVEROUTPUT ON
create or replace type test_array as table of number(20)
/
SHOW err
DECLARE
x test_array := test_array(1, 2, 3);
l_cnt INT;
BEGIN
WITH t AS (SELECT /*+inline*/ COLUMN_VALUE FROM TABLE(CAST(x AS test_array)))
SELECT COUNT(*)
INTO l_cnt
FROM
(SELECT * FROM t UNION ALL SELECT * FROM t);
dbms_output.put_line(l_cnt);
END;
/
DECLARE
x test_array := test_array(1, 2, 3);
l_cnt INT;
BEGIN
WITH t AS (SELECT COLUMN_VALUE FROM TABLE(CAST(x AS test_array)))
SELECT COUNT(*)
INTO l_cnt
FROM
(SELECT * FROM t UNION ALL SELECT * FROM t);
dbms_output.put_line(l_cnt);
END;
/
and output
6
PL/SQL procedure successfully completed
DECLARE
x test_array := test_array(1, 2, 3);
l_cnt INT;
BEGIN
WITH t AS (SELECT COLUMN_VALUE FROM TABLE(CAST(x AS test_array)))
SELECT COUNT(*)
INTO l_cnt
FROM
(SELECT * FROM t UNION ALL SELECT * FROM t);
dbms_output.put_line(l_cnt);
END;
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at line 5
So, be aware of it.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
