Home » SQL & PL/SQL » SQL & PL/SQL » How to use "IN" for this query (oracle dbase 10g, xp)
How to use "IN" for this query [message #384451] Tue, 03 February 2009 21:27 Go to next message
OraVision
Messages: 17
Registered: March 2008
Junior Member
SELECT * FROM EMP WHERE DEPTNO IN (10,20,30) --Its working and OK.


BUT if i want to use a variable instead of hard code like:

x:='10,20,30' and put this variable x in the IN function its showing required INTO clause...

SELECT * FROM EMP WHERE DEPTNO IN (x);--- Its not working. what should i have to do pls?


help me pls

-Thank you.
Re: How to use "IN" for this query [message #384454 is a reply to message #384451] Tue, 03 February 2009 22:26 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you.

One of the Guidelines is to use the SEARCH function of this forum.

SEARCH for: varying in list
Re: How to use "IN" for this query [message #384506 is a reply to message #384451] Wed, 04 February 2009 02:45 Go to previous messageGo to next message
icmohsin
Messages: 12
Registered: January 2009
Location: Mumbai
Junior Member
Use this query

select count(*) from emp where deptno in (&X);
Re: How to use "IN" for this query [message #384569 is a reply to message #384451] Wed, 04 February 2009 05:37 Go to previous messageGo to next message
Martin Eysackers
Messages: 80
Registered: October 2005
Location: Belgium
Member
a varying IN list is possible using objects
it is best explained here :
<a href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:110612348061" target="_blank"></a>


http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:110612348061

[Updated on: Wed, 04 February 2009 07:13]

Report message to a moderator

Re: How to use "IN" for this query [message #384573 is a reply to message #384506] Wed, 04 February 2009 05:45 Go to previous messageGo to next message
c_stenersen
Messages: 255
Registered: August 2007
Senior Member
icmohsin's solution means a danger of SQL injection though.. If you use it, be sure to check the value of x to make sure that it only contains numbers separated by commas (by using regular expressions or such).

imagine that x has the value '1) or (1=1'
the result would be:
select count(*) from emp where deptno in (1) or (1=1);
=>Always true, and the total count of entries are retrieved. Maybe not so severe in this case, but a point for you if you need to use dynamic queries in the future.
Re: How to use "IN" for this query [message #384575 is a reply to message #384573] Wed, 04 February 2009 05:48 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The other big problem with @icmohsin's solution is that it won't work at all in Pl/Sql.
Previous Topic: Objects Used in a procedure (Merged 5)
Next Topic: Effect of drop Table
Goto Forum:
  


Current Time: Wed Dec 07 04:43:02 CST 2016

Total time taken to generate the page: 0.11149 seconds