Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

generate_series() function

User_1871Apr 29 2022 — edited Apr 29 2022

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.

Comments

Solomon Yakobson

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.

1 person found this helpful
Solomon Yakobson

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.

1 person found this helpful
Mike Kutz

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;
/
2 people found this helpful
1 - 3

Post Details

Added on Apr 29 2022
#active, #general-database-discussions-ideas
3 comments
2,866 views
2 mentions