It would make argument passing trivial and avoid that substr trick alltogether.
An UPDATE in the cursor loop will update 1 row and 19 columns at once.
The Update is dependent on results of 19 procedures which are quite complex.
I can not do a one hit update outside of the cursor loop.
My method of using FORALL to update many columns is by splitting a PL table of a string using SUBSTR in fixed places.
This seems a messy way of getting around the problem. COUNT UPDATE profile SET quotopurdays = LTRIM(SUBSTR(people_tab(bulk Num),0,6)), maildays = LTRIM(SUBSTR(people_tab(bulk Num),7,9)), ... What we (well, you) want to do is ARRAY fetch N records, do your thing to them, and ARRAY update N records (or less). (i don't like that substr trick, just adds overhead). The following example shows how to accomplish this.
WHERE personid = LTRIM(SUBSTR(people_tab((bulk Num),50,9); How can you use FORALL to update many rows using a PL table, Collection etc? Do you agree that FORALL is quicker than an update in the cursor loop? If you wanted, you could make the L_RECORD variable a package GLOBAL (define it in the body, not in a procedure in the body) and just pass the INDEX around from subroutine to subroutine.
That way, all of the subroutines in this package could access the i'th element of the RECORD of TABLES we have defined.
FOR profile_rec IN profile_cur LOOP Quo To Pur Days( profile_rec.personid , profile_rec. IF changed Flag = TRUE THEN UPDATE profile SET quotopurdays = profile_rec. WHERE personid = profile_rec.personid; changed Flag := FALSE; END IF; END LOOP; I tried to use the CURSOR loop to populate a PL table of profile People_rec.
I tried to use this PL table of records to so a FORALL update etc..
OPEN profile_cur FOR sql Stmt; LOOP FETCH profile_cur INTO profile_rec; EXIT WHEN profile_cur%NOTFOUND; Quo To Pur Days( profile_rec.personid , profile_rec. IF changed Flag = TRUE THEN people_tab(x).personid := profile_rec.personid; people_tab(x).quotopurdays := profile_rec.quotopurdays; ...; ...; x := x +1; changed Flag := FALSE; END IF; END LOOP; CLOSE profile_cur; FORALL bulk Num IN 1..people_tab.
COUNT UPDATE profile SET quotopurdays = people_tab(bulk Num). WHERE personid = people_tab(bulk Num); COMMIT; Oracle will not allow a FORALL update to use a PL table of records in the update as above etc..