Home » SQL & PL/SQL » SQL & PL/SQL » BULK DELETE
BULK DELETE [message #435200] Tue, 15 December 2009 02:53 Go to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
I have a below block for bulk delete by executing this i got below error.

Declare

CURSOR cur_stage_chk IS
SELECT DISTINCT sitm.doc_item_id doc_itm
FROM tmp_chk citm, stage_checklist_item sitm
WHERE citm.doc_item_ref = sitm.doc_item_ref
AND citm.checklist_id = 20050805359640
AND citm.status = 'DELETED';

TYPE reclist IS TABLE OF cur_stage_chk%ROWTYPE;
recs reclist;
	
Begin

    OPEN cur_stage_chk;
    LOOP
      FETCH cur_stage_chk BULK COLLECT INTO recs LIMIT 10000;

      FORALL i IN 1..recs.COUNT
	  
	  DELETE FROM STAGE_CHECKLIST_ITEM WHERE DOC_ITEM_ID = recs(i);
	  
      EXIT WHEN cur_stage_chk%NOTFOUND;
    END LOOP;
    CLOSE cur_stage_chk;

End;


ORA-06550: line 21, column 57: PLS-00382: expression is of wrong type


Re: BULK DELETE [message #435205 is a reply to message #435200] Tue, 15 December 2009 03:11 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not a Performances question just a SQL/PLSQL one.

"recs" is an array of rowtype so recs(i) can't be an id.

Regards
Michel

[Updated on: Tue, 15 December 2009 03:11]

Report message to a moderator

Re: BULK DELETE [message #435221 is a reply to message #435205] Tue, 15 December 2009 04:43 Go to previous messageGo to next message
ORAGENASHOK
Messages: 238
Registered: June 2006
Location: Chennai
Senior Member
I have modified as below instead of FORALL i have used FOR

Its working when i execute this for large data it perform very slow.

According to Michel's Reply we cant delete bulk record using forall stmt right


Declare

CURSOR cur_stage_chk IS
SELECT DISTINCT sitm.doc_item_id doc_itm
FROM tmp_chk citm, stage_checklist_item sitm
WHERE citm.doc_item_ref = sitm.doc_item_ref
AND citm.checklist_id = 20050805359640
AND citm.status = 'DELETED';

TYPE reclist IS TABLE OF cur_stage_chk%ROWTYPE;
recs reclist;
	
Begin

    OPEN cur_stage_chk;
    LOOP
      FETCH cur_stage_chk BULK COLLECT INTO recs LIMIT 10000;

      FOR i IN 1..recs.COUNT loop
	  
	  DELETE FROM STAGE_CHECKLIST_ITEM WHERE DOC_ITEM_ID = recs(i).doc_itm;
	  
      EXIT WHEN cur_stage_chk%NOTFOUND;
	  end loop;
    END LOOP;
    CLOSE cur_stage_chk;

End;
Re: BULK DELETE [message #435223 is a reply to message #435221] Tue, 15 December 2009 04:52 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
According to Michel's Reply we cant delete bulk record using forall stmt right


We are reading the same message, aren't we?
The one where @Michel wrote:Quote:
This is not a Performances question just a SQL/PLSQL one.

"recs" is an array of rowtype so recs(i) can't be an id.


I can't see anything in there about not being able to delete using forall - just @Michel pointing out that you'd got your data types and record types mixed up.

What do you get if you try:
      FORALL i IN 1..recs.COUNT
	  
	  DELETE FROM STAGE_CHECKLIST_ITEM WHERE DOC_ITEM_ID = recs(i).doc_itm;


It's all a bit pointless though, as the correct answer is to do it in a single delete. Something like this:
BEGIN
  DELETE stage_checklist_item
  WHERE doc_itm_ref IN (SELECT doc_itm_ref 
                        FROM   tmp_chk
                        WHERE  citm.checklist_id = 20050805359640
                        AND citm.status = 'DELETED');
END;


[Updated on: Tue, 15 December 2009 04:53]

Report message to a moderator

Previous Topic: Materialized View not refreshing properly
Next Topic: In which year maximum number of employees has been recruited(hire date)using employees table(oracle)
Goto Forum:
  


Current Time: Thu Sep 29 03:56:46 CDT 2016

Total time taken to generate the page: 0.05714 seconds