Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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

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

From: Cale, Rick T (Richard) <RICHARD.T.CALE_at_saic.com>
Date: Wed, 6 Dec 2000 12:11:09 -0800
Message-Id: <10702.123837@fatcity.com>


I am not an expert on this but isn't sql%notfound returning true for the delete statement and not
you cursor. It immediately follows the delete statement.

Rick

> -----Original Message-----
> From: salu Ullah [SMTP: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
> 001640 RACK D
> 001640 RACK K
> 001640 RACK L
>
> Would really appreciate any help
>
> Thanks
>
> Salman
>
>
> >From: "djordjej" <djordjej_at_home.com>
> >Reply-To: ORACLE-L_at_fatcity.com
> >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >Subject: Re: 2nd Req URGENT help- needed (deletes)
> >Date: Tue, 05 Dec 2000 20:15:29 -0800
> >
> >Hi,
> >
> >Is this one big delete ? How do you know that it is not doing anything.
> >Have you checked v$sesstat and v$rollstat ? It might be deleting
> records,
> >but of course you cannot see this from another session. And deleting 1M
> >rows from a 45M rows table, especially if you have foreign keys from
> child
> >tables with no index on the other end, can take quite some time.
> >
> >Djordje
> >
> >----- Original Message -----
> >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> >Sent: Tuesday, December 05, 2000 10:00 PM
> >
> >
> > > 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 it 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
> > >
> > >
> >_________________________________________________________________________
> ___
> >_________
> > > 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).
> > >
> >
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >--
> >Author: djordjej
> > INET: djordjej_at_home.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).
>
> __________________________________________________________________________
> ___________
> 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
Received on Wed Dec 06 2000 - 14:11:09 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US