How to insert multiple rows from one column?

Question

I want to insert multiple rows from one column by splitting column value. But I have to do that without cursors because of performance issues.

Every value is splitted to 6 chars length values. Then these values also splitted to 3, 1 and 2 chars length values to insert different columns in table B.

I think giving a sample will clarify my question:

Table A

ID      Value
1       ABCDEFGHJKLM
2       NOPRST
3       NULL VALUE

I want to insert these values into table B like this format

Table B

ID     Value1       Value2       Value3
1       ABC          D            EF
1       GHJ          K            LM
2       NOP          R            ST

Answer 1

Supposing 600(100 rows) as maximum length of value:

insert into tableB
select id, substr(value,n*6+1,3), substr(value,n*6+4,1), substr(value,n*6+5,2)
from tableA 
     join (select level-1 as n from dual connect by level <= 100)
       on length(value) > n*6;

see Sqlfiddle.

Answer 2

select ID,
    SUBSTR(value,number*6+1,3),
    SUBSTR(value,number*6+4,1),
    SUBSTR(value,number*6+5,2)
from yourtable,
    (select 0  as number union select 1 union select 2 union select 3 union select 4 
            union  select 5 union select 6) as numbers 
      /* etc up to the max length of your string /6 */
where LEN(value)>number*6