3 Replies Latest reply: Nov 26, 2012 8:06 AM by scott.wesley RSS

    4.2 new database sessions per report region

    Nick Walsh
      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
          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
            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
              This may also be an interesting & related read
              http://www.danielmcghan.us/2012/08/implicit-commits-in-apex.html

              Scott