Hi,

I‘m using Oracle 8.0.5.2.1 Enterprise Edition with PL/SQL 8.0.5.2.0.
There's an importtable, which is continuously filled with data from
several clients (~50-200 records per minute). A dbms_job starts the
sort-procedure every 2 minutes to spread the data into different
tables (statistics...).

Following sporadic ‘error‘ occurs:

The data from importtable is deleted, but not sorted (inserted) into
my datatables.
I'm missing 100 ids in my datatables. Why ?

Following procedure spreads the data from importtable:

/************************************************** ************************/
procedure SortMyData
************************************************** *************************/
as
vRowTab tRowTable;
vDataMemTab tDataTable;
vId number;
vNum number;

loop
vRowTab.delete;
vDataMemTab.delete;
vTabNum := 0;

<<loop_cursor>>
for row in (
select rownum,rowid, import_temp.* from import_temp
where rownum <= 100)
loop
if vNum = 0 then
vNum := to_number(to_char(trunc(row.inserttime,'MM'),'MM') );
else
if vNum !=
to_number(to_char(trunc(row.inserttime,'MM'),'MM') )
then
exit loop_cursor;
end if;
end if;

vRowTab(row.ROWNUM) := row.ROWID;

/* get new id for data tables*/
select my_seq.nextval into vId from dual;

vDataMemTab(row.ROWNUM).id := vId;
vDataMemTab(row.ROWNUM).field_1 := nvl(row.field_1,'0');
...
vDataMEmTab(row.ROWNUM).field_n := nvl(row.field_n,'0');
...
end loop
loop_cursor;

/* delete selected rows from temp1 */
for i in 1..vRowTab.count loop
delete from import_temp where rowid = vRowTab(i);
end loop;

/* start inserts */
for i in 1..vDataMemTab.count loop
/* procedure split Data into different tables */
insertIntoDataTables((i));
end loop;
...
/* end inserts */

if vRowTab.COUNT = 0 then
exit; /* exit function */
else
vRowTab.delete;
vDataMemTab.delete;
commit;
end if;
end loop;
vRowTab.delete;
VDataMemTab.delete;
commit;
end SortMyData;



Do you have any suggestion what's wrong ?!

Thanks.

MarcO