Does version 4.2 create a new db session for each report run on a page?
The reason I ask is we have a page with 4 report regions, each querying a global temporary table that is populated in a 'Before Regions' page process. The first report displays the correct data, but the others return 'No Data Found' and it is because the Global Temporary Table has been cleared out following the first report. This did not happen in 4.1.
I know we should be using Apex Collections, and we will in future, but time is at a premium to get this live so if anyone knows a quick fix I would be extremely grateful.
I'd just like to add that the page worked again when the global temporary tables (GTT) were changed from "ON COMMIT DELETE ROWS" to "ON COMMIT PRESERVE ROWS".
This would seem to imply that a COMMIT occurs after running report region 1, which empties the global temporary table.
That strikes me as wrong - I would have hoped that the page was "transactional":-
* Procedure populates GTTs
* Report region 1 SQL runs, querying GTT
* Report region 2 SQL runs, querying GTT
* Report region 3 SQL runs, querying GTT
* Report region 4 SQL runs, querying GTT
* After page is rendered, COMMIT is issued - and GTTs rows are deleted.
Edited by: Eeyore on 24-Nov-2012 15:15
As a suggestion, since APEX doesn't play well with Global Temporary Tables, maybe you could instead use a collection for your report(s) to be based upon?
Noted issues with GTT's and APEX: