Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: 2nd Req URGENT pls help- needed (deletes)

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

From: salu Ullah <>
Date: Wed, 06 Dec 2000 23:07:57 -0000
Message-Id: <>

Hello Diana

I ran the cursor u send me but still its not deleting any records.

after running the cursor i got :

Deleted 0 rows.
Total Rows Deleted from Mem Check: 0

PL/SQL procedure successfully completed.

Both data has same three columns with same data type. For more info below are the data from tow tables

Temp_rc table

------ ---- --------
001640 RACK A
001640 RACK A1D1O
001640 RACK B
001640 RACK C
001640 RACK D
001640 RACK K
001640 RACK L Mem_check table


------ --------- ---- -------- -
001640 04-JAN-01 RACK A        B
001640 01-APR-01 RACK L        B
001640 02-APR-01 RACK A        B
001640 03-APR-01 RACK A        B
001640 04-APR-01 RACK D        B
001640 05-APR-01 RACK L        B
001640 22-APR-01 RACK A        B
001640 23-APR-01 RACK B        B
001640 24-APR-01 RACK A1D10    B
001640 26-APR-01 RACK A        B
001640 27-APR-01 RACK A        B
001640 28-APR-01 RACK B        B
001640 29-APR-01 RACK A        B
001640 30-APR-01 RACK D        B

Want to delete rows from mem_check where it matches values from temp_rc and join_date > '31-JAN-00'

Would appreciate any help.



>From: Diana Duncan <>
>To: Multiple recipients of list ORACLE-L <>
>Subject: RE: 2nd Req URGENT pls help- needed (deletes)
>Date: Wed, 06 Dec 2000 13:15:37 -0800
>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,
>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):
> v_join_date date := to_date('31-DEC-2000', 'DD-MON-YYYY');
> v_count number(10) := 0;
> 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 || '
> commit;
> exit when sql%notfound;
> end loop;
> dbms_output.put_line('Total rows deleted: ' || v_count);
>-----Original Message-----
>Sent: Wednesday, December 06, 2000 2:39 PM
>To: Multiple recipients of list ORACLE-L
>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
>the temp table its reading from and also the output of the cursor.
>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';
>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;
>v_deletes := v_deletes + 1;
>end if;
>if v_count = 500 then
>v_count := 0;
>end if;
>end loop;
>DBMS_OUTPUT.put_line ('Total Rows Deleted from Member Check: '||v_deletes);
>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
>------ ----- -----
>001640 RACK A
>001640 RACK A1D1O
>001640 RACK B
>001640 RACK C
>001640 RACK D
>001640 RACK K
>001640 RACK L
>Would really appreciate any help
>Please see the official ORACLE-L FAQ:
>Author: Diana Duncan
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
Received on Wed Dec 06 2000 - 17:07:57 CST

Original text of this message