how to get multiple records by passing parameter to where clause in oracle pl/sql

Question

table :

create table emp
(
  E_ID number,
  E_NAME varchar2(30)
);

select * from emp;

101 name1
102 name2

My code:

declare
v1 varchar2(30) := '101,102';
begin
for i in (select e_id,e_name 
          from emp
          where e_id in (v1)) loop
dbms_output.put_line(i.e_id);
end loop;
end;
/

ISSUE:

Getting ORA -01722:invalid number

Please help to understand this issue and suggest me the solution.

You're comparing a number to a string. e_id is NUMBER while v1 is a VARCHAR2. — Renz Dominique, Dec 12 '18 at 09:23
Possible duplicate of [Splitting string into multiple rows in Oracle](https://stackoverflow.com/questions/14328621/splitting-string-into-multiple-rows-in-oracle) — GolezTrol, Dec 12 '18 at 09:28
You need to split the string into a table of separate items. This is not trivial, but the suggested duplicate shows various ways to approach this. — GolezTrol, Dec 12 '18 at 09:29
you are passing string while checking with Integer column. you can alter the column to varchar2 and try string literal - Bind variable may to work in this case. — Atul Kr Dey, Dec 14 '18 at 06:29

score 0 · Answer 1 · answered Dec 12 '18 at 09:24

0

It is syntax error.

E_ID is of number type and you are comparing it will v1 which is varchar2 type.

answered Dec 12 '18 at 09:24

Chaitanya Kotha

476
2
4

There is a larger issue here than attempting to compare a string to a number. The OP routine would fail even had e_id been defined as character. While the 2 expressions: "in(101,102)" and in(v1) when v1='101,102' appear the same they are actually totally different concepts. Oracle recognizes the first as a list of values during the parse phase while the second is a symbol value substitution after parse. At that point v1 is just a single value that just happens to contain a comma - just like this comment contains them. To verify redefine e_id as varchar, leave every else the same and rerun. – Belayer Dec 15 '18 at 19:36

score 0 · Answer 2 · answered Dec 12 '18 at 10:00

Welcome to SO. A great place to ask questions: I can see what you're trying to do. Syntactically, you'd be forgiven for trying to query your table using the "IN" clause, but as others have said, this can not be done where you have committed your numeric values into a varchar2. In anycase, an array or a collection, (even if you had created one) it isn't an easy option here. But you do have a variety of solutions open to you:

1/ Place your numbers into an Array and use a condition in your loop and check that e_id forms part of the your array. But in-elegant!

2/ Create a global temporary table and add your numbers in and add the table into your query, specify a join.

3/ Create some dynamic PL/SQL using a Ref Cursor. I've included an example for you below, using your table (emp) and values. In this case, you'd be able to build up your string according to the values you want to query. See below. The varchar2 string: sqlString can be manipulated however you want. Paste into a test-harness and see. Hope it helps

declare
  type refCursor is ref cursor;
  tableCursor refCursor;
  emp_record  emp%rowtype;
  sqlString   varchar2(200);

begin
 -- Dynamic SQL statement with placeholder:
  sqlString := 'SELECT * FROM emp WHERE e_id in 
(101, 102)';

  -- Open cursor:
  open tableCursor for sqlString;

  -- Fetch rows from result set one at a time:
  loop
    fetch tableCursor
    into emp;
    exit when tableCursor%notfound;
    dbms_output.put_line(emp.e_id);
  end loop;

  -- Close cursor:
  close tableCursor;

end;

how to get multiple records by passing parameter to where clause in oracle pl/sql

2 Answers2