Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    2

    Unanswered: Strange behaviour of UPDATE RETURNING BULK COLLECT INTO array

    Hi all,

    I have this update that is being run by several threads (each with a different connection) at the same time. The idea is that since several threads are doing the same update, I need the returning clause to determine which rows each affected.

    Note that the dd_documents table has a unique index on the doc_seq_num column.

    This is the code where the update is:


    BEGIN

    GEN_DOCS.gtAccountsSet.DELETE;

    UPDATE DD_DOCUMENTS
    SET processing_step = pnCurrStep
    WHERE processing_status = CONST.DOC_PROC_STATUS_UNFINISHED
    AND processing_step = pnPreviousStep
    AND error_code is NULL
    AND rownum <= pnMaxNumber
    RETURNING doc_seq_num,
    entity_no,
    entity_status,
    entity_deact_date,
    period_start_date,
    period_end_date,
    bill_ref_no
    BULK COLLECT INTO GEN_DOCS.gtAccountsSet;

    affectedRows:=SQL%ROWCOUNT;

    INSERT INTO vp_a (message_date, message) VALUES (SYSTIMESTAMP,
    'STEP - '||pnPreviousStep||' Affected rows - '||affectedRows);

    FOR i IN NVL(GEN_DOCS.gtAccountsSet.FIRST,-1)..NVL(GEN_DOCS.gtAccountsSet.LAST,-2) LOOP
    INSERT INTO vp_a (message_date, message) VALUES (SYSTIMESTAMP,
    'STEP - '||pnPreviousStep||' Accounts in Batch ('||i||')- '||GEN_DOCS.gtAccountsSet(i).doc_seq_num);

    END LOOP;

    COMMIT;

    RETURN (CONST.SUCCESS);

    EXCEPTION
    WHEN OTHERS THEN
    ROLLBACK;
    UTILS.plog(vProcedure,GEN_DOCS.gvGenDocsProcId,
    CONST.XTR_LOG_ERROR_LOW,
    'ERROR on Update DD_DOCUMENTS'||
    'SQLCODE - '||to_char(SQLCODE)||' '||SQLERRM(SQLCODE));
    RETURN(SQLCODE);


    The insert and the loop with another insert, are just for debugging proposes. Basically, I'm inserting the number of rows the update affected and then I'm printing every position of the array. The NVL is a trick to prevent the loop to start if the array is empty.

    This would all be fine if the results I'm getting in the table weren't so strange. In a single run (the update is over a table with almost 500.000 rows) I get most of the updates and corresponding array correctly, but sometimes the most unexplainable thing happens:

    Apart from the SQL%rowcount returning a value smaller than the size of the array, the array contains duplicated values of a column that has a unique index.

    These are some partial listings from the vp_a table that I'm using for logging proposes:

    Note that between parentheses is index position on the array followed by the doc_seq_num (the unique identifier on dd_documents).

    ...
    02-FEB-05 11.18.03.664107 AM STEP - 40 Affected rows - 4
    02-FEB-05 11.18.03.664599 AM STEP - 40 Accounts in Batch (1)- 5162
    02-FEB-05 11.18.03.664721 AM STEP - 40 Accounts in Batch (2)- 5163
    02-FEB-05 11.18.03.664812 AM STEP - 40 Accounts in Batch (3)- 5164
    02-FEB-05 11.18.03.664911 AM STEP - 40 Accounts in Batch (4)- 5165
    02-FEB-05 11.18.03.665003 AM STEP - 40 Accounts in Batch (5)- 5162
    02-FEB-05 11.18.03.665097 AM STEP - 40 Accounts in Batch (6)- 5163
    02-FEB-05 11.18.03.665195 AM STEP - 40 Accounts in Batch (7)- 5164
    02-FEB-05 11.18.03.665285 AM STEP - 40 Accounts in Batch (8)- 5165
    ...

    Another example:
    ...
    02-FEB-05 11.27.27.148387 AM STEP - 10 Affected rows - 9
    02-FEB-05 11.27.27.148868 AM STEP - 10 Accounts in Batch (1)- 9305
    02-FEB-05 11.27.27.148999 AM STEP - 10 Accounts in Batch (2)- 9308
    02-FEB-05 11.27.27.149092 AM STEP - 10 Accounts in Batch (3)- 9310
    02-FEB-05 11.27.27.149186 AM STEP - 10 Accounts in Batch (4)- 9565
    02-FEB-05 11.27.27.149287 AM STEP - 10 Accounts in Batch (5)- 9566
    02-FEB-05 11.27.27.149379 AM STEP - 10 Accounts in Batch (6)- 9567
    02-FEB-05 11.27.27.149469 AM STEP - 10 Accounts in Batch (7)- 9305
    02-FEB-05 11.27.27.149569 AM STEP - 10 Accounts in Batch (8)- 9308
    02-FEB-05 11.27.27.149661 AM STEP - 10 Accounts in Batch (9)- 9310
    02-FEB-05 11.27.27.149754 AM STEP - 10 Accounts in Batch (10)- 9311
    02-FEB-05 11.27.27.149853 AM STEP - 10 Accounts in Batch (11)- 9488
    02-FEB-05 11.27.27.149943 AM STEP - 10 Accounts in Batch (12)- 9489
    02-FEB-05 11.27.27.150036 AM STEP - 10 Accounts in Batch (13)- 9565
    02-FEB-05 11.27.27.150134 AM STEP - 10 Accounts in Batch (14)- 9566
    02-FEB-05 11.27.27.150225 AM STEP - 10 Accounts in Batch (15)- 9567
    ...

    Another example:
    ...
    02-FEB-05 11.36.37.857369 AM STEP - 40 Affected rows - 27
    02-FEB-05 11.36.37.857854 AM STEP - 40 Accounts in Batch (1)- 17382
    02-FEB-05 11.36.37.857987 AM STEP - 40 Accounts in Batch (2)- 17383
    02-FEB-05 11.36.37.858080 AM STEP - 40 Accounts in Batch (3)- 17385
    02-FEB-05 11.36.37.858172 AM STEP - 40 Accounts in Batch (4)- 17387
    02-FEB-05 11.36.37.858270 AM STEP - 40 Accounts in Batch (5)- 17519
    02-FEB-05 11.36.37.858361 AM STEP - 40 Accounts in Batch (6)- 17520
    02-FEB-05 11.36.37.858452 AM STEP - 40 Accounts in Batch (7)- 17522
    02-FEB-05 11.36.37.858547 AM STEP - 40 Accounts in Batch (8)- 17523
    02-FEB-05 11.36.37.858637 AM STEP - 40 Accounts in Batch (9)- 17524
    02-FEB-05 11.36.37.858727 AM STEP - 40 Accounts in Batch (10)- 17525
    02-FEB-05 11.36.37.858824 AM STEP - 40 Accounts in Batch (11)- 17526
    02-FEB-05 11.36.37.858914 AM STEP - 40 Accounts in Batch (12)- 17527
    02-FEB-05 11.36.37.859004 AM STEP - 40 Accounts in Batch (13)- 17382
    02-FEB-05 11.36.37.859102 AM STEP - 40 Accounts in Batch (14)- 17383
    02-FEB-05 11.36.37.859194 AM STEP - 40 Accounts in Batch (15)- 17385
    02-FEB-05 11.36.37.859287 AM STEP - 40 Accounts in Batch (16)- 17387
    02-FEB-05 11.36.37.859390 AM STEP - 40 Accounts in Batch (17)- 17519
    02-FEB-05 11.36.37.859478 AM STEP - 40 Accounts in Batch (18)- 17520
    02-FEB-05 11.36.37.859567 AM STEP - 40 Accounts in Batch (19)- 17522
    02-FEB-05 11.36.37.859663 AM STEP - 40 Accounts in Batch (20)- 17523
    02-FEB-05 11.36.37.859752 AM STEP - 40 Accounts in Batch (21)- 17524
    02-FEB-05 11.36.37.859898 AM STEP - 40 Accounts in Batch (22)- 17525
    02-FEB-05 11.36.37.859994 AM STEP - 40 Accounts in Batch (23)- 17526
    02-FEB-05 11.36.37.860080 AM STEP - 40 Accounts in Batch (24)- 17527
    02-FEB-05 11.36.37.860168 AM STEP - 40 Accounts in Batch (25)- 17585
    02-FEB-05 11.36.37.860264 AM STEP - 40 Accounts in Batch (26)- 17586
    02-FEB-05 11.36.37.860351 AM STEP - 40 Accounts in Batch (27)- 17588
    02-FEB-05 11.36.37.860438 AM STEP - 40 Accounts in Batch (28)- 17593
    02-FEB-05 11.36.37.860533 AM STEP - 40 Accounts in Batch (29)- 17594
    02-FEB-05 11.36.37.860620 AM STEP - 40 Accounts in Batch (30)- 17595
    02-FEB-05 11.36.37.860708 AM STEP - 40 Accounts in Batch (31)- 17600
    02-FEB-05 11.36.37.860804 AM STEP - 40 Accounts in Batch (32)- 17602
    02-FEB-05 11.36.37.860891 AM STEP - 40 Accounts in Batch (33)- 17603
    02-FEB-05 11.36.37.860980 AM STEP - 40 Accounts in Batch (34)- 17645
    02-FEB-05 11.36.37.861076 AM STEP - 40 Accounts in Batch (35)- 17744
    ...


    There are several more occurrences but I think you all got the picture.
    This is all rather strange and I must be doing something very wrong.

    Thanks everyone for their time.

    Best regards,
    VP

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I can't see anything wrong with your code. I modified it to work on the EMP table like this:
    Code:
    DECLARE
      TYPE emps_set_type IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
      emps_set emps_set_type;
      affectedRows integer;
      pnPreviousStep integer := 40;
    BEGIN
    
      emps_set.DELETE;
    
      UPDATE emp
      SET sal = sal
      WHERE rownum <= &pnMaxNumber
      RETURNING empno,
      ename,
      job,
      mgr,
      hiredate,
      sal,
      comm,
      deptno
      BULK COLLECT INTO emps_set;
    
      affectedRows:=SQL%ROWCOUNT;
    
      INSERT INTO vp_a (message_date, message) VALUES (SYSTIMESTAMP,
      'STEP - '||pnPreviousStep||' Affected rows - '||affectedRows);
    
      FOR i IN NVL(emps_set.FIRST,-1)..NVL(emps_set.LAST,-2) LOOP
    
        INSERT INTO vp_a (message_date, message) VALUES (SYSTIMESTAMP,
        'STEP - '||pnPreviousStep||' Accounts in Batch ('||i||')- '||emps_set(i).ename);
    
      END LOOP;
    
    END;
    /
    I then ran it a few times and got these results:
    Code:
    STEP - 40 Affected rows - 5
    STEP - 40 Accounts in Batch (1)- SMITH
    STEP - 40 Accounts in Batch (2)- ALLEN
    STEP - 40 Accounts in Batch (3)- WARD
    STEP - 40 Accounts in Batch (4)- JONES
    STEP - 40 Accounts in Batch (5)- MARTIN
    STEP - 40 Affected rows - 6
    STEP - 40 Accounts in Batch (1)- SMITH
    STEP - 40 Accounts in Batch (2)- ALLEN
    STEP - 40 Accounts in Batch (3)- WARD
    STEP - 40 Accounts in Batch (4)- JONES
    STEP - 40 Accounts in Batch (5)- MARTIN
    STEP - 40 Accounts in Batch (6)- BLAKE
    STEP - 40 Affected rows - 2
    STEP - 40 Accounts in Batch (1)- SMITH
    STEP - 40 Accounts in Batch (2)- ALLEN
    Does that work for you?

  3. #3
    Join Date
    Nov 2004
    Posts
    2

    Smile

    That is what I would expect my code to do.

    I'm afraid that to simulate this, one would need to implement several connections competing for the dd_documents table (with that update) and a table with some fair amount of records...

    I don't expect one to carry out all that work just to help me. I was expecting a more "that happened to me" or "theoretically you are doing this wrong" kind of answer.

    Anyway, thanks a lot for your efforts.

    Best Regards,
    VP

Posting Permissions

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