How to return thousands of ids from a function accepting input to a select query

Question

Create or replace function f(v_input nvarchar2) 
Return t_table
Is
v_table t_table;
BEGIN
Select id bulk collect into v_table from table1 where "USER"=v_input ;
Return v_table;
End; 
/

Select * from table2 where id in (select * from table(f(‘abc’)));

How to implement this using pipelined table functions in oracle 19.0 using limit clause with bulk collect to reduce the memory usage and improve performance?

If not pipelined functions what else can we use?

Answer 1

How to implement this using pipelined table functions in oracle 19.0 using limit clause with bulk collect to reduce the memory usage and improve performance?

A simple pipelined function is:

CREATE OR REPLACE FUNCTION f(v_input nvarchar2)
RETURN t_table PIPELINED
IS
BEGIN
  FOR n IN ( SELECT id FROM table1 WHERE "USER" = v_input )
  LOOP
    PIPE ROW (n.id);
  END LOOP;
END;
/

or if you want to use a cursor and process the ids in bulk:

CREATE OR REPLACE FUNCTION f(v_input nvarchar2)
RETURN t_table PIPELINED
IS
  CURSOR v_cur IS
    SELECT id
    FROM    table1
    WHERE   "USER" = v_input;
  v_table t_table;
BEGIN
  OPEN v_cur;
  LOOP
    FETCH v_cur BULK COLLECT INTO v_table LIMIT 100;
    FOR i IN 1 .. v_table.COUNT LOOP
      PIPE ROW (v_table(i));
    END LOOP;
    EXIT WHEN v_cur%NOTFOUND;
  END LOOP;
  CLOSE v_cur;
END;
/

Then you can use:

SELECT *
FROM   table2
WHERE  id MEMBER OF f('abc');

db<>fiddle here

Answer 2

Pipeline functions are not so good for your purposes: extra context switches (SQL <-> PL/SQL), row-by-row pipe, extra user-defined type, etc.

Since you are on Oracle 19, I'd suggest you to try SQL Table Macros (19.6+):

Simple example: DBFiddle

Create or replace function f(v_input nvarchar2)
  return varchar2 sql_macro(table)
is
begin
  return 'Select id from table1 where "USER"=v_input';
end;
/

then you can use it as a simple table in your queries:

Select * 
from f('user1') ff 
     join table2 t2 
          on t2.id=ff.id;