Creating a user defined function that returns a rowset - pipeline or reference cursor?

Hi,

I need to create a UDF that returns a resultset. It can't just be a view because filtering occurs 'before' the resultset is returned. Its performing a match on a map table, then returning the best match based on precendence. The map table has what are effectively 'partitions'. We pick a data partition based on passed in parameters, then we match on that partition and pick the highest match.

I am struggling with the CREATE FUNCTION syntax to return a rowset to a calling query.

My reading indicates that I should use a 'pipeline' or a 'reference cursor' to return a rowset to a calling select. The whole purpose is to prvide data to cubes for reporting purposes.

My questions are:

1. Should I use a pipeline or a cursor ref?
2. Can I somehow use this TABLE%ROWTYPE thing that I've read about
3. Whats the syntax for pipeline or cursor ref - create function syntax and calling select syntax

I would prefer not to involve packages unless someone can give me a good reason.


I have posted my non functional code below which has been tacked togeher from other posts. I'm not too sure of the RETURN MapList PIPELINED IS ??? syntax.


Thanks.Start Free Trial

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:

CREATE OR REPLACE FUNCTION DB.GET_PRCMAP ( 
  pSRC_SYSTEM       IN VARCHAR,
  pMAPPING_CODE     IN VARCHAR)
  RETURN MapList PIPELINED IS ??? 
BEGIN  
 
FOR x IN
(
    SELECT
    MIN(PRC.report_codes)   KEEP (dense_rank first order by M.Precedence DESC) Report_Codes,
    MIN(M.source_code_a)            KEEP (dense_rank first order by M.Precedence DESC) source_code_a,
    MIN(M.map1)              KEEP (dense_rank first order by M.Precedence DESC) map_value_a,
    MIN(M.map2)              KEEP (dense_rank first order by M.Precedence DESC) map_value_b,
    FROM DB.REF_MAP_DATA_MAPS M
    INNER JOIN
    DB.hr_primary_report prc
    ON PRC.Primary_Report_Codes LIKE M.source_code_a || '%'
    WHERE M.src_system = pSRC_SYSTEM
    AND M.mapping_code = pMAPPING_CODE
    AND M.source_code_b IN ('DEFAULT','N/A')
    GROUP BY
    PRC.report_codes
) 
LOOP
    PIPE ROW(x);
END LOOP;
RETURN;
 
    
END;