Forum Stats

  • 3,874,283 Users
  • 2,266,718 Discussions
  • 7,911,798 Comments

Discussions

generate_series() function

User_1871
User_1871 Member Posts: 247 Red Ribbon

Could Oracle create an OOTB generate_series() function? https://www.citusdata.com/blog/2018/03/14/fun-with-sql-generate-sql/

Example: https://stackoverflow.com/questions/72059481/generate-rows-from-string-of-numbers. I can't easily solve that problem -- without making things complicated with CROSS JOINS or CONNECT BY. Having an OOTB generate_series() function would make things simpler.

User_1871KayK
2 votes

Active · Last Updated

Comments

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,941 Red Diamond
    edited Apr 29, 2022 4:04PM

    And what CREATE TYPE has to do with generating series of numbers? And how generate_series would help eample in link you posted:

    with sample as (
                    select '((0 5 0, 10 10 11.18, 30 0 33.54),(50 10 33.54, 60 10 43.54))' as multipart_lines
                            --There are more rows in the actual table.
                      from  dual
                   )
    select  part_num,
            vertex_num,
            to_number(regexp_substr(vertex,'[^ ]+')) x,
            to_number(regexp_substr(vertex,'[^ ]+',1,2)) y,
            to_number(regexp_substr(vertex,'[^ ]+',1,3)) z
      from  sample,
            lateral(
                    select  level part_num,
                            regexp_substr(multipart_lines,'\(([^()]+)',1,level,null,1) part
                      from  dual
                      connect by level < regexp_count(multipart_lines,'\(')
                   ),
            lateral(
                    select  level vertex_num,
                            regexp_substr(part,'[^,]+',1,level) vertex
                      from  dual
                      connect by level <= regexp_count(part,',') + 1
                   )
    /
    
      PART_NUM VERTEX_NUM          X          Y          Z
    ---------- ---------- ---------- ---------- ----------
             1          1          0          5          0
             1          2         10         10      11.18
             1          3         30          0      33.54
             2          1         50         10      33.54
             2          2         60         10      43.54
    
    SQL>
    

    SY.

    User_1871
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,941 Red Diamond

    and if you are on 19C:

    create or replace
      function split_multipart_line(
                                    p_line varchar2
                                   )
        return varchar2
        sql_macro
        is
        begin
            return q'[
    select  part_num,
            vertex_num,
            to_number(regexp_substr(vertex,'[^ ]+')) x,
            to_number(regexp_substr(vertex,'[^ ]+',1,2)) y,
            to_number(regexp_substr(vertex,'[^ ]+',1,3)) z
      from  dual,
            lateral(
                    select  level part_num,
                            regexp_substr(p_line,'\(([^()]+)',1,level,null,1) part
                      from  dual
                      connect by level < regexp_count(p_line,'\(')
                   ),
            lateral(
                    select  level vertex_num,
                            regexp_substr(part,'[^,]+',1,level) vertex
                      from  dual
                      connect by level <= regexp_count(part,',') + 1
                   )]';
    end;
    /
    
    Function created.
    
    SQL> with sample as (
      2                  select '((0 5 0, 10 10 11.18, 30 0 33.54),(50 10 33.54, 60 10 43.54))' multipart_lines from dual union all
      3                  select '((1 2 3, 4 5 6, 7 8 9, 10 11 12),(22 33 44, 55 66 77))' multipart_lines from dual
      4                 )
      5  select  l.*
      6    from  sample,
      7          lateral(
      8                  select  *
      9                    from  split_multipart_line(multipart_lines)
     10                 ) l
     11  /
    
    
      PART_NUM VERTEX_NUM          X          Y          Z
    ---------- ---------- ---------- ---------- ----------
             1          1          0          5          0
             1          2         10         10      11.18
             1          3         30          0      33.54
             2          1         50         10      33.54
             2          2         60         10      43.54
             1          1          1          2          3
             1          2          4          5          6
             1          3          7          8          9
             1          4         10         11         12
             2          1         22         33         44
             2          2         55         66         77
    
    
    11 rows selected.
    
    SQL>
    

    SY.

    User_1871
  • Mike Kutz
    Mike Kutz Member Posts: 6,253 Gold Crown

    19c+ code. Its also on GitHub

    package generate_series
    authid definer
    as
        /* package of SQL_MACROS for generating series of values
        *
        * @headcom
        */
    
        /* Generates a series of integers.
        *
        * @param start_value initial integer
        * @param n The number of row to generate
        * @return Series of integers
        */
        function of_numbers( start_value in int, n in int ) return varchar2 SQL_MACRO(TABLE);
    
        /* Generates a series of integers.
        *
        * @param start_value initial date
        * @param n The number of row to generate
        * @return Series of Dates
        */
        function of_dates( start_value in date, n in int ) return varchar2 SQL_MACRO(table);
    end;
    /
    
    create or replace
    package body generate_series
    as
        function of_numbers( start_value in int, n in int ) return varchar2 SQL_MACRO(TABLE)
        as
        begin
            return q'[select of_numbers.start_value + (level - 1) number_series
            from dual
            connect by level <= of_numbers.n]';
        end;
    
        function of_dates( start_value in date, n in int ) return varchar2 SQL_MACRO(table)
        as
        begin
            return q'[select of_dates.start_value + (level - 1) date_series
            from dual
            connect by level <= of_dates.n]';
        end;
    end;
    /
    
    


    User_1871