SQL> DEFINE N=3
SQL> SELECT ROWNUM 
  2  FROM ALL_OBJECTS
  3  WHERE ROWNUM <= &N
  4  /
    ROWNUM
----------
         1
         2
         3

SQL> SELECT ROWNUM 
  2  FROM ( SELECT 1 FROM DUAL GROUP BY CUBE(1,2,3,4,5,6,7,8,9,10) )
  3  WHERE ROWNUM <= &N
  4  /
    ROWNUM
----------
         1
         2
         3

SQL> SELECT * 
  2  FROM ( SELECT ROWNUM FROM DUAL CONNECT BY LEVEL <= &N )
  3  /
    ROWNUM
----------
         1
         2
         3

SQL> SELECT ROWNUM FROM DUAL CONNECT BY LEVEL <= &N;
    ROWNUM
----------
         1
         2
         3

SQL> create or replace type row_table as table of number;
  2  /

Type created.

SQL> Create or replace function gen_row (num_rows in number) 
  2  return row_table 
  3  parallel_enable pipelined is 
  4  begin 
  5    for x in 1..num_rows loop
  6      pipe row (x);
  7    end loop;
  8    return;
  9  end; 
 10  / 

Function created.

SQL> select * from table(gen_row(&N)); 
COLUMN_VALUE
------------
           1
           2
           3

3 rows selected.

SQL> define n=5
SQL> select to_number(column_value) colval
  2  from xmltable('for $i in 1 to &n return $i');

    COLVAL
----------
         1
         2
         3
         4
         5

SQL> select integer_value
  2   from   dual
  3  where  1=2
  4  model
  5  dimension by ( 0 as key )
  6  measures     ( 0 as integer_value )
  7  rules upsert ( integer_value[ for key from 1 to 5 increment 1 ] = cv(key) );

INTEGER_VALUE
-------------
            1
            2
            3
            4
            5

SQL> select val
  2  from (select 1 rn from dual)
  3  model
  4    dimension by (rn)
  5    measures (1 val)
  6  rules 
  7    iterate (&N)
  8    ( val[iteration_number] = iteration_number+1 )
  9  order by val
 10  /
       VAL
----------
         1
         2
         3
         4
         5

SQL> select * 
  2  from table (cast (multiset (select level from dual connect by level <= 5) 
  3                    as sys.OdciNumberList ) 
  4             )
  5  /
COLUMN_VALUE
------------
           1
           2
           3
           4
           5

SQL> WITH data(r) AS (
  2    SELECT 1 r FROM dual
  3    UNION ALL
  4    SELECT r+1 FROM data WHERE r < 5
  5    )
  6  SELECT r FROM data
  7  /
         R
----------
         1
         2
         3
         4
         5

SQL> def date_start = '13/11/2010'
SQL> def date_end   = '22/11/2010'
SQL> with 
  2    data as (
  3      select to_date('&date_start', 'DD/MM/YYYY') date1,
  4             to_date('&date_end',   'DD/MM/YYYY') date2
  5      from dual
  6    )
  7  select to_char(date1+level-1, 'DD/MM/YYYY') the_date
  8  from data
  9  connect by level <= date2-date1+1
 10  /
THE_DATE
----------
13/11/2010
14/11/2010
15/11/2010
16/11/2010
17/11/2010
18/11/2010
19/11/2010
20/11/2010
21/11/2010
22/11/2010

select trunc(sysdate,'YEAR') + level - 1 the_date
from dual
connect by level <= trunc(add_months(sysdate,12),'YEAR') - trunc(sysdate,'YEAR')
/

select trunc(sysdate,'MONTH') + level - 1 the_date
from dual
connect by level <= last_day(sysdate) - trunc(sysdate,'MONTH') + 1
/

select trunc(sysdate,'IW') + level - 1 the_date
from dual
connect by level <= 7
/

select trunc(add_months(sysdate, -1*level), 'month') month 
from dual 
connect by level <= 6
/

-- Setup
create table t (id integer, start_date date, end_date date);
insert into t 
select level, sysdate-dbms_random.value(0,5), sysdate+dbms_random.value(0,5)
from dual
connect by level <= 3
/
commit;

SQL> -- Table content
SQL> select * from t;
        ID START_DATE  END_DATE
---------- ----------- -----------
         1 18-NOV-2010 21-NOV-2010
         2 16-NOV-2010 23-NOV-2010
         3 17-NOV-2010 20-NOV-2010

3 rows selected.

SQL> -- Expanded
SQL> break on id dup skip 1
SQL> select id, column_value cur_date
  2  from t, 
  3       table (cast (multiset ( select start_date+level-1 
  4                               from dual connect by level <= end_date-start_date+1
  5                             )
  6                    as sys.odciDateList
  7                   )
  8             )
  9  /
        ID CUR_DATE
