0

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.

  • what version and edition of oracle? – Joe W Oct 14 at 14:45
3

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;
/
  • 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! – user2271033 Oct 15 at 10:27
-3

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

  • 2
    as for table aliases is not even valid Oracle syntax. – Balazs Papp Oct 14 at 14:33
  • 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. – Joe W Oct 14 at 15:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.