This discussion is archived
3 Replies Latest reply: Nov 26, 2012 6:06 AM by scott.wesley RSS

4.2 new database sessions per report region

Nick Walsh Newbie
Currently Being Moderated
Hi,

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.

Thanks,

Nick.
  • 1. Re: 4.2 new database sessions per report region
    Jonathan Taylor Newbie
    Currently Being Moderated
    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
  • 2. Re: 4.2 new database sessions per report region
    TexasApexDeveloper Guru
    Currently Being Moderated
    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:

    https://forums.oracle.com/forums/search.jspa?threadID=&q=global+temporary+tables&objID=f137&dateRange=all&userID=&numResults=15

    Thank you,

    Tony Miller
    Ruckersville, VA
  • 3. Re: 4.2 new database sessions per report region
    scott.wesley Guru
    Currently Being Moderated
    This may also be an interesting & related read
    http://www.danielmcghan.us/2012/08/implicit-commits-in-apex.html

    Scott

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points