Calling Oracle Stored Procedures from iReport 5.6 and JasperStudio 6.1

Posted on September 2, 2015 at 9:23am

I have an Oracle stored procedure within the package and I'm successfully run this stor proc from Oracle SQLDEVELOPER and TOAD.

When I do call in iReport 5.6 and JasperStudio 6.1 - no errors at all but report is blank, no records.

Is any idea?

Here is what I'm doing:

1. Query Language Executer is plsql;

{call DA.PLZ0018_UNION_PR_PKG.UNION_PR_REPORT($P{ORACLE_REF_CURSOR}, $P{P_COMP_CODE}, $P{P_PAY_DATE})}

2. Stor proc in Oracle:

PROCEDURE Union_PR_Report (CUR_OUT OUT SYS_REFCURSOR, P_COMP_CODE IN VARCHAR2, P_PAY_DATE IN DATE)
IS
BEGIN
   OPEN CUR_OUT FOR
     SELECT JOB.JOB_COMP_CODE AS COMPANY_CODE,
            CASE
               WHEN JOB.JOB_STATUS_CODE = 'I' THEN 'In Prog'
               WHEN JOB.JOB_STATUS_CODE = 'C' THEN 'Comp'
            END
               AS JOB_STATUS,
            JOB.JOB_CODE AS JOB_CODE,
            JOB.JOB_NAME AS JOB_NAME,
            PY_EMP.EMP_NO AS PY_EMP_NO,
            PY_EMP.EMP_FIRST_NAME AS PY_EMP_FIRST_NAME,
            PY_EMP.EMP_MIDDLE_NAME AS PY_EMP_MIDDLE_NAME,
            PY_EMP.EMP_LAST_NAME AS PY_EMP_LAST_NAME,
            SUM (CASE WHEN PHY_TRAN_TYPE = 'BW' THEN PHY_AMOUNT END)
               AS PHY_AMOUNT_BW,
            SUM (CASE WHEN PHY_TRAN_TYPE = 'BN' THEN PHY_AMOUNT END)
               AS PHY_AMOUNT_BN,
              SUM (CASE WHEN PHY_TRAN_TYPE = 'BN' THEN PHY_AMOUNT END)
            / SUM (
                 NULLIF (
                    CASE WHEN PHY_TRAN_TYPE = 'BW' THEN PHY_AMOUNT ELSE 0 END,
                    0))
               AS BURDEN_PERCENT
       FROM PYEMPPAYHIST PY_PAYHIST
            INNER JOIN JCJOB JOB
               ON     JOB.job_comp_code = PY_PAYHIST.PHY_COMP_CODE
                  AND JOB.JOB_CODE = PY_PAYHIST.PHY_JOB
            INNER JOIN DA.PYEMPLOYEE PY_EMP
               ON     PY_PAYHIST.PHY_COMP_CODE = PY_EMP.EMP_COMP_CODE
                  AND PY_PAYHIST.PHY_EMP_NO = PY_EMP.EMP_NO
      WHERE     PY_PAYHIST.PHY_COMP_CODE = P_COMP_CODE --SYS_CONTEXT ('CMIC', 'P_COMP_CODE')
            AND PY_PAYHIST.PHY_JOB IS NOT NULL
            AND PY_PAYHIST.phy_union_code IS NOT NULL
            AND JOB.JOB_STATUS_CODE IN ('I', 'C')
            AND trunc(PY_PAYHIST.PHY_PAY_DATE) = trunc(P_PAY_DATE)
                   --TO_DATE (SYS_CONTEXT ('CMIC', 'P_PAY_DATE'), 'YYYYMMDD') --'23-JUL-15'
   GROUP BY JOB.JOB_COMP_CODE,
            JOB.JOB_STATUS_CODE,
            JOB.JOB_CODE,
            JOB.JOB_NAME,
            PY_EMP.EMP_NO,
            PY_EMP.EMP_FIRST_NAME,
            PY_EMP.EMP_MIDDLE_NAME,
            PY_EMP.EMP_LAST_NAME;