Procedure Error

[Eshwar Prasad]

i have requirement to delete huge data where i have to select data into small pieces and then delete it to avoid overload on database server. I have written a procedure for this, but it is not giving right output. I am getting error saying "one or more records are fetched". Can you please let me know where am i wrong.

create or replace procedure testproc as
blimit number;
bcount number;
bdate date;
bval varchar2(10);
cursor c1 is
select * from mytest where creationdate<'12-JAN-2011';
begin
blimit:=1000;
select count(*) into bcount from mytest where <'12-JAN-2011';
while bcount >blimit loop
for z in c1 loop
select creationdate into bdate from mytest where creationdate<z.creationdate and rownum><blimit;
delete from mytest where creationdate=bdate;
dbms_output.put_line('print creationdate' || z.creationdate || bdate);
end loop;
commit;
select count(*) into bcount from mytest;
dbms_output.put_line('Print totalcount' || bcount);
end loop;
end;

here mytest table has 3 lakhs records. I want to select 1000 records everytime and delete them. Please help me.>

[Eshwar Prasad]

any help?

[Fatih Keles]

Hi Eshwar,

I really did not understand your procedure. Why don't you just use something like this?
begin delete from some_table where 1=1 and <some_other_criteria> and rownum < 1001; dbms_output.put_line('deleted row count : ' || sql%rowcount); end;