Pass Query in Clob to Cursor (PL/SQL)

Question

I have a function that returns A clob which is actually a query. I want to call that function in another function where I want to create a cursor that will have the results of that query as it's data.

So I have this function

performance_9chartsDay(p_display_resolution,p_technology_group)

And If I call it

SELECT performance_9chartsDay(1,'OTHER') FROM DUAL;

I get the following query (clob)

'Select "Category" as "Category", less_than_5m as "<5 min", less_than_15m as "<15 min", less_than_60m as "<60 min", more_than_60m as ">60 min" ,95 as KPI  from ( Select "Category",ROUND((less_than_5m / (less_than_5m+less_than_15m+less_than_60m+more_than_60m))*100,2) AS less_than_5m,    
                      ROUND((less_than_15m / (less_than_5m+less_than_15m+less_than_60m+more_than_60m))*100,2) AS less_than_15m,    
                      ROUND((less_than_60m / (less_than_5m+less_than_15m+less_than_60m+more_than_60m))*100,2) AS less_than_60m,    
                      ROUND((more_than_60m / (less_than_5m+less_than_15m+less_than_60m+more_than_60m))*100,2) AS more_than_60m from (WITH statistics AS            
                  (SELECT /*+ materialize */               
                  *              
                  FROM                
                  (SELECT trim(TO_CHAR(TRUNC(last_upd), 'Month')) || ' ' || trim(TO_CHAR(TRUNC(last_upd), 'YYYY')) AS month ,                  
                  'Week ' || TO_CHAR(TRUNC(last_upd), 'WW') || ' (' || trim(TO_CHAR(TRUNC(last_upd), 'YYYY')) || ')'    AS week ,                  
                  TRUNC(last_upd)                    AS datum,                  
                  CASE                    
                  WHEN (completed_sec/60) <= 5                    
                  THEN '<5M'                    
                  WHEN (completed_sec/60)  > 5                    
                  AND (completed_sec /60) <= 15                    
                  THEN '<15M'                    
                  WHEN (completed_sec/60)  > 15                    
                  AND (completed_sec /60) <= 60                    
                  THEN '<60M'                    
                  WHEN (completed_sec/60) > 60                    
                  THEN '>60M'                  
                  END AS completed_in_less_than                
                  FROM ORD_LOAD                 
                  WHERE 1=1 AND trunc(last_upd) >= to_date('29.01.2023', 'dd.mm.yyyy') and technology in  ('Mobile', 'Homebox') ) pivot (COUNT(completed_in_less_than) FOR completed_in_less_than 
                IN ('<5M' less_than_5m,'<15M' less_than_15m ,'<60M' less_than_60m,'>60M' more_than_60m)))                
                SELECT  to_char(date,'dd.mm.yyyy') as "Category",SUM (less_than_5m)  AS less_than_5m,                            
                SUM (less_than_15m) AS less_than_15m,                            
                SUM (less_than_60m) AS less_than_60m,                            
                SUM (more_than_60m) AS more_than_60m                           
                from statistics                            
                group by to_char(date,'dd.mm.yyyy'))) order by to_date("Category",'dd.mm.yyyy')'

Now I have this function

FUNCTION fixMobOtherDayWeekMonth_pipe(
    p_display_resolution VARCHAR2,
    p_technology_group   VARCHAR2
    )return fixMobOtherDayWeekMonth_table PIPELINED
    
    AS
     output   fixMobOtherDayWeekMonth;
        v1       VARCHAR2 (200);
        v2       NUMBER;
        v3       NUMBER;
        v4       NUMBER;
        v5       NUMBER;
        v6       NUMBER;

    CURSOR cursor_k IS --SELECT THAT QUERY FROM THAT FUNCTION
    ....
      
    ``` 

How do I get the data from the query from that function into this cursor?

Answer 1

Look into REF CURSORs, that's what they are for. Modify performance_9chartsDay so that instead of returning a string, it opens a ref cursor with that string and returns the ref cursor.

CREATE OR REPLACE FUNCTION performance_9chartsDay (...)
  RETURN sys_refcursor
AS
  cur sys_refcursor;
BEGIN
  OPEN cur for 'SQL string here';
  RETURN cur;
END;

Then pass the result of that function into fixMobOtherDayWeekMonth_pipe through an IN variable of type sys_refcursor, and fetch from it.

CREATE OR REPLACE FUNCTION fixMobOtherDayWeekMonth_pipe(in_cursor IN sys_refcursor,....)
  RETURN fixMobOtherDayWeekMonth_table 
AS
  output   fixMobOtherDayWeekMonth;
BEGIN
  FETCH in_cursor INTO output;
  WHILE in_cursor%FOUND
  LOOP
    FETCH in_cursor INTO output;
    PIPE ROW....
  END LOOP;
END;