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