Parameter for IN query oracle

Question

SELECT * FROM EMPLOYEE
WHERE EMP_NAME IN (:EMP_NAME);

This is my query and now the EMP_NAME parameter I would like to send it as a list of strings.
When I run this query in SQL developer it is asked to send the EMP_NAME as a parameter, Now I want to send 'Kiran','Joshi' (Basically, I want to fetch the details of the employee with employee name either Kiran or Joshi. How should I pass the value during the execution of the query?

It works when I use the value Kiran alone, but when I concatenate with any other string it won't work. Any pointers in this?

I tried the one below
'Kiran','Joshi'
The above way doesn't work as understood this is a single parameter it tries the employee with the name as 'Kiran',Joshi' which won't come. Understandable, but in order to achieve this thing, how can I go ahead?

Any help would be really appreciated.

You may be try it by making a Function or Procedure that takes paramaters as EMP_NAME. — Lucifer, Feb 24 '14 at 07:03
Kedarnath, Thanks for the quick reply but can you help me out in guiding me how to do that? — HookUp, Feb 24 '14 at 07:07
You welcome, Its been long time since I worked with PL Sql part. However you can google this part. Remember that it is easy not that much tough :) — Lucifer, Feb 24 '14 at 07:09
Here the point is I am calling this query from Java Layer, I want to avoid SQL injections in this case. Hence I went with parameter approach. But how can I achieve this while sending as parameter which is formed dynamically? — HookUp, Feb 24 '14 at 07:14
I understand there are limitations for the number of parameters in IN clause but I wanted it to work atleast for few. Currently I am unable to achieve that. — HookUp, Feb 24 '14 at 07:43
Have you tried like ? `SELECT * FROM EMPLOYEE WHERE EMP_NAME IN (&EMP_NAME1,&EMP_NAME2);` for two param — Lucifer, Feb 24 '14 at 08:05

score 2 · Accepted Answer · edited May 23 '17 at 11:57

2

Thanks to the people who helped me in solving this problem.

I could get the solution using the way proposed, below is the approach

SELECT * FROM EMPLOYEE WHERE EMP_NAME IN (&EMP_NAME)

I have tried in this way and following are the scenarios which I have tested and they are working fine.

Scenario 1:

To fetch details of only "Kiran", then in this case the value of EMP_NAME when sql developer prompts is given as Kiran. It worked.

Scenario 2:

To fetch details of either "Kiran" or "Joshi", then the value of EMP_NAME is sent as

Kiran','Joshi

It worked in this case also.

Thanks Kedarnath for helping me in achieving the solution :)

edited May 23 '17 at 11:57

Community

1
1

answered Feb 24 '14 at 09:02

HookUp

393
1
6
20

While this might work for ad-hoc queries, it's generally a really bad idea - it's open for SQL injection (try using "'Kiran') or 1=1 --" for your EMP_NAME parameter), and it puts a lot of unnecessary load on the database, because you aren't using bind variables, and therefore each time you execute this query, you're generating a new, distinct SQL statement and force a so-called "hard parse" by the database. This doesn't scale at all. – Frank Schmitt Feb 24 '14 at 09:32
Agreed Frank Schmitt, Basically I wanted to avoid SQL Injection hence went with parameterized approach. But now as this is also open to SQL Injection it cannot be termed as a best solution. To avoid this scenario we are now forming the IN clause dynamically instead of adding a dynamic value to a single parameter. Thanks for the advice. I make sure I won't use in this way, but this question definitely helped me in a certain way. – HookUp Feb 24 '14 at 11:25
Forming the IN clause dynamically (i.e. building the SQL string dynamically in Java instead of using a prepared statement) suffers from the same problems - your application will still suffer from bad performance, and you're still vulnerable to SQL injection. The only sensible way to do this is to use a separate (temporary) table for the values in your in list or to use a nested table, see the answer by OracleUser. – Frank Schmitt Feb 24 '14 at 11:46

Maheswaran Ravisankar · Answer 2 · 2014-02-24T07:52:25.307

IN clause would be implicitly converted into multiple OR conditions.. and the limit is 1000.. Also query with bind variable means, the execution plan will be reused.. Supporting bind variables for IN clause will hence affect the bind variable's basic usage, and hence oracle limits it at syntax level itself.

Only way is like name in (:1,:2) and bind the other values..

for this, you might dynamic SQL constructing the in clause bind variables in a loop.

Other way is, calling a procedure or function(pl/sql)

DECLARE
    v_mystring VARCHAR(50);
    v_my_ref_cursor sys_refcursor;
    in_string varchar2='''Kiran'',''Joshi''';
    id2 varchar2(10):='123'; --- if some other value you have to compare
        myrecord tablename%rowtype;
  BEGIN

    v_mystring := 'SELECT a.*... from tablename a where name= :id2 and 
                    id in('||in_string||')';

    OPEN v_my_ref_cursor FOR v_mystring USING id2;

    LOOP
      FETCH v_my_ref_cursor INTO myrecord;
      EXIT WHEN v_my_ref_cursor%NOTFOUND;
        ..
      -- your processing
    END LOOP;
    CLOSE v_my_ref_cursor;

  END;

IN clause supports maximum of 1000 items. You can always use a table to join instead. That table might be a Global Temporary Table(GTT) whose data is visible to thats particular session.

Still you can use a nested table also for it(like PL/SQL table)

TABLE() will convert a PL/Sql table as a SQL understandable table object(an object actually)

A simple example of it below.

CREATE TYPE pr AS OBJECT
           (pr  NUMBER);
/
CREATE TYPE prList AS TABLE OF pr;
/

declare
  myPrList prList := prList ();
  cursor lc is 
    select * 
      from (select a.*
              from yourtable a
                   TABLE(CAST(myPrList as prList)) my_list
             where 
                   a.pr = my_list.pr
             order by a.pr desc) ;
  rec lc%ROWTYPE;

BEGIN 
  /*Populate the Nested Table, with whatever collection you have */
  myPrList := prList ( pr(91),
                       pr(80));
  /*
     Sample code: for populating from your TABLE OF NUMBER type 

     FOR I IN 1..your_input_array.COUNT
     LOOP
          myPrList.EXTEND;
          myPrList(I) := pr(your_input_array(I));
     END LOOP;
  */
  open lc;
  loop 
    FETCH lc into rec;
    exit when lc%NOTFOUND; -- Your Exit WHEN condition should be checked afte FETCH iyself!
    dbms_output.put_line(rec.pr);
  end loop;
  close lc;
END;
/

But I guess that is not a normal query right? I suppose that is a function/procedure. Yes I agree that using functions/procedures I could be able to achieve that, but I wanted to get it done using a simple query. Won't it be possible in that way? — HookUp, Feb 24 '14 at 07:42
IN clause would be implicitly converted into OR conditions.. and the limit is 1000.. Also query with bind variable means, the execution plan will be reused.. So Supporting bind variables for IN clause will hence affect the bind variable's basic usage, and hence oracle limits it at syntax level itself. Only way is like _name in (?1,?2)_ and bind the other values.. — Maheswaran Ravisankar, Feb 24 '14 at 07:45
I'd go for the GTT (temporary table) approach - easy to understand & works well. — Frank Schmitt, Feb 24 '14 at 09:45

Parameter for IN query oracle

2 Answers2

Related