How to "join" 2 functions in Oracle SQL where the result from one function is a param to another

Question

I have this function:

select POD
from get_error_rate_by_pod(:P26_REPORT_WEEK,:P26_ERROR_TYPE);

And I need to feed every result from the above query into the function below as param 2:

select * 
from p49_get_data(:P26_REPORT_WEEK,POD,:P26_ERROR_TYPE);

and basically "union" these results together

Can I do with with a query? Or do I need to create a function with loop + accumulator?

I can change the 2nd function if needed to accept a series of PODS, or a regex (which, I'd rather not), but I'm hoping it isn't necessary

Answer 1

From Oracle 12, you can use CROSS JOIN LATERAL:

SELECT d.*
FROM   get_error_rate_by_pod(:P26_REPORT_WEEK,:P26_ERROR_TYPE) e
       CROSS JOIN LATERAL (
         SELECT * 
         FROM   p49_get_data(:P26_REPORT_WEEK,e.POD,:P26_ERROR_TYPE)
       ) d;

or CROSS APPLY:

SELECT d.*
FROM   get_error_rate_by_pod(:P26_REPORT_WEEK,:P26_ERROR_TYPE) e
       CROSS APPLY p49_get_data(:P26_REPORT_WEEK,e.POD,:P26_ERROR_TYPE) d;

Which for the setup:

CREATE TYPE pod_obj IS OBJECT (pod NUMBER);
CREATE TYPE data_obj IS OBJECT (pod NUMBER, data NUMBER);
CREATE TYPE pod_tab IS TABLE OF pod_obj;
CREATE TYPE data_tab IS TABLE OF data_obj;

CREATE FUNCTION get_error_rate_by_pod(
  i_week NUMBER,
  i_type NUMBER
) RETURN pod_tab PIPELINED
IS
BEGIN
  PIPE ROW (pod_obj(1));
  PIPE ROW (pod_obj(2));
  PIPE ROW (pod_obj(3));
END;
/

CREATE FUNCTION p49_get_data(
  i_week NUMBER,
  i_pod  NUMBER,
  i_type NUMBER
) RETURN data_tab PIPELINED
IS
BEGIN
  PIPE ROW (data_obj(i_pod, 4));
  PIPE ROW (data_obj(i_pod, 5));
  PIPE ROW (data_obj(i_pod, 6));
END;
/

Both output:

POD	DATA
1	4
1	5
1	6
2	4
2	5
2	6
3	4
3	5
3	6

fiddle