Table function in oracle

Question

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)

and

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.

Answer 1

That will not work like that. test_table is a table of test_row objects, not 2 columns, so you need to create test_row objects from the 2 columns, like below:

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 test_row(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;
/

Answer 2

Can you please try to use the proper alias syntax?

from T_D_DATES as td

But I don't really see the advantage of receiving a temporary table made up of select results over JUST receiving said select results....

So in my eyes the whole test function (if you want some kind of stored procedure or similar) could be shortened