---------- -----------
         1 18-NOV-2010
         1 19-NOV-2010
         1 20-NOV-2010
         1 21-NOV-2010

         2 16-NOV-2010
         2 17-NOV-2010
         2 18-NOV-2010
         2 19-NOV-2010
         2 20-NOV-2010
         2 21-NOV-2010
         2 22-NOV-2010
         2 23-NOV-2010

         3 17-NOV-2010
         3 18-NOV-2010
         3 19-NOV-2010
         3 20-NOV-2010

SQL> with
  2    lines as (
  3      select level-1 line 
  4      from dual
  5      connect by level <= (select max(ceil(end_date-start_date)) from t)
  6    )
  7  select id, start_date+line cur_date
  8  from t, lines
  9  where line <= end_date-start_date
 10  order by 1, 2
 11  /
        ID CUR_DATE
---------- -----------
         1 18-NOV-2010
         1 19-NOV-2010
         1 20-NOV-2010
         1 21-NOV-2010

         2 16-NOV-2010
         2 17-NOV-2010
         2 18-NOV-2010
         2 19-NOV-2010
         2 20-NOV-2010
         2 21-NOV-2010
         2 22-NOV-2010
         2 23-NOV-2010

         3 17-NOV-2010
         3 18-NOV-2010
         3 19-NOV-2010
         3 20-NOV-2010

-- Setup
drop table t purge;
create table t (id integer, num integer);
insert into t 
select level, round(dbms_random.value(0,5))
from dual
connect by level <= 3
/
commit;

SQL> -- Table content
SQL> select * from t;
        ID        NUM
---------- ----------
         1          2
         2          4
         3          3

SQL> -- Expanded
SQL> break on id dup skip 1
SQL> -- Old way
SQL> with 
  2    lines as (
  3      select level cur 
  4      from dual
  5      connect by level <= ( select max(num) from t )
  6    )
  7  select id, num, cur
  8  from t, lines
  9  where cur <= num
 10  order by 1, 3
 11  /
        ID        NUM        CUR
---------- ---------- ----------
         1          2          1
         1          2          2

         2          4          1
         2          4          2
         2          4          3
         2          4          4

         3          3          1
         3          3          2
         3          3          3

SQL> -- New way
SQL> select id, num, column_value cur
  2  from t,
  3       table (cast (multiset (select level from dual connect by level <= num)
  4                    as sys.odciNumberList
  5                   )
  6             )
  7  order by 1, 3
  8  /
        ID        NUM        CUR
---------- ---------- ----------
         1          2          1
         1          2          2

         2          4          1
         2          4          2
         2          4          3
         2          4          4

         3          3          1
         3          3          2
         3          3          3

-- Setup
drop table t purge;
create table t (
  id    integer,
  col11 integer,
  col12 varchar2(5),
  col21 integer,
  col22 varchar2(5),
  col31 integer,
  col32 varchar2(5)
)
/
insert all 
into t values (1, 11, 'AAAAA', 12, 'BBBBB', 13, 'CCCCC')
into t values (2, 21, 'DDDDD', 22, 'EEEEE', 23, 'FFFFF')
select null from dual
/
commit;

SQL> -- Table content
SQL> select * from t order by id;
        ID      COL11 COL12      COL21 COL22      COL31 COL32
---------- ---------- ----- ---------- ----- ---------- -----
         1         11 AAAAA         12 BBBBB         13 CCCCC
         2         21 DDDDD         22 EEEEE         23 FFFFF

SQL> -- Columns to rows conversion
SQL> break on id dup skip 1
SQL> with 
  2    lines as (select level line from dual connect by level <= 3) -- row generator
  3  select id, line, 
  4         decode(line, 1, col11, 2, col21, 3, col31) col1,
  5         decode(line, 1, col12, 2, col22, 3, col32) col2
  6  from t, lines
  7  order by id, line
  8  /
        ID       LINE       COL1 COL2
---------- ---------- ---------- -----
         1          1         11 AAAAA
         1          2         12 BBBBB
         1          3         13 CCCCC

         2          1         21 DDDDD
         2          2         22 EEEEE
         2          3         23 FFFFF

devtest@ Test.DB> create table t (id integer, start_date date, end_date date);

Table created.

devtest@ Test.DB> insert into t 
  2  select level, sysdate-dbms_random.value(0,5), sysdate+dbms_random.value(0,5)
  3  from dual
  4  connect by level <= 3
  5  /

3 rows created.

devtest@ Test.DB> commit;

Commit complete.
devtest@ Test.DB> select * from t order by 1;

       ID START_DAT END_DATE
--------- --------- ---------
        1 20-OCT-11 26-OCT-11
        2 16-OCT-11 24-OCT-11
        3 19-OCT-11 25-OCT-11

3 rows selected.

