Its version 11.2.0.4 Of Oracle Exadata. We have one application which uses Java as front end and calls plsql procedures. We use connection pooling at Java end. And the plsql procedure in-turn populates global temporary table(On commit preserve row types) after processing some complex business logic based on specific input customer. And that way this functionality is called for multiple customer at same time and the global temporary table results gets moved to another table(which persists data for tracking) and then to those respective customer based on the run_id without any issue.
Now, we are also providing functionality in which this can be called second time for some customer if they sees the data is inaccurate or due to some other issues. And we found in such situation few times, the data gets duplicated i.e. say for RUN_ID "1" we have got "10" rows. And then when it calls second time i.e. with run_id "2" , it give us "20" rows and those are duplicate.
As the global temporary table is same for that functionality say, "GTT_PROC1" So we suspect the second run i.e. run_id-2 is utilizing same session(which may be used from the earlier connection pool) such that the data in the global temporary table gets appended and doubled. So how should we handle such scenario?
Is there some other option through which we can ensure the new call to the procedure will utilize new session this its won't see the old data from earlier run_id , so we wont have the data duplication?