Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using Conditions with Bulk Collect, For All and Insert All

Cloud Nines BlissFeb 14 2011 — edited Feb 14 2011
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!
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 14 2011
Added on Feb 14 2011
6 comments
1,145 views