devtest@ Test.DB> with
  2     lines as (
  3               select level-1 line
  4                 from dual
  5              connect by level <= (select max(ceil(end_date-start_date)) from t)
  6              )
  7  select id, start_date+line cur_date
  8    from t, lines
  9   where line <= end_date-start_date
 10   order by 1, 2
 11  .
devtest@ Test.DB> break on id dup skip 1
devtest@ Test.DB> /

       ID CUR_DATE
--------- ---------
        1 20-OCT-11
        1 21-OCT-11
        1 22-OCT-11
        1 23-OCT-11
        1 24-OCT-11
        1 25-OCT-11

        2 16-OCT-11
        2 17-OCT-11
        2 18-OCT-11
        2 19-OCT-11
        2 20-OCT-11
        2 21-OCT-11
        2 22-OCT-11
        2 23-OCT-11
        2 24-OCT-11

        3 19-OCT-11
        3 20-OCT-11
        3 21-OCT-11
        3 22-OCT-11
        3 23-OCT-11
        3 24-OCT-11


21 rows selected.

devtest@ Test.DB> 

Create table a ( Objectid number, Value varchar2(2000));
Insert into a values (12, '2,3,4');
Insert into a values (13, '8,7,4');
Insert into a values (14, '3,8,9');
Insert into a values (15, '6,3,11');

SQL> break on objectid dup skip 1
SQL> col value format a10
SQL> With data as (select objectid, ','||value||',' value from a)
  2  select objectid, column_value value_nb,
  3         substr(value,
  4                instr(value, ',', 1, column_value)+1,
  5                instr(value, ',', 1, column_value+1)-instr(value, ',', 1, column_value)-1
  6               ) value
  7  from data,
  8       table(cast(multiset(select level from dual 
  9                           connect by level < length(value)-length(replace(value,',')))
 10             as sys.odciNumberList))
 11  order by 1, 2
 12  /
  OBJECTID   VALUE_NB VALUE
---------- ---------- ----------
        12          1 2
        12          2 3
        12          3 4

        13          1 8
        13          2 7
        13          3 4

        14          1 3
        14          2 8
        14          3 9

        15          1 6
        15          2 3
        15          3 11

SQL> break on objectid dup skip 1
SQL> col value format a10
SQL> select objectid, column_value value_nb,
  2         regexp_substr(value, '[^,]+', 1, column_value) value
  3  from a,
  4       table(cast(multiset(select level from dual
  5                           connect by level <= regexp_count(value,',')+1)
  6             as sys.odciNumberList))
  7  order by 1, 2
  8  /

  OBJECTID   VALUE_NB VALUE
---------- ---------- ----------
        12          1 2
        12          2 3
        12          3 4

        13          1 8
        13          2 7
        13          3 4

        14          1 3
        14          2 8
        14          3 9

        15          1 6
        15          2 3
        15          3 11

SQL> break on objectid dup skip 1
SQL> col value format a10
SQL> select objectid, column_value value_nb,
  2         regexp_substr(value, '[^,]+', 1, column_value) value
  3  from a,
  4       table(cast(multiset(select level from dual
  5                           connect by regexp_substr(value, '[^,]+', 1, level) is not null)
  6             as sys.odciNumberList))
  7  order by 1, 2
  8  /
  OBJECTID   VALUE_NB VALUE
---------- ---------- ----------
        12          1 2
        12          2 3
        12          3 4

        13          1 8
        13          2 7
        13          3 4

        14          1 3
        14          2 8
        14          3 9

        15          1 6
        15          2 3
        15          3 11

SQL> select * from xmltable('&1 to &2' columns i int path '.');
Enter value for 1: 10
Enter value for 2: 15

         I
----------
        10
        11
        12
        13
        14
        15

6 rows selected.

SQL> select * from xmltable('1 to &1' columns i for ordinality);
Enter value for 1: 5

         I
----------
         1
         2
         3
         4
         5

SQL> l
  1  select rownum
  2  from table(powermultiset(sys.ku$_objnumset(1,2,3,4,5,6,7,8,9,10)))
  3* where rownum<=10
SQL> /

    ROWNUM
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

Elapsed: 00:00:00.00

drop table test;

create table test
(
	father	varchar2(10)	,
	son   	varchar2(10)
)
;


insert into test values ('A', 'B');
insert into test values ('A', 'C');

insert into test values ('B', 'D');
insert into test values ('B', 'E');

insert into test values ('C', 'F');
insert into test values ('C', 'G');
insert into test values ('C', 'H');

insert into test values ('E', 'I');

          A
       /     \
      B       C
    /   \   / | \
    D    E  F  G  H
         |
         I

column my_path format A10
column father format A2
column son format A2
column nb format 99

