This article shows simple method to duplicate records based on value in a column.

For example following query returns 3 records

select 'a' letter, 2 num from dual
union all
select 'b', 4 from dual
union all
select 'c', 4 from dual

LETTER        NUM
------ ----------
a               2 
b               4 
c               4

to duplicate records based on column NUM you can do following transformation using CONNECT BY

with a as (
select 'a' letter, 2 num from dual
union all
select 'b', 4 from dual
union all
select 'c', 4 from dual
)
select a.*, b.column_value 
from a,
     table(cast(multiset(
                 select level from dual
                 connect by level <= num ) 
           as sys.odciNumberList)) b;

LETTER        NUM COLUMN_VALUE
------ ---------- ------------
a               2            1 
a               2            2 
b               4            1 
b               4            2 
b               4            3 
b               4            4 
c               4            1 
c               4            2 
c               4            3 
c               4            4

You can use above method CONNECT BY to transform complex string (comma separated string) in a column into multiple records and simple string(split based on comma)

select 'a,b,c' letter from dual
union all
select 'g,h' from dual
union all
select 'm,k,l' from dual

LETTER
------
a,b,c  
g,h    
m,k,l

and now simple

with a as (                         
select 'a,b,c' letter from dual
union all
select 'g,h' from dual
union all
select 'm,k,l' from dual
)
select a.*, b.column_value 
  from a,
     table(cast(multiset(
                  select regexp_substr(letter,'[^,]+',1,level) 
                    from dual
                  connect by level <= regexp_count(letter, ',')+1)
           as sys.odciVarchar2List)) b;

LETTER COLUMN_VALUE    
------ ----------------
a,b,c  a               
a,b,c  b               
a,b,c  c               
g,h    g               
g,h    h               
m,k,l  m               
m,k,l  k               
m,k,l  l

Have a fun 🙂
Tomasz

If you want to add RELY DISABLE constraint to child table that references a master table with primary key constraint with NORELY flag you can encounter following error:

ORA-25158: Cannot specify RELY for foreign key if the associated primary key is NORELY

Example:

create table master_tbl
(id number primary key);

select constraint_name, constraint_type, table_name, rely
from user_constraints
where table_name='MASTER_TBL';

CONSTRAINT_NAME     CONSTRAINT_TYPE TABLE_NAME    RELY
------------------- --------------- ------------- ----
SYS_C0012428928                   P MASTER_TBL        
create table child_tbl
(id number);

alter table child_tbl add constraint child_tbl_fk
foreign key (id) references master_tbl rely disable;

ORA-25158: Cannot specify RELY for foreign key 
           if the associated primary key is NORELY

First option is to modify primary key constraint of table master_tbl to RELY but there is another workaround – just add DISABLE RELY constraint in two steps to child_tbl.

alter table child_tbl add constraint child_tbl_fk
foreign key (id) references master_tbl disable;

alter table child_tbl
modify constraint child_tbl_fk rely;

select constraint_name, constraint_type, table_name, rely
from user_constraints
where table_name in ('MASTER_TBL', 'CHILD_TBL');

CONSTRAINT_NAME   CONSTRAINT_TYPE TABLE_NAME     RELY
----------------- --------------- -------------- ----
CHILD_TBL_FK      R               CHILD_TBL      RELY
SYS_C0012428928   P               MASTER_TBL

Have a fun 🙂

Tomasz