We are having unusual occurrences in PRD.
We get an XMLs from an agency and it's transformed by our ESB
After that ESB calls a PL/SQL package to process
PL/SQL process will process it and write to some tables and insert a warning to warning table if the XML is a certain type (found using a tag in the XML)
Problem is, for some XMLs it is writing to all tables EXCEPT the warning table. For most XMLs it is working fine.
Looking at the code we can see only one place that can go wrong. I.e. the insert to warning table is done IF some condition is satisfied. The condition is IF lc_local_var1 = gc_global_var1
gc_global_var1 is a constant defined in the package specification.
We are suspecting that the Oracle multithreading , connection pooling is somehow making the gc_global_var1 to NULL for some reason (Oracle bug??).
We can't enable debug logging in PRD as it will slow everything down nor can we replicate this issue in lower environments.
This seems to happen when lots of XMLs are processed one after the other by the ESB and when the XML is at the end. I.e. If 17 XMLs are processed by the ESB this does seem to happen if the XML is no 2 or 3 but happens if it is 14 or 15.
DB is Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 .
Any advice would be greatly appreciated.