RE: 2nd Req URGENT pls help- needed (deletes)

From: Diana Duncan <Diana_at_fileFRENZY.com>
Date: Wed, 6 Dec 2000 16:02:36 -0500
Message-Id: <10702.123850@fatcity.com>

Salu,

You have a few problems with your program, although you are on the right track. You want to do your deletes in chunks to avoid running out of rollback, which is good.

Your problems are:
1) you are counting the number of rows in your cursor (7) not the number of rows you are deleting
2) you are only deleting 500 rows for each entry in your temp table, instead of deleting all of the rows that meet the requirements. 3) is your 'join_date' column an actual date? If not, you need to cast it to a date to do date comparisons.

How about something like the following (untested, off the top of my head, the usual caveats):

declare

	v_join_date	date := to_date('31-DEC-2000', 'DD-MON-YYYY');
	v_count	number(10) := 0;
begin
	loop
		delete from mem_check
		where (mem_id, rt_code, rm_code) in (select mem_id, rt_code,
rm_code from temp_rc)
		and to_date(join_date) > v_join_date
		and rownum <= 500;
		v_count := v_count + sql%rowcount;
		dbms_output.put_line('Deleted ' || sql%rowcount || '
rows.');
		commit;
		exit when sql%notfound;
	end loop;
	dbms_output.put_line('Total rows deleted: ' || v_count);

end;
/

-----Original Message-----
From: salu Ullah [mailto:salu_ullah_at_hotmail.com] Sent: Wednesday, December 06, 2000 2:39 PM To: Multiple recipients of list ORACLE-L Subject: Re: 2nd Req URGENT pls help- needed (deletes)

Hello djordjej

I made some changes in my cursor...after running it is says no data found where as the temp table has 7 rows in it. Below is the cursor & the data in

the temp table its reading from and also the output of the cursor.

declare
cursor c_mem is
select mem_id, rt_code, rm_code
from temp_rc;

v_count number(10) :=0;
v_deletes number(10) :=0;
v_join_date mem_check.join_date%type :='31-DEC-00';

begin
for v_memdata in c_mem loop
v_count := v_count + 1;
v_deletes := v_deletes + 1;
delete from mem_check
where mem_id = v_memdata.mem_id
and rt_code = v_memdata.rt_code
and rm_code = v_memdata.rm_code
and join_date > v_join_date;
if sql%notfound then
DBMS_OUTPUT.put_line ('Data not found'); v_deletes := v_deletes;
else
v_deletes := v_deletes + 1;
end if;
if v_count = 500 then
commit;
v_count := 0;
end if;
end loop;
commit;
DBMS_OUTPUT.put_line ('Total Rows Deleted from Member Check: '||v_deletes); end;

RESULT

---

Data not found
Data not found
Data not found
Data not found
Data not found
Data not found
Data not found
Total Rows Deleted from Rate Check: 0

PL/SQL procedure successfully completed.

DATA in temp_rc

MEM_ID RT_CD RM_CD
------ ----- -----
001640 RACK A
001640 RACK A1D1O
001640 RACK B
001640 RACK C Received on Wed Dec 06 2000 - 15:02:36 CST