I don't think that it's a problem if you're not taking those rowid's and storing them somewhere, then expecting to come back the next day and use them. Is there a reason for using a cursor and not just running an update statement directly?
I really appreciate your help.
Please look at the code below. How can I replace the code below in to direct update then retriveing data and process, then update multiple rows for combination of three column in where clause.
The loop is going backword. as cursor in desending. Values are calculated with formula for multiple rows one by one.
One SKU, MULTIPLE SOTORE for one SKU and UNIQUE YEARWEEKKEY FOR EACH SKU AND SOTRE.
so if i pass one SKU AND SORE it WILL update atlear 10 to 50 rows for each COMBINATION OF SKU and STORE depending upon how many week. max in 50 week.
Please Please guide me.
Procedure SP_930_End_Of_Week_OH_up (
pSKU In SS_SKU_Store_Week.SKU%Type,
pStore In SS_SKU_Store_Week.Store_Num%Type) Is
Cursor OpportunityRating_Cursor Is
SELECT /*+ index (SS_SKU_Store_Week
SS_SKU_STR_WK_PK )*/ Opportunity_Rating,
Decode(PSW_Flag, 0, 0, Distributed_Planned_Sales) DistributedPlannedSales
WHERE SKU = pSKU AND
Store_Num = pStore
ORDER BY Year_Week_Key Desc;
FETCH OpportunityRating_Cursor bulk collect into
vOpertunityRating,vOnHand, vdistributedSales limit 200 ;
FOR i in vsku.first..vsku.last LOOP
IF vOpertunityRating(i) Is Not Null Then
when 0 then 1
else Round(vOnHand(i)/ vForwardSales, 4)
vForwardSales := 0;
vForwardSales := vForwardSales + vdistributedSales(i); END LOOP;;
FORALL i in 1..vOpRat.count
update /*+ index (SS_SKU_Store_Week SS_SKU_STR_WK_PK )*/SS_SKU_Store_Week
SET Opportunity_Rating = vOpRat(i)
Where sku =vsku_1(i) and
store_num =vstore_1(i) and
EXIT WHEN OpportunityRating_Cursor%NOTFOUND;
When OTHERS Then
Last edited by devmiral; 01-11-2007 at 09:21 AM.
Can you instead give us a table structure (create table ...) + some sample data for before and after the execution of the procedure? It looks like you're calculating a moving average or sum, and that might be amenable to just calculating on the fly with an analytic function