Query the table with array in the where clause in PL/SQL?

Question

I have the list of employee_id's as an array. How do I query the table with array in the where clause in PL/SQL?

empIdArray = [1,2,3]

SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID IN (empIdArray);

post a minimal reproducible example please

OldProgrammer
– OldProgrammer

2022-07-18 16:01:52 +00:00
Commented Jul 18, 2022 at 16:01 — OldProgrammer
– OldProgrammer, Commented Jul 18, 2022 at 16:01
SQL or PL/SQL doesn't have an array datatype

astentx
– astentx

2022-07-18 20:27:43 +00:00
Commented Jul 18, 2022 at 20:27 — astentx
– astentx, Commented Jul 18, 2022 at 20:27

Littlefoot · Accepted Answer · 2022-07-18 19:48:44Z

1

That would be something like this (see line #8):

SQL> declare
  2    empidarray sys.odcinumberlist := sys.odcinumberlist(7369,7499,7521);
  3    l_names    sys.odcivarchar2list;
  4  begin
  5    select ename
  6    bulk collect into l_names
  7    from emp
  8    where empno in (select * from table(empidarray));      --> this
  9
 10    for i in l_names.first .. l_names.last loop
 11      dbms_output.put_line(l_names(i));
 12    end loop;
 13  end;
 14  /
SMITH
ALLEN
WARD

PL/SQL procedure successfully completed.

SQL>

answered Jul 18, 2022 at 19:48

Littlefoot

144k15 gold badges42 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query the table with array in the where clause in PL/SQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related