Salu,
Let me clarify 2 points so that u understand it
correctly.
- The sql%rowcount was suggested to check whether
the delete statement affected any rows and if not
use it as a mechanisom to quit the program with
a exception so that u don't have to wait for a
long time to know that it did not delete any
records.
- I did mention in my earlier email that it could
be because of the time component stored in the
join_date column.
try,
select to_char(join_date,'dd-mon-yy hh24:mi')
from <table_name> where rownum <= 5;
This sql will list out 5 rows and find out as to
whether you see the hours and the minutes
displayed
as 00:00 or valid numbers. If they are valid
numbers, it means the join_date field has time
component stored with it.
In that case one way is to use trunc(join_date)
in the WHERE condition so that you will get the
script working but this will have performance
implication because the index will not be used.
Alternatively declare 2 local variables as (say)
v_low_date and v_high_date and initialize them
as v_low_date := to_date(given_date,'dd-mon-yy')
and
v_high_date := to_date((given_date||'23:59:59'),
'dd-mon-yy hh24:mi:ss').
Then use these variables in the WHERE condition as
WHERE join_date between v_low_date and v_high_date
besides other conditions so that the existing index
is used.
Hope this is clear to you and let me know if it
worked for you...
Regards
Rajagopal Venkataramany
- salu Ullah <salu_ullah_at_hotmail.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
> > >
>
>_____________________________________________________________________________________
> > > 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
Received on Wed Dec 06 2000 - 22:26:51 CST
