PL/SQL function called twice from a Jasper Server Report

0
Hi All,
 
I have a report that selects data from an Oracle PL/SQL function. This function executes a quite complex logic behind and returns a table to the query executor (JasperServer).
 
If I run this report from JasperServer just by clicking the report name it works well and returns the results as expected, the same if I run it from JasperSoft Studio.
 
However, if I set up a scheduler and the report is run from the scheduler on the given time, it also runs well and returns correct results, but the PL/SQL function in executed TWICE on the Oracle database. I can see it because the function produces log messages in the database (twice). The second execution starts immediatelly after the first one completes. This has never happened to me when selecting from tables or views or other functions. And this is happening ONLY when the report is scheduled or "Run in Background...".
 
Does anybody have any ideas why this is happening and what might be the reason for that behaviour?
 
 
Part of my report XML is below.
 
    -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
    <queryString language="SQL">
    <![CDATA[SELECT (TRUNC(SYSDATE) - to_number(TO_CHAR(sysdate, 'd')) - 6) week_start,
      (TRUNC(SYSDATE)      - to_number(TO_CHAR(sysdate, 'd')) + 1 - 1/24/3600) week_end,
      b15.subrepid,
      b15.subrepname,
      b15.numof,
      b15.volume,
      b15.revenue
    FROM TABLE(bs01.TEST_XBTOP_P_reports.bsr015()) b15]]>
    </queryString>
    <field name="WEEK_START" class="java.sql.Timestamp"/>
    <field name="WEEK_END" class="java.sql.Timestamp"/>
    <field name="SUBREPID" class="java.lang.String"/>
    <field name="SUBREPNAME" class="java.lang.String"/>
    <field name="NUMOF" class="java.math.BigDecimal"/>
    <field name="VOLUME" class="java.math.BigDecimal"/>
    <field name="REVENUE" class="java.math.BigDecimal"/>
    <sortField name="SUBREPID"/>
    -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
 
My PL/SQL function is:
 
    -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
    CREATE OR REPLACE PACKAGE body TEST_XBTOP_P_reports
    AS
      -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
      FUNCTION bsr015
        RETURN xbtop_y_bsr015_tab
      AS
        r_tab xbtop_y_bsr015_tab := xbtop_y_bsr015_tab();
        subrepid              CHAR(2);
        subrepname            CHAR(35);
        numof                 NUMBER;
        volume                NUMBER;
        revenue               NUMBER ;
        systimestamp_hostname VARCHAR2(200 CHAR);
      BEGIN
    -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
 
 
I run JasperReports Server Community 6.3 and Oracle SE 11.2.0.3.0.
 
Any ideas would be more then appreciated.
 
Thank you and kind regards
 
Pawel
 
hadzio's picture
4
Joined: Nov 2 2016 - 3:40am
Last seen: 5 days 20 hours ago

Does the scheduled job produce both paginated and non-paginated output? Jasper will execute the query twice in that case. Otherwise, is there anything else exceptional about the scheduled job?

 

darth_fader - 1 week 3 days ago
Not about paginated and non-paginated, but I have just noticed that my reports generate two files, HTML and Excel and then they run the SQL query twice. If I generate only one output format, the SQL is run only once. However, when I specify 4 output formats (e.g. Html, Excel, PDF, CSV) the SQL is still run only twice and not four times. Is this expected?
 
hadzio - 6 days 20 hours ago

0 Answers:

No answers yet
Feedback
randomness