Home » SQL & PL/SQL » SQL & PL/SQL » send values to IN query with parameter
send values to IN query with parameter [message #209313] Thu, 14 December 2006 04:15 Go to next message
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 Sad

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 #209319 is a reply to message #209313] Thu, 14 December 2006 04:48 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
This might help you.
comma-separated-input
Thatks to William.

By
Vamsi
Re: send values to IN query with parameter [message #209331 is a reply to message #209319] Thu, 14 December 2006 05:49 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
this might be helpful to you
http://tkyte.blogspot.com/2006/06/varying-in-lists.html

Re: send values to IN query with parameter [message #209358 is a reply to message #209313] Thu, 14 December 2006 07:48 Go to previous message
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.
Previous Topic: Question for PL/SQL Package dbms_repair
Next Topic: Insert statement that ignores some rows
Goto Forum:
  


Current Time: Sat Dec 10 01:09:10 CST 2016

Total time taken to generate the page: 0.09332 seconds