[SOLVED] Generate sequence in Oracle based on length of column

Issue

I need to generate sequence in Oracle based on length of column and incremental of 2.

For example:
Select comp_name from table_a
Output: COGNIZANT

So I need to generate sequence:

Generate_sequence ( min number , length (comp_name), incremental)

Meaning: sequence min val 1 , max value 9 and incremental of 2
i.e (1, 9,2)

Here 9 because our output is cognizant whose length is 9

Output from sequence geneated should be

1
3
5
7
9

Solution

You can create the user-defined function:

CREATE FUNCTION generate_series(
  i_start IN NUMBER,
  i_end   IN NUMBER,
  i_step  IN NUMBER DEFAULT 1
) RETURN SYS.ODCINUMBERLIST PIPELINED DETERMINISTIC
IS
  v_steps CONSTANT PLS_INTEGER := FLOOR((i_end - i_start)/i_step);
BEGIN
  FOR step_num IN 0 .. v_steps LOOP
    PIPE ROW (i_start + i_step * step_num);
  END LOOP;
END;
/

Then use it in a table collection expression:

SELECT *
FROM   TABLE(generate_series(1,3,0.7));

Which outputs:

COLUMN_VALUE
1
1.7
2.4

---

If you have the table:

CREATE TABLE table_a (comp_name) AS
SELECT 'COGNIZANT' FROM DUAL UNION ALL
SELECT 'ABCD' FROM DUAL;

Then:

SELECT comp_name,
       s.column_value
FROM   table_a
       CROSS JOIN TABLE(generate_series(1, LENGTH(comp_name), 2)) s

Outputs:

COMP_NAME	COLUMN_VALUE
COGNIZANT	1
COGNIZANT	3
COGNIZANT	5
COGNIZANT	7
COGNIZANT	9
ABCD	1
ABCD	3

db<>fiddle here

Answered By – MT0

Answer Checked By – Clifford M. (BugsFixing Volunteer)