This discussion is archived
11 Replies Latest reply: Jan 15, 2013 12:54 PM by cloaked RSS

Regular Report Not Returning All Rows

cloaked Newbie
Currently Being Moderated
Greetings,

On APEX version 4.1.1.00.23 Using latest versions of Chrome and IE. I found some threads that discussed this aspect some, but not to the extent that is helpful to my situation.

I know it is crazy to have APEX return 40K+ rows on a report, but I have a need to do so. I need to return that many rows from a table so that an APEX page can be opened via Excel and then have an Excel macro run over the report results and import all 40K+ rows into a worksheet. As I said, that's crazy, but the decision isn't mine. :-) We have the macro working and it passes parameters to the APEX page and imports data from the APEX page.

Also, the report has 14 columns on it, not that many.

Given the requirement above I have a regular report page that only has the report and 2 page items on it. The page items contain values that are passed via Excel and are then used in the WHERE condition of the report. Using the same WHERE condition, when I run the SQL for the report outside of APEX it returns 46,840 rows. I have the both the Number of Rows and Maximum Row Count settings set to 50000. And, I have the Pagination Scheme set to Row Ranges X to Y of Z (with pagination).

When I run the page and enter the selection criteria the report only returns 15,500 rows. Yes, that's a lot rows. And, it takes about 5 minutes to populate. But, it doesn't return ALL of the rows. Also, the pagination suggests that all rows were returned because it reads - row(s) 1 to 15500 of 15500. I have changed the pagination and max rows settings a lot to see of it's just a matter of having the correct report setting. I've yet to find a setting that will cause the whole 46840 result set to be returned.

It may end up that the report selection needs to change in order to return fewer rows. But, regardless of that why isn't APEX returning the complete result set now? And, is there a way to force it to return the complete result set regardless of the size?

Any suggestions are appreciated.

