OraFAQ Forum: SQL & PL/SQL » Passing variable in IN clause with more than one values

Home » SQL & PL/SQL » SQL & PL/SQL » Passing variable in IN clause with more than one values

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Passing variable in IN clause with more than one values [message #236525]

Thu, 10 May 2007 02:59

bhoite_amol83
Messages: 110
Registered: June 2005
Location: Pune

Senior Member

Hi,
Please take a look at the following query.

DECLARE 
    A VARCHAR2(1000);
    B NUMBER;
BEGIN
    A :='0024'||','||'0007'||','|| '0008';
    SELECT COUNT(*)
    INTO   B
    FROM   TEMP
    WHERE  ID IN ('A');
    RAISE_APPLICATION_ERROR(-20001,B);
END;

Now the output of the query is 0.
I want the output of the query as 3. Because table has 3 records with the ID '0024','0007','0008'
I am passing the variable value A which has three values. Even i can pass the A variable by using the SUBSTR .But the problem is A varible is has not fixed number of values. They will increase dynamically.
Please tell me how can i pass the variable with different values.

Can anyone help me regarding the query?

Thanks in advance.

[Updated on: Thu, 10 May 2007 03:02]

Report message to a moderator