This discussion is archived
5 Replies Latest reply: Dec 13, 2012 10:23 AM by rp0428 RSS

How to clear rowids from collection

user1014019 Newbie
Currently Being Moderated
Hi Team,

DECLARE

CURSOR rec_cur_emp_id IS
SELECT S.ROWID
S.emp_ID,
FROM emp S
WHERE s.emp_status ='A'


CURSOR rec_cur_ins_mgr IS
SELECT s.rowid
FROM emp S
WHERE sec_status_cd = 'A'
AND maint_status_cd <> 'A'
AND MOD(sec_id, 1) = 0;

rowids dbms_sql.urowid_table;

BEGIN

dbms_output.put_line('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
dbms_output.put_line('Beginning data seeding process for emp_TBL' ||
chr(10));
dbms_output.put_line('++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
BEGIN

OPEN rec_cur_emp_id;
LOOP
FETCH rec_cur_emp_id BULK COLLECT
INTO rowids LIMIT 20000;
EXIT WHEN rec_cur_emp_id%NOTFOUND;
END LOOP;
CLOSE rec_cur_dormant;

FORALL i in rowids.FIRST .. rowids.LAST
UPDATE emp_tbl s
SET S.emp_STATUS_CD = 'N'
WHERE ROWID =rowids(i);

v_commit_cnt_dormant := SQL%ROWCOUNT;
dbms_output.put_line('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
DBMS_OUTPUT.PUT_LINE(' Total Number of variant empids is :' ||
v_commit_cnt_dormant);
dbms_output.put_line('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('The error is :' || SQLERRM);
END;

BEGIN
OPEN rec_cur_ins_mgr;
LOOP
FETCH rec_cur_ins_mgr BULK COLLECT INTO rowids LIMIT 20000;
EXIT WHEN rec_cur_emp_id%NOTFOUND;
END LOOP;
CLOSE rec_cur_dormant;


FORALL i in rowids.FIRST .. rowids.LAST
UPDATE emp
SET s.emp_status_cd= 'M'
WHERE sec_id = rowids(i);

v_commit_cnt_user := SQL%ROWCOUNT;

dbms_output.put_line('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');
dbms_output.put_line('Total Number of IS :' ||
v_commit_cnt_user);
dbms_output.put_line('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++');

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(' The error is : ' || sqlerrm);

END;
end;

The problem here is after every block rowds needs to be released. The rowids need to be released after every END

Can you please help me the approach?

Thanks & Regards
Thakur Manoj R
  • 1. Re: How to clear rowids from collection
    971895 Journeyer
    Currently Being Moderated
    we are not seen any sql error in your question?
  • 2. Re: How to clear rowids from collection
    Dom Brooks Guru
    Currently Being Moderated
    This is just wrong, wrong, wrong.
  • 3. Re: How to clear rowids from collection
    Hoek Guru
    Currently Being Moderated
    Why are you allowing a bug in your code?
    Remove:
    EXCEPTION
    WHEN OTHERS THEN
    dbms_output.put_line(' The error is : ' || sqlerrm);
    Usually a commit or rollback will release locks.
  • 4. Re: How to clear rowids from collection
    AlbertoFaenza Expert
    Currently Being Moderated
    user1014019 wrote:
    The problem here is after every block rowds needs to be released. The rowids need to be released after every END
    Hi Manoj R,
    the problem is not the one you mentioned. The problem is that the code you posted is not even working.

    i.e.:
    OPEN rec_cur_emp_id;
    LOOP
    FETCH rec_cur_emp_id BULK COLLECT
    INTO rowids LIMIT 20000;
    EXIT WHEN rec_cur_emp_id%NOTFOUND;
    END LOOP;
    CLOSE rec_cur_dormant;
    You are closing rec_cur_dormant which you never declared. Additionally can you understand what is wrong in your loop? Suppose that the select statement is returning 55000 rows. At the end of the loop your rowids collection will have only last 15000 records. Do you ignore previous records?

    Please read SQL and PL/SQL FAQ

    Additionally when you put some code or output please enclose it between two lines starting with {noformat}
    {noformat} to have a decent formatting.
    
    i.e.:
    {noformat}
    {noformat}
    SELECT ...
    {noformat}
    {noformat}
    
    Be sure to post some working code and explain exactly what you want to do. Your code is full of bugs.
    Bulk collecting and doing the update in this way is not an efficiente method. 
    You can do it more efficiently with SQL.
    And you cannot update table emp_tbl using ROWID from table emp.They are not going to be the same.
    You seem quite confused about your code.
    
    Regards.
    Al                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 5. Re: How to clear rowids from collection
    rp0428 Guru
    Currently Being Moderated
    >
    The problem here is after every block rowds needs to be released. The rowids need to be released after every END
    >
    No - that isn't your problem. A BULK COLLECT reinitializes the collection and replaces any content the collection has.

    You should just be using SQL to do these updates. The biggest problem is that you are using PL/SQL and doing updates 20,000 at a time instead of just using SQL and being done with it.

    You are wasting time and resources by using PL/SQL particularly because your code doesn't even have a COMMIT in it anywhere. That means you are issuing multiple updates when all you need is one for each block.

    The second biggest problem is your WHEN OTHERS exception handlers. Remove them immediately so Oracle will report the correct errors and where they happen.

    The third biggest problem is that your BULK COLLECT loop doesn't have any code in it. As Alberto pointed out it just loops and loops until it gets the last batch or ROWIDS. Then you do the UPDATE block. That means that none of the previous BULK COLLECTs actually accomplish anything.

    The fourth biggest problem is that for BULK COLLECTs you put the exit condition AT THE END of the loop, not at the beginning. So even if you put your code in your current loop you would then never process the last set of ROWIDs.

    The code (pseudo-code and untested) should be structured more like this
    LOOP
    FETCH rec_cur_emp_id BULK COLLECT
    INTO rowids LIMIT 20000;
    
    FORALL i in rowids.FIRST .. rowids.LAST
    UPDATE emp_tbl s
    SET S.emp_STATUS_CD = 'N'
    WHERE ROWID =rowids(i);
    
    EXIT WHEN rec_cur_emp_id%NOTFOUND;
    END LOOP;
    CLOSE rec_cur_dormant;
    First your fetch. Then you process. Then you check for NOTFOUND. For a single row fetch the NOTFOUND would be immediately after the FETCH but not for BULK COLLECT.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points