Thanks, Tony
  • 1. Re: Regular Report Not Returning All Rows
    MarkoGoricki Journeyer
    Currently Being Moderated
    Hi Tony,

    Do you use IR or classic report?

    I've created example on apex.oracle.com displaying 80000 rows:

    Classic:
    http://apex.oracle.com/pls/apex/f?p=2072:3

    IR:
    http://apex.oracle.com/pls/apex/f?p=2072:4:

    Query:
    select level as lvl
    from dual
    connect by level <= 80000

    Try to logout and login when after you change rows per page property.

    Br,
    Marko Goricki
    -------------------
    http://apexbyg.blogspot.com/
  • 2. Re: Regular Report Not Returning All Rows
    Tom Petrus Expert
    Currently Being Moderated
    Tony,

    In case you did not know: IRs have a built-in download functionality that allows users to extract their resultset to a CSV. Classic reports also have the possibility to serve a CSV, but require you to set this up in the report attributes (enable it and set some of the usual csv parameters). That should work a whole lot better than trying to load nearly 50k rows into a page, and a hell of a lot faster than 5 minutes.
  • 3. Re: Regular Report Not Returning All Rows
    cloaked Newbie
    Currently Being Moderated
    Hi Marko,

    In answer to your question it is a classic report.

    Thanks for your examples, but they don't exactly apply to what I need. I need all 45,000 rows to display on the same page without any pagination. (I only have pagination set up on the page in order for APEX to tell me how many rows it has returned, otherwise it would be difficult to tell.)

    Thanks, Tony
  • 4. Re: Regular Report Not Returning All Rows
    cloaked Newbie
    Currently Being Moderated
    Hi Tom,

    Thanks for the response. Yup, you're correct. Downloading to a CSV might be better. I've set reports up that way many, many times. I have even developed pages that import from a CSV into APEX using PL/SQL, years before it was an APEX feature. I initially set the report up to allow it to be downloaded, but the user doesn't want it to work that way.

    The worksheet will be used by a lot of people in our accounting department and they want the process to be as simple as possible. Right now Excel opens up the APEX page, logs into the application, passes parameters to it for the month and company, hits the Select button, waits on the report to build, then automatically imports it, and finally closes the page. It is quite slick. Yes, it takes 5 minutes to run, but the user is OK with that given what the automation provides.

    So, in order for the automated import to work properly all 45K rows need to display on one page, without pagination. I currently have pagination on the page simply to determine if APEX is returning all of the rows.

    Thanks, Tony
  • 5. Re: Regular Report Not Returning All Rows
    MarkoGoricki Journeyer
    Currently Being Moderated
    Hi Tony,

    I think it's not problem with APEX. Look, 80000 records on one page:
    http://apex.oracle.com/pls/apex/f?p=2072:3

    You can also try to output HTML table with sys.htp.prn procedure.

    Maybe GET request is too big.

    Br,
    Marko Goricki
    -----------------------------------
    http://apexbyg.blogspot.com/
  • 6. Re: Regular Report Not Returning All Rows
    fac586 Guru
    Currently Being Moderated
    cloaked wrote:

    Thanks for the response. Yup, you're correct. Downloading to a CSV might be better. I've set reports up that way many, many times. I have even developed pages that import from a CSV into APEX using PL/SQL, years before it was an APEX feature. I initially set the report up to allow it to be downloaded, but the user doesn't want it to work that way.

    The worksheet will be used by a lot of people in our accounting department and they want the process to be as simple as possible. Right now Excel opens up the APEX page, logs into the application, passes parameters to it for the month and company, hits the Select button, waits on the report to build, then automatically imports it, and finally closes the page. It is quite slick. Yes, it takes 5 minutes to run, but the user is OK with that given what the automation provides.

    So, in order for the automated import to work properly all 45K rows need to display on one page, without pagination. I currently have pagination on the page simply to determine if APEX is returning all of the rows.
    A process of this nature might work better using an export: XML or export: CSV report template, which won't go anywhere near pagination. When a page containing a report using these templates is requested APEX sends the report results in XML or CSV format rather than rendering the page. This should be faster, avoid complications with pagination, and be easier to parse in Excel.

    Previous thread along similar lines: +{thread:id=2285213}+
  • 7. Re: Regular Report Not Returning All Rows
    cloaked Newbie
    Currently Being Moderated
    Thanks Marko,

    I didn't think about using sys.htp.prn I've used it before, but only in special situations. This may be one of those. That may be route I need to take.

    Tony
  • 8. Re: Regular Report Not Returning All Rows
    cloaked Newbie
    Currently Being Moderated
    Thanks fac586,

    I've never used the template type of export: XML before, but I'm always willing to try new things. That may end up what I need to use, if the user is OK with it.

    So, I just changed the page as per the reference you sent me. I went to the Layout and Pagination region and changed the Report Template to export: XML. That was the only change I made. Now, the page acts errr... weird. In uncharted territory so I don't know if this is typical. When I click on the tab for the page it automatically does the export. (it doesn't open the page) Which would be OK, I guess if the year and company had been selected. But, since they weren't it exports nothing. The page doesn't open, it just downloads when the tab is clicked. Very strange, because the page doesn't have very much on it. A button to submit the page, an item for year, an item for company, and the SQL that selects based on the year and company selected. But, since the page won't open the SQL selects nothing.

    Tony
  • 9. Re: Regular Report Not Returning All Rows
    cloaked Newbie
    Currently Being Moderated
    Oops!

    Just reread the reference material and it states - "Selecting export: XML prevents the Application Express engine from rendering the page and dumps the content to an XML file."

    So, I would need to pass parameters to the page I suppose.

    Tony
  • 10. Re: Regular Report Not Returning All Rows
    matthew_morris Expert
    Currently Being Moderated
    cloaked wrote:
    ...Using the same WHERE condition, when I run the SQL for the report outside of APEX it returns 46,840 rows.
    ...When I run the page and enter the selection criteria the report only returns 15,500 rows.
    ...Also, the pagination suggests that all rows were returned because it reads - row(s) 1 to 15500 of 15500.
    Combining the three statements above suggests to me is that the "SQL for the report outside of APEX" isn't exactly what's being run to generate your Apex report. You haven't provided the SQL, so I can't even begin to guess at what might be different. What I would suggest is to run the SQL outside of Apex and get your 46K rows. Copy and paste that exact SQL into Apex. Do not change anything, including referencing your page items. Run the report. If it still only returns 15,500 rows, then something is screwy.
  • 11. Re: Regular Report Not Returning All Rows
    cloaked Newbie
    Currently Being Moderated
    Excellent point.

    The 2 SQL's weren't identical. One had select columns, one had count(*). But, the WHERE condition was identical in both. I use TOAD for PL/SQL development and it does similar to APEX in that it returns 500 rows at a time, so if I want to know the total rows returned I use count.

    Unfortunately, I'm still stumped about this and am going to punt. The user has agreed to provide an additional selection criteria for the SQL which will cut the result set to a tenth of what it was previously. That should eliminate the issue I was having. And, the report will run a lot faster.

    Maybe I can revisit this one day and determine the root cause. If so, I will post it.

    Thanks for your suggestion.

    Tony

    Edited by: cloaked on Jan 15, 2013 12:54 PM

Legend

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