Forum Stats

  • 3,817,244 Users
  • 2,259,294 Discussions
  • 7,893,711 Comments

Discussions

Oracle returning a cursor from a function or procedure

2»

Answers

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,957 Blue Diamond

    @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
    Jonathan Lewis Member Posts: 9,957 Blue Diamond

    @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
    Alex Nuijten Member Posts: 238 Silver Badge
    edited Jul 22, 2021 8:21AM

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


    User_WI23P
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,957 Blue Diamond
    edited Jul 22, 2021 9:21AM

    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
    BeefStu Member Posts: 322 Blue Ribbon

    @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
    Jonathan Lewis Member Posts: 9,957 Blue Diamond

    @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