Oracle – Create a function that has the system date as a default parameter that returns all rows meeting a certain condition on the parameter

In oracle I have a table that has multiple columns, two of which are date columns. I want to create a parameterized function where given an input date I would like to return all the rows where the input date is between the two date columns. In addition, if the user doesn’t pass an input – I have to use system date as the default parameter.

I’ve tried to create a pipelined function to do this, however when I try to use an if condition such as

create or replace function abc (v_date in date) return table_type pipelined;
begin 
if v_date is null then
 for row in (select a, b, c from abc where sysdate between abc.date1, abc.date2) LOOP
 pipe row(table_type(a, b, c));
else
 for row in (select a, b, c from abc where v_date between abc.date1, abc.date2) LOOP
 pipe row(table_type(a, b, c));
end if;
end loop;
return;

I’ve tried variations of this but I keep getting errors like – found else, expecting end.

>Solution :

No need for IF when you have NVL… you do however need to fix the BETWEEN operator which expects an AND, not a comma.

create or replace function abc (v_date in date) return table_type pipelined;
begin 
  for row in (select a, b, c from abc where NVL(v_date,sysdate) between abc.date1 AND abc.date2)
  loop
    pipe row(table_type(a, b, c));
  end loop;
  return;
end;