Convert For loop procedure to FORALL or Bulk Collect

Hi guys

here is the proc I have which is working fine but since there is a switch over between sql and pl/sql looks like it takes lot of time... how do i convert this into FORALL or bulk collect procedure so i can reduce talk between pl/sql and sql engine...

CREATE OR REPLACE PROCEDURE H.CMATES_Stats_Gen_test IS
          BEGIN
            FOR c IN (SELECT school_id,usernum,startyear,endyear FROM test.DAILY_C_JOINS)
        LOOP
                             
                   INSERT INTO test.new_CMATES (
                       RECIPIENT_USERNUM,
                         EMAILADDRESS,
                         FIRSTBLAME,
                         LASTBLAME,
                         SCHOOL_ID,
                         PRIMARYSCHOOL,
                         SCHOOL_BLAME,
                         RECIPIENT_VALIDATION_STATUS,
                         RECIPIENT_STARTYEAR,
                         RECIPIENT_ENDYEAR,
                         NEW_CM_STARTYEAR,
                         NEW_CM_ENDYEAR,
                         CMATE_USERNUM,
                         CMATEFIRSTBLAME,
                         CMATELASTBLAME,
                         NEW_CMATES_ALERT)
                               (SELECT watcher.usernum AS recipient_usernum,
                               watcher.EMAIL AS emailAddress,
                               watcher.firstBLAME AS firstBLAME,
                               watcher.lastBLAME AS lastBLAME,
                               watcher_aw.school_id AS school_id,
                               watcher_aw.primary_school AS primarySchool,
                               nh.BLAME AS school_BLAME,
                               hev.validation_status AS recipient_validation_status,
                               watcher_aw.startyear AS recipient_startyear,
                               watcher_aw.endyear AS recipient_endyear,
                               c.startyear AS new_cm_startyear,
                               c.endyear AS new_cm_endyear,
                               joiner.usernum AS Cmate_usernum,
                               joiner.firstBLAME AS CmateFirstBLAME,
                               NVL(joiner.maidenBLAME,joiner.lastBLAME) AS CmateLastBLAME,
                               nvl(hev.new_CMATES_alert,1) as NEW_CMATES_ALERT
                        FROM H.PDA joiner,
                              H.PDA watcher,
                              H.PDAALUMNIWATCH watcher_aw,
                              H.PDA_EMAIL_PROFILE hev,
                              H.NEW_HIGHSCHOOLS nh
                         WHERE watcher.deleted=0
                         AND   joiner.deleted=0
                                     --
                                     --AND   hev.validation_status IN (1,2,3)
                                     --AND   hev.new_CMATES_alert IS NULL OR hev.new_CMATES_alert=1
                                     --
                         AND   nh.pin=c.school_id
                         AND   hev.usernum=watcher.usernum
                         AND   joiner.gradyear BETWEEN watcher_aw.startyear AND watcher_aw.endyear
                         AND   watcher.usernum=watcher_aw.usernum
                         AND   c.school_id=watcher_aw.school_id
                         AND   joiner.usernum=c.usernum)
                         ;
                      COMMIT;
                 
         END LOOP;
      END;
/
LVL 1
anandmahajanAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
how about you skip pl/sql completely and just do it one sql statement...



