Hi and Happy Valentines Day to Everyone here in OTN,
I have a question regarding the use of BULK INSERT and FORALL.
1. I have a scenario wherein I will create 4 collections based on 4 items in the same table.
TYPE item_tablea IS TABLE OF main.item%TYPE;
item_tbl item_tablea;
TYPE lvl1_tablea IS TABLE OF lvl1.item%TYPE;
lvl1_tbl lvl1_tablea;
TYPE lvl2_tablea IS TABLE OF lvl2.item%TYPE;
lvl2_tbl lvl2_tablea;
TYPE child_tablea IS TABLE OF child.item%TYPE;
child_tbl child_tablea;
2. I will then use bulk collect to insert huge amount of records to the collection.
select item,
level1,
level2,
child,
BULK COLLECT INTO item_tbl,
lvl1_tbl,
lvl2_tbl,
child_tbl
from main;
3. My Question: Does using FORALL work even if I need to use IF statement to conditionally insert items in another table? I have to insert the items in a way they are read as 1 row.
Below is the sample scenario code I'm trying to work on but not the real code.
FORALL i IN item_tbl.first .. item_tbl.last
INSERT ALL
WHEN lvl1_tbl(i) = lvl2_tbl(i) then
INTO table2
select item,lvl1,lvl2,hour
from main2
where item = item_tbl(i)
WHEN lvl1_tbl(i) < lvl2_tbl(i) then
INTO table2
select item,lvl1,lvl2,hour
from main2
where item = child_tbl(i)
Will this type of coding works? Or is there any alternatives. I'm already using the classic FOR loop that loops thru a cursor reading records one by one. I wanted to implement the forall functionality. But will condition work in FORALL?
Thanks and Have a great day to all!