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.

• Joe W: what version and edition of oracle?

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

• user2271033: Thanks a lot, that worked, but now I have another problem. I am using with to separate smaller selects until I have what I need. So, I used what you wrote there and the function works but it doesn't populate the columns with the results, it just shows 0 instead of the proper numeric value. Not quite sure what the problem is. EDIT: nvm, figured it out. thanks again!

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

• Balazs Papp: as for table aliases is not even valid Oracle syntax.
• Joe W: While you may not agree with the need for a temporary table approach saying that does not answer the question. If you could explain why it should not be used here it might help but as there is no explanation as to what it is going to be used at it is very hard to say if it should or shouldn't be used.