INSERT INTO TEST.new_cmates
            (recipient_usernum, emailaddress, firstblame, lastblame,
             school_id, primaryschool, school_blame,
             recipient_validation_status, recipient_startyear,
             recipient_endyear, new_cm_startyear, new_cm_endyear,
             cmate_usernum, cmatefirstblame, cmatelastblame, new_cmates_alert)
    (SELECT watcher.usernum AS recipient_usernum,
            watcher.email AS emailaddress, watcher.firstblame AS firstblame,
            watcher.lastblame AS lastblame, watcher_aw.school_id AS school_id,
            watcher_aw.primary_school AS primaryschool,
            nh.blame AS school_blame,
            hev.validation_status AS recipient_validation_status,
            watcher_aw.startyear AS recipient_startyear,
            watcher_aw.endyear AS recipient_endyear,
            c.startyear AS new_cm_startyear, c.endyear AS new_cm_endyear,
            joiner.usernum AS cmate_usernum,
            joiner.firstblame AS cmatefirstblame,
            NVL(joiner.maidenblame, joiner.lastblame) AS cmatelastblame,
            NVL(hev.new_cmates_alert, 1) AS new_cmates_alert
       FROM h.pda joiner,
            h.pda watcher,
            h.pdaalumniwatch watcher_aw,
            h.pda_email_profile hev,
            h.new_highschools nh,
            (SELECT school_id, usernum, startyear, endyear
               FROM TEST.daily_c_joins) c
      WHERE watcher.deleted = 0
        AND joiner.deleted = 0
        --
        --AND   hev.validation_status IN (1,2,3)
        --AND   hev.new_CMATES_alert IS NULL OR hev.new_CMATES_alert=1
        --
        AND nh.pin = c.school_id
        AND hev.usernum = watcher.usernum
        AND joiner.gradyear BETWEEN watcher_aw.startyear AND watcher_aw.endyear
        AND watcher.usernum = watcher_aw.usernum
        AND c.school_id = watcher_aw.school_id
        AND joiner.usernum = c.usernum);


or if you must wrap it in a procedure then still skip the looping and forall construct and just

CREATE OR REPLACE PROCEDURE h.cmates_stats_gen_test
IS
BEGIN
    INSERT INTO TEST.new_cmates
                (recipient_usernum, emailaddress, firstblame, lastblame,
                 school_id, primaryschool, school_blame,
                 recipient_validation_status, recipient_startyear,
                 recipient_endyear, new_cm_startyear, new_cm_endyear,
                 cmate_usernum, cmatefirstblame, cmatelastblame,
                 new_cmates_alert)
        (SELECT watcher.usernum AS recipient_usernum,
                watcher.email AS emailaddress,
                watcher.firstblame AS firstblame,
                watcher.lastblame AS lastblame,
                watcher_aw.school_id AS school_id,
                watcher_aw.primary_school AS primaryschool,
                nh.blame AS school_blame,
                hev.validation_status AS recipient_validation_status,
                watcher_aw.startyear AS recipient_startyear,
                watcher_aw.endyear AS recipient_endyear,
                c.startyear AS new_cm_startyear, c.endyear AS new_cm_endyear,
                joiner.usernum AS cmate_usernum,
                joiner.firstblame AS cmatefirstblame,
                NVL(joiner.maidenblame, joiner.lastblame) AS cmatelastblame,
                NVL(hev.new_cmates_alert, 1) AS new_cmates_alert
           FROM h.pda joiner,
                h.pda watcher,
                h.pdaalumniwatch watcher_aw,
                h.pda_email_profile hev,
                h.new_highschools nh,
                (SELECT school_id, usernum, startyear, endyear
                   FROM TEST.daily_c_joins) c
          WHERE watcher.deleted = 0
            AND joiner.deleted = 0
            --
            --AND   hev.validation_status IN (1,2,3)
            --AND   hev.new_CMATES_alert IS NULL OR hev.new_CMATES_alert=1
            --
            AND nh.pin = c.school_id
            AND hev.usernum = watcher.usernum
            AND joiner.gradyear BETWEEN watcher_aw.startyear
                                    AND watcher_aw.endyear
            AND watcher.usernum = watcher_aw.usernum
            AND c.school_id = watcher_aw.school_id
            AND joiner.usernum = c.usernum);
END;

0
 
sdstuberCommented:
add COMMIT;  if necessary to the procedure
0
 
sdstuberCommented:
Did this answer your question?  If not, how else can I help?
0
 
anandmahajanAuthor Commented:
i dont think second solution of inserting all rows at once will work as it might need large caching memory.....but still ur solution is good
0
 
sdstuberCommented:
The second solution is the same as the first solution.
And inserting shouldn't require lots of memory caching.  Perhaps the select itself might, but that will apply to SQL outside of a pl/sql block as well as one within it.

Glad it helped though
0
All Courses

From novice to tech pro — start learning today.