Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    76

    Question Unanswered: need help with cursor row count

    Good Morning,
    I am trying to compare the record counts collected by a cursor with the counts inserted to a table to validate if data inserted was successfull. Firstly, not really sure if this is the best way to do it. Any ideas, please share.

    Having said that, I am working with these two statements. You can see where I am placing them in the code below. Trying to figure out what the best place is to collect this count. I am pretty sure that when I set the limit at 2000 within the loop; it collects the count and than wipes it out for the next iteration. How could I set this up as a counter so it accumulates as 2000 + 2000 + 2000 + 994?

    Thank you all for your help. Please let me know if you need to look at the full code.

    1) v_rowcount := contract_rec.count; --994
    2) v_Total_records := contract_cur%rowcount; --6994



    open contract_cur;

    Loop

    fetch contract_cur bulk collect into contract_rec limit 2000;

    Forall j IN contract_rec.FIRST .. contract_rec.LAST
    save exceptions
    insert into ( select contract_id, item_name, organization_code, PRIM_ACTV_FLG, creation_date, created_by, last_update_date, updated_by
    from ctrct_org_item )
    values contract_rec (j);

    v_rowcount := contract_rec.count;

    i := i+1;
    IF mod(i, 1000) = 0 THEN -- Commit every 1000 records
    COMMIT;
    END IF;

    if contract_cur%notfound then exit;
    end if;

    End Loop;

    v_Total_records := contract_cur%rowcount;
    dbms_output.put_line ( 'Total number of rows inserted was into CTRCT_ORG_ITEM = ' || v_Total_records);
    dbms_output.put_line ('rowcount is ' || v_rowcount);

    if v_rowcount = v_Total_records then v_status := 'EXECUTED' ;
    else v_status := 'FAILED';
    end if;

    **********DBMS OUTPUT*************
    Table CTRCT_ORG_ITEM Truncated
    Table CTRCT_ORG_ITEM Truncated and reloaded
    Total number of rows inserted was into CTRCT_ORG_ITEM = 6994
    rowcount is 994
    Total time to load the CTRCT_ORG_ITEM was 1.76seconds

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    why are you doing INSERT inside LOOP?
    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
    Nov 2003
    Posts
    76
    I am not sure what you mean by that. How else would I insert the data collected by by the loop. Perhaps you can expalin this a little and show me how this can be done. I understand or so I thought that for BULK inserts, the FETCH/FORALL inserts have to come back to back.

    Great, so now I have 2 questions. How to count and where to put the insert.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    DECLARE
    MASTER_COUNT NUMBER;
    
    BEGIN
    MASTER_COUNT :=0;
    
    LOOP
    
    MASTER_COUNT := MASTER_COUNT + contract_rec.count;
    
    END LOOP;
    dbms_output.put_line ( 'Total number of rows inserted was into CTRCT_ORG_ITEM = ' || MASTER_COUNT);
    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.

  5. #5
    Join Date
    Nov 2003
    Posts
    76
    Thank you Anacedent.

Posting Permissions

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