Create a Dynamic List of Values

Question

I have a function that returns an Array of codes (NUMBER).

select GET_USER_OFFICE_CODES(10) FROM DUAL;

returns

DB.OFFICE_CODES(3,4,5,6,7,8,9)

Ultimately I would like to create a LOV that uses this function in an APEX 5.1 application.

For example,

select office_code, id 
from offices where ID IN (select GET_USER_OFFICE_CODES(10) FROM DUAL);

Obviously, I am having trouble with datatypes.

Can someone help me massage the results in SQL so that I can just do the following in SQL?

select * 
from offices 
where ID IN (select GET_USER_OFFICE_CODES(10) FROM DUAL);

Answer 1

It would have helped if you described what the function exactly does. I'm now just guessing.

Function:

SQL> create or replace function f_test(par_in in number)
  2    return sys.odcinumberlist
  3  is
  4    l_arr sys.odcinumberlist := sys.odcinumberlist();
  5  begin
  6    l_arr.extend;
  7    l_arr(l_arr.last) := 10;
  8    l_arr.extend;
  9    l_arr(l_arr.last) := 20;
 10    return l_arr;
 11  end;
 12  /

Function created.

What does it return?

SQL> select f_test(10) from dual;

F_TEST(10)
--------------------------------------------------------------------------------
ODCINUMBERLIST(10, 20)

How to use it in Apex?

SQL> select deptno d,
  2         dname r
  3  from dept d
  4  where d.deptno in (select * from table(f_test(10)));

         D R
---------- --------------
        10 ACCOUNTING
        20 RESEARCH

SQL>

Answer 2

Assuming that DB.OFFICE_CODES is a collection defined in the SQL scope then you can use the MEMBER OF operator:

select * 
from offices 
where ID MEMBER OF GET_USER_OFFICE_CODES(10);

Which outputs:

ID | LOCATION  
-: | :---------
 3 | Location 3
 4 | Location 4
 5 | Location 5
 6 | Location 6
 7 | Location 7
 8 | Location 8
 9 | Location 9

Assuming your schema is called DB and this is an example of your setup:

CREATE TYPE DB.OFFICE_CODES IS TABLE OF NUMBER;

CREATE FUNCTION GET_USER_OFFICE_CODES(
  value IN INT
) RETURN DB.OFFICE_CODES
IS
BEGIN
  -- Do something to get your values.
  RETURN DB.OFFICE_CODES(3,4,5,6,7,8,9);
END;
/

Then, if you have the offices table:

CREATE TABLE offices ( id, location ) AS
SELECT LEVEL, 'Location ' || LEVEL FROM DUAL CONNECT BY LEVEL <= 10;

db<>fiddle here