| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: URGENT help- needed (deletes)
Sallu,
My earlier response should have solved your problem.
Here is a small correction :
Since u are using a cursor here instead of sql%rowcount u need to use c_mem%rowcount to get the rowcount. sql%rowcount will hold good when a cursor is not involved in the operation.
Regards
Rajagopal Venkataramany
On Wed, 06 Dec 2000 15:02:07 -0800, ORACLE-L_at_fatcity.com wrote:
> Hello Rajagopal
>
> I did add %rowcount in my cursor & still got no deletes.
> What i added in my cursor is :
> if sql%rowcount = 0 THEN
> DBMS_OUTPUT.put_line ('Data not found');
> v_deletes := v_deletes;
> else
> v_deletes := v_deletes + 1;
> end if;
>
> after adding it & running the cursor i got :
>
> 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 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
>
> MEM_ID RT_C RM_CODE
> ------ ---- --------
> 001640 RACK A
> 001640 RACK A1D1O
> 001640 RACK B
> 001640 RACK C
> 001640 RACK D
> 001640 RACK K
> 001640 RACK L
>
> Mem_check table
> MEM_ID JOIN_DATE RT_C RM_CODE A
> ------ --------- ---- -------- -
> 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
>
>
> Would appreciate any help.
>
> Thanks
>
> Salman
>
> >From: Rajagopal Venkataramany <rajagopalvr_at_excite.com>
> >Reply-To: ORACLE-L_at_fatcity.com
> >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >Subject: Re: URGENT help- needed (deletes)
> >Date: Tue, 05 Dec 2000 18:36:05 -0800
> >
> >Hi,
> >
> > One of the reason could be that the "join_date" column stores
> > the time component along with the date. That may be the reason
> > that the delete is failing and the process does a redundant run
> > of reading all the rows before it exits out of the program.
> >
> > If u are sure that the delete statements should always delete
> > atleast 1 row, then I suggest u check for sql%rowcount after the
> > delete statement. If sql%rowcount = 0 then u can raise a exception
> > and abort. This way, you will come to know about the problem much
> > earlier rather than waiting till the job completes.
> >
> >** If this is true then do not use trunc(join_date) as it will still
> > not use the index. Let us know so that we can take u to the next
> > step after knowing that this was the issue.
> >
> > Hope this helps...
> >
> >Regards
> >Rajagopal Venkataramany
> >
> > On Tue, 05 Dec 2000 11:16:55 -0800, ORACLE-L_at_fatcity.com wrote:
> >
> > > Hello all
> > >
> > > Iam tring to delete rows (million or more) from a table which
currently
> >has
> > > around 45 million rows. The primary key is composed of (mem_id,
> >join_date,
> > > rt_code, rm_code, month). The values in the where clause are used
from
> > > another temp table which has only three column (mem_id, rt_code,
> > > rm_code)similar to main table. As for join_date column value has to
> >match
> > >=
> > > 'given_date' (DD-MON-YY format ). I have created a cursor which read
> >mem_id,
> > > rt_code, rm_code from temp table & deletes from main table.
> > > Somehow its not deleting any rows even after showing procedure
> >successfullt
> > > completed or just hangs & do nothing.
> > > Would really appreciate any help.
> > > below is my 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 :='01-JAN-01';
> > > 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 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;
> > >
> > > Thanks
> > >
> > > salu
> > >
> >_____________________________________________________________________________________> >(or the name of mailing list you want to be removed from). You may
> > > Get more from the Web. FREE MSN Explorer download :
> >http://explorer.msn.com
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > --
> > > Author: salu Ullah
> > > INET: salu_ullah_at_hotmail.com
> > >
> > > 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: ListGuru_at_fatcity.com (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).
> >
> >
> >Regards
> >Rajagopal Venkataramany
> >
> >
> >
> >
> >
> >_______________________________________________________
> >Tired of slow Internet? Get @Home Broadband Internet
> >http://www.home.com/xinbox/signup.html
> >
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >--
> >Author: Rajagopal Venkataramany
> > INET: rajagopalvr_at_excite.com
> >
> >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: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> >the message BODY, include a line containing: UNSUB ORACLE-L
 |
 |