DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: ROWID in Where clause

  1. #1
    Join Date
    Jan 2007
    Posts
    13

    ROWID in Where clause

    sorry
    Last edited by devmiral; 10-25-2007 at 04:57 PM.

  2. #2
    Join Date
    Feb 2005
    Posts
    158
    A ROWID can change (eg with an ALTER TABLE SHRINK) so I would advise against it unless performance is ABSOLUTELY and OVERRIDINGLY critical.

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Jan 2007
    Posts
    13

    ROWID in Where clause

    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.

    Code:
    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
    VARIABLEs......
    
     Cursor OpportunityRating_Cursor Is
          SELECT  /*+ index (SS_SKU_Store_Week 
          SS_SKU_STR_WK_PK  )*/ Opportunity_Rating,
               End_Of_Week_On_Hand,
               Decode(PSW_Flag, 0, 0, Distributed_Planned_Sales)    DistributedPlannedSales
          FROM     SS_SKU_Store_Week
          WHERE    SKU                  = pSKU AND
                   Store_Num            = pStore
          ORDER BY Year_Week_Key Desc;
    BEGIN
    
      OPEN OpportunityRating_Cursor;
       LOOP 
            FETCH OpportunityRating_Cursor bulk collect into
                      vsku,vstore,vyearweekkey,
                      vOpertunityRating,vOnHand, vdistributedSales limit 200 ;
               FOR i in    vsku.first..vsku.last  LOOP               
                
                     IF  vOpertunityRating(i) Is Not Null Then
                                              
                           vOpRat(v_idx):=case  vforwardsales
                                     when 0 then 1
                             else  Round(vOnHand(i)/ vForwardSales, 4)
                                     end;  
                              vsku_1.extend;
                              vstore_1.extend;
                              vyearweekkey_1.extend;
                              vsku_1(v_idx)        :=vsku(i);
                              vstore_1(v_idx)      :=vstore(i);
                              vyearweekkey_1(v_idx):=vyearweekkey(i);         
                              v_idx                :=v_idx+1;
                              vForwardSales := 0;                       
                      END IF;
                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 
                                      year_week_key=vyearweekkey_1(i);
    
                EXIT WHEN OpportunityRating_Cursor%NOTFOUND;                   
       END LOOP;
          CLOSE OpportunityRating_Cursor;
         COMMITt;
     Exception
       When OTHERS Then
          Null;
    End SP_930_End_Of_Week_OH_up;
    Last edited by devmiral; 01-11-2007 at 10:21 AM. Reason: CORRECTION

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Jan 2007
    Posts
    13
    sorry
    Last edited by devmiral; 10-25-2007 at 04:58 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width