Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2004
    Posts
    9

    Unanswered: Crit this update query Please (im really rusty)

    Does this look correct (trying to remove the rust)

    PHP Code:
    DECLARE

                
    CURSOR C1 IS
                            SELECT  SKU
    .COLOR_ID
                    S
    .STATUS,
                    
    S.ACTIVE_DATE,
                    
    S.VENDOR_ID,
                    
    S.VENDOR_ITEM_ID,
                    
    S.PRODUCT_GROUP,
                    
    S.HAS_IMAGE,
                    
    S.INTERNET,
                    
    SD.MMSIZE,
                    
    SD.CUT,
                    
    SD.CTW,
                    
    SD.STONE,
                    
    SD.MEDAL,
                    
    SD.STONE_GROUP,
                    
    SD.COLOR_ID
                            FROM ACNTV
    .SKU SACNTV.SKU_DETAIL_TABLED SDACNTV.MASTER_SKU M
                            WHERE 
                            M
    .MASTER_ID S.MASTER_ID AND
                    
    S.PRODUCT_ID SD.PRODUCT_ID;

                
    C1_REC C1%ROWTYPE;
            
    V_CNT PLS_INTEGER :=0;
             
    R_CNT PLS_INTEREG :=0;

    BEGIN
                

    FOR C1_REC IN C1 LOOP
     
     UPDATE ACNTV
    .MASTER_SKU SET ACTIVE_DATE C1_REC.ACTIVE_DATE,
            
    VENDOR_ID C1_REC.VENDOR_ID,
            
    VENDOR_ITEM_ID C1_REC.VENDOR_ITEM_ID,
            
    STATUS C1_REC.STATUS,
            
    PRODUCT_GROUP C1_REC.PRODUCT_GROUP,
            
    STONE_GROUP C1_REC.STONE_GROUP,
            
    STONE C1_REC.STONE,
            
    COLOR_ID C1_REC.COLOR_ID,
            
    MMSIZE C1_REC.MMSIZE,
            
    CUT C1_REC.CUT,
            
    CTW C1_REC.CTW,
            
    MEDAL C1_REC.MEDAL,
            
    HAS_IMAGESET C1_REC.HAS_IMAGESET
            
    INTERNET C1_REC.INTERNET);
     
    R_CNT := R_CNT 1;
     IF 
    R_CNT 15000 THEN
        V_CNT 
    := V_CNT 1;
         IF 
    V_CNT >= 100 THEN
            V_CNT 
    := 0
            COMMIT
    ;
         
    END IF;

    END IF;
    END LOOP;
    COMMIT;
    END
    Last edited by jepierce; 01-05-05 at 12:22.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Depending upon how many rows in the table, how many rows being updated & how large is your rollback segment, COMMITs inside cursor loops may produce ORA-01555 errors (SNAPSHOT TOO OLD).

    There should only be a single COMMIT, outside the loop to avoid ORA-01555 errors.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2004
    Posts
    9
    thanks,

    Does the way the update is used in the cursor look correct?

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    you could just make it an update statement with no cursor.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    The update looks okay.

    I am pulling memories from 4 years ago so I will not guarantee what I am saying beyond here, but I think I am correct.

    Something is tingling in my brain about the error AnnaCDent says you will get. I think what without the commits you are going to blow your rollback. With the commits you are probably going to get is "Update across commits" (uncertain of the error number, sorry).

    I think your approach needs to be the update with the commits. However to avoid the "commit across updates" roadblock you need to use a "marker" field an restructure your loop.

    Here is how I remember doing it. I am using pseudo code as I do not have time to write it all for you. Hope you can figure it out.

    Code:
    declare
    cursor is 
         .....
         where .... and m.marker = 0 and rownum < 20000
    .....
    begin
    
    loop
    
      for c1_rec in c1
         update .....  marker = 1
    
      end loop;
      commit;
    
      select count(0) from ACNTV.MASTER_SKU where marker = 0;
    
      exit when "count" = 0
    
    end loop
    As I said this is the way I did it 4 years ago. There might be a better way.
    NOTE: Please disregard the label "Senior Member".

  6. #6
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Also a little formatting never hurt ;-)

    While I'm nitpicking, "c1" and "c1_rec" are not acceptable names outside demo code, IMHO.

    The cursor query has color_id twice and is missing a comma after the first one. The two counters seem like a pretty elaborate way to count up to 1,500,000 (which doesn't work anyway since v_cnt appears to stop at 1). The UPDATE is missing a WHERE clause and has a spare bracket - but I agree with Duck, if possible it should just be a single SQL UPDATE.

    How many SKU and SKU_DETAIL_TABLED rows are there for each SKU_MASTER, though? If more than one then I don't see how the update is meant to work.

  7. #7
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    > With the commits you are probably going to get is "Update across commits" (uncertain of the error number, sorry).

    The error is "ORA-01555 Snapshot too old" as Anacedent mentioned. The practice that leads to it is known as "fetching across commits". Oracle uses information from rollback segments to construct read-consistent results as the query runs. It helps not to trash those rollback blocks as you go along.

    The other error you might be thinking of is "ORA-01002: fetch out of sequence", but this only occurs when the cursor is FOR UPDATE:
    Code:
    SQL> CREATE TABLE test_tab AS SELECT * FROM user_objects;
    
    Table created.
    
    SQL> DECLARE
      2      CURSOR c_obj IS
      3          SELECT *
      4          FROM   test_tab
      5          FOR UPDATE;
      6  BEGIN
      7      FOR r IN c_obj     
      8      LOOP
      9          COMMIT;
     10      END LOOP;
     11  END;
     12  /
    DECLARE
    *
    ERROR at line 1:
    ORA-01002: fetch out of sequence
    ORA-06512: at line 7
    Last edited by WilliamR; 01-05-05 at 14:39.

  8. #8
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Thanks for the clarification William. The memory was not real sharp. I was recalling my DBA saying "fetch across commits" and thinking it was an error in and of itself.

    I will admit to being surprised that AnnaCDent "appeared" to be incorrect. :-)
    NOTE: Please disregard the label "Senior Member".

  9. #9
    Join Date
    Jan 2004
    Posts
    492

    Talking

    Quote Originally Posted by Todd Barkus
    I will admit to being surprised that AnnaCDent "appeared" to be incorrect. :-)
    We all know she will never ever be wrong

    Ever.
    Oracle OCPI (Certified Practicing Idiot)

Posting Permissions

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