Getting Ranges from a series of numbers from a table and storing all ranges in a string variable in PLSQL/Oracle Forms

I have a table containing a series of numbers 1,2,3,4,5,11,12,13,14,15,101,102,103,104,105,510,511,512,513,515,516,517. I want an PL/SQL Function so that I can get the ranges and then store all all the ranges in a single string variable in the following format. "1-5, 11-15, 101-105, 510-517".

I have get a code to generate the rages in SQL*Plus, but it did not work in PL/SQL and forms. The procedure is given below:

  SQL> SET SERVEROUTPUT ON
  SQL> DECLARE
    2    v_list VARCHAR2(100);
    3  BEGIN
    4    SELECT listagg(RANGE, ',') WITHIN GROUP(
    5    ORDER BY min_num)
    6    INTO v_list
    7    FROM
    8      (SELECT MIN(num) min_num,
    9        MIN(num)
   10        ||'-'
   11        || MAX(num) range
   12      FROM
   13        (SELECT num, num-Row_Number() over(order by num) AS rn FROM t
   14        )
   15      GROUP BY rn
   16      );
   17    dbms_output.put_line(v_list);
   18  END;
   19  /
  1-3,5-7,10-12,20-20

Solution

As mentioned here, since the output is a list of strings, you could declare and store the output in a varchar2 variable.

You could create a procedure and put the entire logic in it.

For example,

Setup

SQL> CREATE TABLE t AS
  2  SELECT *
  3  FROM
  4    ( WITH data(num) AS
  5    ( SELECT 1 FROM dual
  6    UNION
  7    SELECT 2 FROM dual
  8    UNION
  9    SELECT 3 FROM dual
 10    UNION
 11    SELECT 5 FROM dual
 12    UNION
 13    SELECT 6 FROM dual
 14    UNION
 15    SELECT 7 FROM dual
 16    UNION
 17    SELECT 10 FROM dual
 18    UNION
 19    SELECT 11 FROM dual
 20    UNION
 21    SELECT 12 FROM dual
 22    UNION
 23    SELECT 20 FROM dual
 24    )
 25  SELECT * FROM DATA);

Table created.

Procedure

SQL> CREATE OR REPLACE
  2  PROCEDURE p_get_list
  3  AS
  4    v_list VARCHAR2(100);
  5  BEGIN
  6    SELECT listagg(RANGE, ',') WITHIN GROUP(
  7    ORDER BY min_num)
  8    INTO v_list
  9    FROM
 10      (SELECT MIN(num) min_num,
 11        MIN(num)
 12        ||'-'
 13        || MAX(num) range
 14      FROM
 15        (SELECT num, num-Row_Number() over(order by num) AS rn FROM t
 16        )
 17      GROUP BY rn
 18      );
 19    dbms_output.put_line(v_list);
 20  END;
 21  /

Procedure created.

Test case

SQL> SET SERVEROUTPUT ON
SQL> BEGIN
  2     p_get_list;
  3  END;
  4  /
1-3,5-7,10-12,20-20

PL/SQL procedure successfully completed.

You could simply call the procedure in your Oracle Forms.