I want to create a function that will return a table as a result. Now, I have written quite a big SQL that works, but as I need to push dates trough that SQL to the beginning, I need a funtion that will do that. Now, as an example, to see if I can get it working, I've created this so far:
create or replace type test_row as object (date_id number,day date)
create or replace type test_table as table of test_row
and written this function:
create or replace FUNCTION test_function ( p_year_start_in integer, p_year_end_in integer ) RETURN test_table AS v_ret test_table; p_start date; p_end date; BEGIN select td.date_id, td.day bulk collect into v_ret from T_D_DATES td where date_id between p_year_start_in and p_year_end_in; RETURN v_ret; END test_function;
but the compiler throws an error. Specifically is says:
PL/SQL: SQL Statement ignored for the
select td.date_id, td.day part and
PL/SQL: ORA-00947: not enough values on
from T_D_DATES td
but I am not completely sure how to fix it. I've tried to make a PIPELINED function but got lost on how to insert all my values from my select into the table type.
Funtions aren't really my forte and especially ones that return a table as a result. So, how can I write a function that would work with the sample select?
P.S: I've looked online for solutions but it's either a super simple loop that writes some hardcoded values or something really complex and I can't decipher it.