SQL & PL/SQL

Hi Folks,

I am running the below anonymous block for the dml operation, i am receiving the "snapshot too old error". Following are the results for undo. database is 10g. The number of records are in millions.


ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec] NEEDED UNDO SIZE [MByte]
------------------------ ------------------------- ------------------------
500 900 224.964844

Following is the PL/SQL block

declare
v_dn VARCHAR2(16);
v_ln NUMBER(38);
v_stabilityHistory VARCHAR2(30);
v_dcVoltageAE NUMBER;
v_dcVoltageBE NUMBER;
v_capacitanceBalance NUMBER(38);
v_resistanceAE NUMBER(38);
v_resistanceBE NUMBER(38);
v_resistanceAB NUMBER(38);
v_resistanceBA NUMBER(38);
v_resistanceAtoBatt NUMBER(38);
v_resistanceBtoBatt NUMBER(38);
v_stabilityToday VARCHAR2(2);
v_stability VARCHAR2(20);

cursor ANTICCIRCUIT_CURSOR is
select DIRNUM, LINENUM, STABILITY_HISTORY from ANTICCIRCUIT where TESTCOMPDATE is not null ;

cursor TESTDATA_CURSOR (dn PSAOSSTESTDATA_1.DIRECTORYNUMBER%TYPE, ln PSAOSSTESTDATA_1.LINENUMBER%TYPE) is
select DCVOLTAGEAE, DCVOLTAGEBE, CAPACITANCEBALANCE, RESISTANCEAE, RESISTANCEBE,
RESISTANCEAB, RESISTANCEBA, RESISTANCEATOBATT, RESISTANCEBTOBATT
from PSAOSSTESTDATA_1 where DIRECTORYNUMBER = dn and LINENUMBER = ln ;

begin
for anticRecord in ANTICCIRCUIT_CURSOR
loop
v_stability := null;
v_stabilityToday := '-';
v_dn := anticRecord.DIRNUM;
v_ln := anticRecord.LINENUM;
v_stabilityHistory := anticRecord.STABILITY_HISTORY;

for testDataRecord in TESTDATA_CURSOR(v_dn, v_ln)
loop
v_dcVoltageAE := testDataRecord.DCVOLTAGEAE;
v_dcVoltageBE := testDataRecord.DCVOLTAGEBE;
v_capacitanceBalance := testDataRecord.CAPACITANCEBALANCE;
v_resistanceAE := testDataRecord.RESISTANCEAE;
v_resistanceBE := testDataRecord.RESISTANCEBE;
v_resistanceAB := testDataRecord.RESISTANCEAB;
v_resistanceBA := testDataRecord.RESISTANCEBA;
v_resistanceAtoBatt := testDataRecord.RESISTANCEATOBATT;
v_resistanceBtoBatt := testDataRecord.RESISTANCEBTOBATT;

if ((v_resistanceAE <= 100000) or (v_resistanceBE <= 100000) or
(v_resistanceAB <= 100000) or (v_resistanceBA <= 100000) or
(v_resistanceAtoBatt <= 100000) or (v_resistanceBtoBatt <= 250000) or
(abs(v_dcVoltageAE) > 30) or (abs(v_dcVoltageBE) > 30) or
(v_capacitanceBalance < 85)) then
v_stabilityToday := 'U';
elsif ((v_resistanceAE < 1000000) or (v_resistanceBE < 1000000) or
(v_resistanceAB < 1000000) or (v_resistanceBA < 1000000) or
(v_resistanceAtoBatt < 1000000) or (v_resistanceBtoBatt < 1000000)) then
v_stabilityToday := 'C' ;
else
v_stabilityToday := 'S' ;
end if;

v_stabilityHistory := concat(v_stabilityToday, v_stabilityHistory);
v_stabilityHistory := substr(v_stabilityHistory, 0, 30);
end loop;

if (instr(v_stabilityHistory, 'U') > 0) then
v_stability := 'UNSTABLE';
elsif (instr(v_stabilityHistory, 'C') > 0) then
v_stability := 'CONDITION';
elsif (instr(v_stabilityHistory, 'S') > 0) then
v_stability := 'STABLE';
end if;

update ANTICCIRCUIT set STABILITY = v_stability, STABILITY_HISTORY = v_stabilityHistory
where DIRNUM = v_dn and LINENUM = v_ln ;

if mod(ANTICCIRCUIT_CURSOR%ROWCOUNT, 100) = 0 then
commit;
end if;
end loop;

commit;


exception
when others then
dbms_output.put_line(SQLERRM);
end;
/
exit

Can you please help me to overcome this error? is there any changes required in the code?

Thanks & Regards,
vj.