PL/SQL function called twice from a Jasper Server Report

[hadzio]

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

 

[darth_fader]

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?

 

[hadzio]

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?

 

[darth_fader]

no, i'm fairly certain the report query would execute for each output format as Jasper does specific in memory calculations/processing with the query results for each; so you should be seeing this four times. Even if the query execution/results were being cached, then I would expect one query execution for all four output formats. Two runs just doesn't seem logical here.