I got some spring batch jobs running on multipartitions...I wanted to use oracle's global temporary tables as my batch job's staging table so that the staging table lives only during my job run...As of now I have physical tables
I have all these running in tomcat. which has tomcat connection pool implemented...
So my staging table gets populated by the staging writer. I could see the data. Everything looks fine until now
Until this point my job is single threaded.. After this there are multireaders all reading different range of records in the staging table..
Staging Reader is partitioned and each partition is using one connection from the pool.. So most of the readers are not able to see the data in the temporary table.. May be one or 2 readers and I could assume that they are using the same session in which the table was created and inserted
Is there a way to make the data available for all the readers using the connection pool?
The data in a global temporary table will only ever be visible in the session that inserted it. The table itself will always exist unlike other databases that use local temporary tables (your comment that the "table lives only during my job run" seems to be incorrect).
If you want the data to be visible across sessions, you would realistically need to load it into a permanent staging table. There are other ways to share data across multiple sessions-- global contexts, for example-- but if you are spawning multiple threads to process the data, those options probably aren't practical for your data volume. Depending on the nature of your processing, you could potentially have one thread in your application distributing data to all the other threads so that only the coordinator needs to query the staging table.