Generate a table with number sequence in sql
=========
1 Pre-defined collection types in Oracle
---------
select to_number(column_value) as num from
table(sys.dbms_debug_vc2coll(1,2,3,4,5,6,7,8,9));
select to_number(column_value) as num from
table(sys.KU$_VCNT(1,2,3,4,5,6,7,8,9));
select column_value as num from
table(sys.KU$_OBJNUMSET(1,2,3,4,5,6,7,8,9));
2 Join: pure sql
---------
select a.i+b.i+c.i+d.i+1 as num
from
(select 0 i from dual union all select 1 from dual) a,
(select 0 i from dual union all select 2 from dual) b,
(select 0 i from dual union all select 4 from dual) c,
(select 0 i from dual union all select 8 from dual) d
order by num;
3 How to make number dynamic?
---------
?
3.1 Define function with collection type as return value
-----------------
It's not only sql.
And myaybe it's not correct or appropriate for dynamic.
However, I like it.
First, define a type:
create or replace type T_NUM_TAB as TABLE OF NUMBER;
Then, define the function:
CREATE OR REPLACE FUNCTION NUM_COLLECT (
NUM_START IN NUMBER,
NUM_END IN NUMBER,
NUM_STEP IN NUMBER DEFAULT 1)
RETURN T_NUM_TAB
AS
l_tab T_NUM_TAB := T_NUM_TAB();
l_idx NUMBER;
BEGIN
l_idx := NUM_START;
WHILE l_idx <= NUM_END LOOP
l_tab.extend;
l_tab(l_tab.last) := l_idx;
l_idx := l_idx + NUM_STEP;
END LOOP;
RETURN l_tab;
END;
Now, use it as follows:
SQL> select * from table(num_collect(2,10,2));
COLUMN_VALUE
------------
2
4
6
8
10
Is it a good idea to take sql string as parameter for the function?