You most certainly are doing a fetch across a commit.
you have:
for x in cursor
loop
DML
if (mod) then commit end if;
end loop
you have a commit in your loop, you are fetching across that. Here is an example that shows what is happening to you:
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:895410916429 <code>
there, we are reading from a READ ONLY tablespace and getting an ora-1555! the reason -- lots of small transactions wiping out transaction headers that we need for read consistency.
Have you priced a 36gig drive recently? About $200 usd. You've probably spent wwwwaaaayyyyy more then that trying to figure this out. It would be more cost effective to keep a closet full of these disks and just pull one out every time you wanted to do this process and use the disk. Just throw the disk away after you run your update! It would be cheaper.
You quite simply
need to configure your system properly to do the jobs it needs to do. Period.
Let's take a further look at this code:
declare
cursor cur is
select /*+ USE_NL(t2 t1) */t1.art_grp_no,
t1.amount+t1.disc_amount amnt,
t2.invoice_date,
t2.cust_no,
t2.store_no
from invoice_line t1,invoice t2
where t1.invoice_no=t2.invoice_no and
t1.till_no = t2.till_no ;
retRow cur%ROWTYPE ;
cnt number := 0 ;
begin
for retRow in cur loop
begin
cnt := cnt + 1 ;
insert into TB_ART_GRP_CUST_STAT values(
retRow.art_grp_no, retRow.amnt,
retRow.invoice_date,retRow.cust_no,retRow.store_no);
exception
when others then
if sqlcode = -1 then
update TB_ART_GRP_CUST_STAT set TOTAL_SUM = TOTAL_SUM + retRow.amnt
where ART_GRP_NO = retRow.art_grp_no and
INVOICE_DATE = retRow.invoice_date and
CUST_NO = retRow.cust_no and
STORE_NO = retRow.store_no ;
end if ;
end ;
if mod(cnt , 1000) = 0 then
commit ;
end if ;
end loop ;
end ;
/
Hmm, couple of big problems I see:
1) it is not restartable. When you hit the ora-1555 or ANY OTHER error -- what then? Well, you'll update the rows you just inserted adding the retRow.amnt in erroneously. Or, you'll update a row for the second time if it existed on the first pass! In other words
you will corrupt your data. Even if you get around the 1555 -- you have a TON more code to write to make this process safely restartable -- so you process a row at least once and at most once (else the integrity of your data -- GONZO)
2) you have a "WHEN OTHERS" that is not followed by a RAISE; This is a bug -- 100% of the time in my opinion. You are looking for DUP_VAL_ON_INDEX (don't use when others, use when dup_val_on_index and remove the sqlcode=-1 check).
What happens if the error is "out of space", "constraint xxxx violated", etc??? You
silently ignore it. Again, this would result in a total, utter and complete loss of data integrity.
Bottom line -- in 8i this should be AT MOST two statements -- one to update existing rows and one to insert the rows that did not exist yet.
In 9i, this will be ONE statement -- MERGE to do an update of existing and insert of new.
In both cases -- you will configure sufficient disk space to do the work your system needs to do.
Actually 3rd option. If this table TB_ART_GRP_CUST_STAT is just a rollup of the invoice and invoice_line table -- you should just:
create materialized view TB_ART_GRP_CUST_STAT
<mv options>
as
select t1.art_grp_no,
t1.amount+t1.disc_amount amnt,
t2.invoice_date,
t2.cust_no,
t2.store_no
from invoice_line t1,invoice t2
where t1.invoice_no=t2.invoice_no and
t1.till_no = t2.till_no ;
and refresh that as needed. Let the system do the work for you (a full refresh would be faster then update/insert every single row)