Writing a PLSQL select in array

Question

I'm using a DB 'A' to output a list of numbers :

123455
123456
123457

And I'm looking to build a dynamic statement to look into a DB 'B' with those results as a filter

a. Build an array with the values from DB 'A'

SELECT * FROM my_table
WHERE number in &array;

How can I achieve this ?

The DB 'B' is an Oracle DB.

Thanks

Answer 1

Hm. Looks like your "background" is not Oracle, because there are no "DB"s there. I mean, there are, but not in a context you're using them. If "DB" stands for a "Database", to me it looks as if you're actually talking about tables here.

Also, I don't understand what

The DB 'B' is in PLSQL means.

If "database" is a table, how is it in PL/SQL?

---

Anyway, to get you started: I'm fetching some data from Scott's EMP and DEPT tables. For collections, I'm using Oracle's built-in types.

These are employees in departments 10 and 20:

SQL> select deptno, ename
  2  from emp
  3  where deptno in (10, 20)
  4  order by deptno, ename;

    DEPTNO ENAME
---------- ----------
        10 CLARK
        10 KING
        10 MILLER
        20 ADAMS
        20 FORD
        20 JONES
        20 SCOTT
        20 SMITH

8 rows selected.

PL/SQL procedure which does something with them (the way I understood the question):

SQL> declare
  2    l_a sys.odcinumberlist;
  3    l_b sys.odcivarchar2list;
  4  begin
  5    select deptno
  6      bulk collect into l_a
  7      from dept
  8      where deptno in (10, 20);
  9
 10    select ename
 11      bulk collect into l_b
 12      from emp
 13      where deptno in (select * from table(l_a))
 14      order by ename;
 15
 16    for i in l_b.first .. l_b.last loop
 17      dbms_output.put_line(l_b(i));
 18    end loop;
 19  end;
 20  /
ADAMS
CLARK
FORD
JONES
KING
MILLER
SCOTT
SMITH

PL/SQL procedure successfully completed.

SQL>

• Lines #1 - 3 - declaration section
• lines #5 - 8 - inserting values (departments) into l_a collection
• Lines #10 - 14 - inserting values (employees) into l_b collection, based on values stored in l_a
• Lines #16 - 18 - displaying contents of l_b

See if it helps.

---

[EDIT] After seeing your comment: as far as I can tell, you can't do what you wanted, not as simple as you'd want it to. This is how it works - you enter a comma-separated values as a parameter (that's your "array"), split it into rows and use the result as a subquery:

SQL> SELECT *
  2    FROM dept
  3   WHERE deptno IN (    SELECT REGEXP_SUBSTR ( '&&par_depts',
  4                                              '[^,]+',
  5                                              1,
  6                                              LEVEL)
  7                          FROM DUAL
  8                    CONNECT BY LEVEL <= REGEXP_COUNT ( '&&par_depts', ',') + 1);
Enter value for par_depts: 10,20

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS

SQL>

This is a SQL*Plus example; you'll probably have to substitute '&&par_depts' with :par_depts (depending on a tool you use).

Answer 2

Use the MEMBER operator.

First create a collection type in SQL:

CREATE TYPE int_list IS TABLE OF INT;

Then just use it in an SQL statement:

SELECT *
FROM   my_table
WHERE  value MEMBER OF int_list(123455, 123456, 123457);

Which, for the sample data:

CREATE TABLE my_table ( id, value ) AS
SELECT LEVEL, 123453 + LEVEL FROM DUAL CONNECT BY LEVEL <= 5;

Outputs:

ID	VALUE
2	123455
3	123456
4	123457

---

If you want it in PL/SQL then:

DECLARE
  items int_list := int_list(123455, 123456, 123457);
BEGIN
  FOR row IN (
               SELECT *
               FROM   my_table
               WHERE  value MEMBER OF items
             )
  LOOP
    DBMS_OUTPUT.PUT_LINE( row.id || ', ' || row.value );
  END LOOP;
END;
/

Which, for the same data, outputs:

2, 123455
3, 123456
4, 123457

db<>fiddle here

---

However, if you just want to connect two databases then setup a database link.