Oracle Pass multiple rows or resultset of a SQL to a function as parameter

Question

Is it possible to do create a function to take in a query result and do some processing and return me a pipeline table. If it works for a normal function, i can get it to work for pipelined. Its not called from a stored proc, so don't know how to use cursor in this scenario.

select SF_PROCESS_FUNCTION(
    select 'abcd' as keyVal, 'first value' as descVal from dual
    union
    select 'efgh' as keyVal, 'second value' as descVal from dual) from dual;

Any help will be greatly appreciated. Thanks

@clinomaniac : I tried that approach, but i am not able to make it to work even from a plsql block DECLARE testTyp Z_TEST_ROW_TBL := Z_TEST_ROW_TBL(); BEGIN select * into testTyp from ( select 'abcd' as keyVal, 'first value' as descVal from dual union select 'efgh' as keyVal, 'second value' as descVal from dual ); --SF_B_TEST_FN_WTIH_TBL(testVal); DBMS_output.put_line('end:'); END; — Jugunu

Kaushik Nayak Kaushik Nayak · Accepted Answer · 2018-01-19T18:23:29

You could probably wrap the query using a CURSOR expression. Since you want it to be PIPELINED, you need to use select * FROM TABLE() syntax

select * FROM TABLE ( SF_PROCESS_FUNCTION(
CURSOR
 (
    select 'abcd' as keyVal, 'first value' as descVal from dual
    union
    select 'efgh' as keyVal, 'second value' as descVal from dual) 
) );

Your function should have a SYS_REFCURSOR as input argument.

CREATE OR REPLACE FUNCTION 
SF_PROCESS_FUNCTION (inpquery IN SYS_REFCURSOR) RETURN keydesc_typ 
    PIPELINED 
    AS
..
..

Oracle Pass multiple rows or resultset of a SQL to a function as parameter

1 Answers