Oracle 19C+
Hi there
We've faced one interensting (bad) issue
We have to parse xml documents in database.
First we used to work with regular buffer tables to load data from xml document, then operate with it and then load into destination tables.
Then we decided to work with multiple documents at once and changed regular buffer tables to global temporary tables (on commit preserve rows). Each document is processed in its own session.
According to the tests everything was fine. And in production at first everything was fine too.
But we noticed that sometimes we get an error.
The error occurres ~ on 1 out of 1000 documents.
begin
insert into GTT
select * from (xml as xmltype);
l_cnt:=SQL%ROWCOUNT;
--at this point we get 1 row(and thats correct)
if l_cnt=0
then raise;
end if;
commit;
--at this point we get 0 rows
end;
XML document is correct.
Why I wrote interesting is because on the test bench this document is loaded into a temporary table without problems and after
commit the data exists.
Test bench has 19c too but less patches
What could it be? bug? How to deal with this?
Dont commit DML before insert into the destination tables?
But there are about 20 tables so 20 select-inserts from xml or parent tables. Several updates on some tables. Then select-inserts to the destination tables.
For this task there were only one big transaction when we added data to destination