Oracle returning a cursor from a function or procedure

Jonathan Lewis

@bede,

There's always the case of "horses for courses". You always pay something somewhere.

The benefit of the pipelined function is that you only write the complicated bit once, even though it costs you in row-by-row processing. You might note, though, that the OP's query had an output of 83 rows (when I ran it), which is a lot more row-by-row than the 7 rows that had to be processed in the function.

An alternative (since the OP may be on a sufficiently recent releast of 19c (19.10 might be okay, I've just tested 19.11) is the SQL Macro:

create or replace function generate_dates(
        p_from  in date,
        p_to    in date
)
return varchar2
sql_macro    -- NOTE THIS
is
        v_sql varchar2(4000);
begin
        v_sql := '
                with calendar (start_date, end_date ) as (
                        select date ''' ||
                        to_char(p_from,'yyyy-mm-dd') ||
                        ''' start_date, date ''' ||
                        to_char(p_to  ,'yyyy-mm-dd') ||
                        ''' end_date from dual ' ||
                        'union all
                        select start_date + 1, end_date
                        from   calendar
                        where  start_date + 1 <= end_date
                )
                select start_date as date_val
                from   calendar
        ';

--      dbms_output.put_line(v_sql);
        return v_sql;

end generate_dates;
/

This has the effect of allowing the complex code to be written once (and then made totally incomprehensible because of the need to make it a generated string!) and used many times. The call to the function effectively generates the correct inline text before the optimizer does a pure SQL optimisation.

(As it stands creating this function and then re-running the final SQL with "date_val" will get the correct result with no row-by-row procssing).

Regards

Jonathan Lewis

Jonathan Lewis

@BeefStu

Thanks for the feedback.

If you're running a recent release of 19c then there is another option available - the SQL Macro - which might have been Oracle Corps. response to exactly this type of problem.

Regards

Jonathan Lewis

Alex Nuijten

@Jonathan Lewis Regarding your SQL Macro example:

I don't think it's needed to format the incoming arguments and concatenate it in the SQL statement, I think you can reference the incoming arguments directly like:

create or replace function generate_dates(
    p_from in date,
    p_to  in date
)
return varchar2
sql_macro
is
    v_sql varchar2(4000);
begin
    v_sql := '
        with calendar (start_date, end_date ) as (
            select generate_dates.p_from as start_date --<-- directly referencing the argument
               ,generate_dates.p_to as end_date  --<-- 
            from dual 
            union all
            select start_date + 1, end_date
            from  calendar
            where start_date + 1 <= end_date
        )
        select start_date as date_val
        from  calendar
    ';
    return v_sql;

end generate_dates;
/

Only problem is that I don't have access to any other version than a Oracle 19.0... and in that version there is a bug where you can't use the WITH clause (Bug 32212976: USING SCALAR ARGUMENTS IN WITH CLAUSE IN SQL TABLE MACRO RAISES ORA-06553 PLS-306 )

So I had to rewrite it to not use recursive subquery factoring to "prove my point"

create or replace function generate_dates(
    p_from in date,
    p_to  in date
)
return varchar2
sql_macro  -- NOTE THIS
is
    v_sql varchar2(4000);
begin
  v_sql := 'select trunc (generate_dates.p_from) -1 + level as startdate
       from dual
       connect by level <= (generate_dates.p_to - generate_dates.p_from)';

    return v_sql;

end generate_dates;
/

Jonathan Lewis

Alex,

Thanks for that - it didn't occur to me that the problem was the WITH subquery (my testbed is 19.11.0.0), so I switched to this messy character substitution without thinking about why I was getting that ORA-06553 error. (The substitution method I used on the corresponding blog note - which is in its final draft ***, but which is now going to have an update - is a little tidier than this one.)

Regards

Jonathan Lewis

*** Update: published at https://jonathanlewis.wordpress.com/2021/07/22/sql-macro/

BeefStu

@Johnathan Lewis I modified my code to use your new function generate_dates_pipelined and it works great!!! Kudos for the excellent idea and detailed write up. 

A quick question if you don't mind. In order to make the calling code truly generic would it make sense to pull out the hard coded dates and do something like this first in order not to possibly mess up the SQL by editing it every time it needs to run.

This way the caller can always pass pipelined_start_date, pipelined_end_date. Your thoughts please. Using bind variables would require input from the user and it could not be run non interactively.

variable pipelined_start_date varchar2(30)

variable pipelined_end_date varchar2(30)

exec :pipelined_start_date := '20210722';

exec :pipelined_end_date := '20210731';

Or set to SYSDATE, SYSDATE + N

Jonathan Lewis

@BeefStu

That seems perfectly reasonable to me; the pipeline method certainly works with incoming bind variables, and it would probably have some effect of reducing total parse costs.

Regards

Jonathan Lewis