SELECT DISTINCT father, son
FROM ( SELECT substr(my_path, 
                     instr(my_path, '/', 1, 1)+1, 
                     instr(my_path, '/', 1, 2)-instr(my_path, '/', 1, 1)-1) 
                father,
              substr(my_path, 
                     instr(my_path, '/', 1, column_value)+1, 
                     instr(my_path, '/', 1, column_value+1)
                     - instr(my_path, '/', 1, column_value)-1) 
                son,
              my_path, my_level + 1 my_level
      FROM ( SELECT sys_connect_by_path(father,'/') || '/' || son || '/' my_path,
                    LEVEL my_level
             FROM test
             CONNECT BY PRIOR son = father ), 
           table(cast(multiset(SELECT LEVEL FROM dual CONNECT BY LEVEL <= my_level + 1) 
                      AS sys.odciNumberList))
      ORDER BY 2 )
WHERE father <> son
ORDER BY 1, 2
/

FA SO
-- --
A  B
A  C
A  D
A  E
A  F
A  G
A  H
A  I
B  D
B  E
B  I
C  F
C  G
C  H
E  I

15 ligne(s) sélectionnée(s).

SQL> select connect_by_root father father, son
  2  from test
  3  connect by prior son = father
  4  order by 1, 2
  5  /
FATHER     SON
---------- ----------
A          B
A          C
A          D
A          E
A          F
A          G
A          H
A          I
B          D
B          E
B          I
C          F
C          G
C          H
E          I

15 rows selected.

SQL> with
  2    data as (
  3      select substr(sys_connect_by_path(father,',')||',',2) father, son
  4      from test
  5      connect by prior son = father
  6    )
  7  select substr(father, 1, instr(father,',')-1) father, son
  8  from data
  9  order by 1, 2
 10  /
FATHER               SON
-------------------- ----------
A                    B
A                    C
A                    D
A                    E
A                    F
A                    G
A                    H
A                    I
B                    D
B                    E
B                    I
C                    F
C                    G
C                    H
E                    I

with t as (
          select 5 a from dual
          union all
          select 2 from dual
)
select a,b
from t
    ,lateral(select level b from dual connect by level<=a)

SQL> -- Setup
SQL> DROP TABLE t PURGE;

Table dropped.

SQL> CREATE TABLE t
  2    (id INTEGER, num INTEGER
  3    );

Table created.

SQL> INSERT INTO t
  2  SELECT level,
  3    ROUND(dbms_random.value(0,5))
  4  FROM dual
  5    CONNECT BY level <= 3
  6  /

3 rows created.

SQL> COMMIT;

Commit complete.

SQL>

SQL> -- Table content
SQL> SELECT * FROM t;

        ID        NUM
---------- ----------
         1          3
         2          2
         3          1

SQL>

SQL> SELECT id,
  2    num,
  3    level cur
  4  FROM t
  5    CONNECT BY LEVEL          <= num
  6  AND PRIOR ID                 = ID
  7  AND PRIOR dbms_random.VALUE IS NOT NULL
  8  ORDER BY 1,3
  9  /

        ID        NUM        CUR
---------- ---------- ----------
         1          3          1
         1          3          2
         1          3          3
         2          2          1
         2          2          2
         3          1          1

6 rows selected.

SQL>

SELECT *
FROM JSON_TABLE(
  '['||REPLACE(LPAD(1,6-1,1),1,'1,')||1||']',
  '$[*]'
  COLUMNS(
    n for ordinality, 
    x NUMBER PATH'$'));


         N          X
---------- ----------
         1          1
         2          1
         3          1
         4          1
         5          1
         6          1

{"x":1, "y":2}

SELECT *
FROM JSON_TABLE(
  '{"x":1, "y":2}', 
  '$' 
  COLUMNS(x NUMBER PATH '$.x')
);
X
-
1

SELECT*
FROM JSON_TABLE(
  '[{"x":1}, {"x":2}]', 
  '$[*]' 
  COLUMNS(x NUMBER PATH'$.x'));
X
-
1
2

SELECT*
FROM JSON_TABLE('[1,2]', '$[*]' 
  COLUMNS(x NUMBER PATH'$'));
X
-
1
2

SELECT LPAD(1,5,1) STR FROM DUAL;
STR
-----
11111

SELECT REPLACE(LPAD(1,5,1),1,'1,') STR FROM DUAL;
STR
----------
1,1,1,1,1,

SELECT n*x FROM JSON_TABLE('['||REPLACE(LPAD(1,5,1),1,'1,')||1||']','$[*]'COLUMNS(n for ordinality, x NUMBER PATH'$'));
       N*X
----------
         1
         2
         3
         4
         5
         6

SELECT n FROM JSON_TABLE('['||REPLACE(LPAD(1,5,1),1,'1,')||1||']','$[*]'COLUMNS(n for ordinality, x NUMBER PATH'$'))
              *
Error at line 1
ORA-40497: only one ordinality column permitted