title: Call Oracle Package from Jasper report Having Multiple Cursors
body: <p>I have a Package In Oracle Which Spools Customer Information. The Package is like:</p>
  
  <p><strong>EDIT</strong></p>
  
  <pre><code>create or replace
  PACKAGE        mfi_statement_spool
  AS
     PROCEDURE mfi_main_spool (fromdate IN DATE, todate IN DATE,c1 out SYS_REFCURSOR,c2 out SYS_REFCURSOR);
  
     FUNCTION mfi_cust_details (vforacid IN VARCHAR2)
        RETURN VARCHAR2;
  
     FUNCTION mfi_citycountry (vcountrycode IN VARCHAR2, vcitycode IN VARCHAR2)
        RETURN VARCHAR2;
  END mfi_statement_spool;
      /
  </code></pre>
  
  <p>I need to use the Main_Spool Procedure Which I declared this way:</p>
  
  <p><strong>EDIT FULL PACKAGE BODY</strong></p>
  
  <pre><code>CREATE OR REPLACE
  PACKAGE BODY TBAADM.mfi_statement_spool
  AS
    -- --------------------------------------------------------------------------
  PROCEDURE mfi_main_spool(
      fromdate DATE,
      todate   DATE,
      c1 OUT SYS_REFCURSOR,
      c2 OUT SYS_REFCURSOR)
  AS
    cramount  NUMBER;
    dramount  NUMBER;
    countcr   NUMBER;
    countdr   NUMBER;
    srl_num   NUMBER;
    ledgerbal NUMBER;
    c1 SYS_REFCURSOR;
    c2 SYS_REFCURSOR;
    opbal             NUMBER;
    vdocremarks       VARCHAR2 (100);
    vremarkscorporate VARCHAR2 (100);
    vremarksretail    VARCHAR2 (100);
    remarks           VARCHAR2 (100);
    endbal            NUMBER;
    curamount         NUMBER;
    totaltrans        NUMBER;
    sumtrans          NUMBER;
    vtranamt          VARCHAR2 (50);
    state1            VARCHAR2 (600);
    rnum              NUMBER;
    state2            VARCHAR2 (600);
    state3            VARCHAR2 (600);
    fuunclamt         NUMBER;
    state4            VARCHAR2 (600);
    balancebf         VARCHAR2 (50);
    ebal              VARCHAR2 (50);
    lbal              VARCHAR2 (50);
    vinstrnumcheck    VARCHAR2 (15);
    vinstrnum         VARCHAR2 (50);
    vpaart            VARCHAR2 (50);
    wfile_handle UTL_FILE.file_type;
    filename VARCHAR2(100);
    CURSOR c1
    IS
      SELECT foracid,
        acct_name,
        addr_1,
        addr_2,
        sol_desc,
        city_code,
        gam.FUTURE_UN_CLR_BAL_AMT
      FROM tbaadm.gam,
        tbaadm.sol
      WHERE gam.sol_id   = sol.sol_id
      AND schm_type     IN ('CAA','ODA','LAA')
      AND schm_code NOT IN ('CTSTF');
    CURSOR c2 (vforacid VARCHAR2)
    IS
      SELECT TRIM (foracid),
        tran_amt,
        part_tran_type,
        acct_name,
        gam.cust_id,
        clr_bal_amt,
        gam.acid,
        un_clr_bal_amt,
        acct_crncy_code,
        tran_date,
        htd.value_date,
        htd.instrmnt_num,
        htd.tran_particular
      FROM tbaadm.gam,
        tbaadm.htd
      WHERE gam.acid     = htd.acid
      AND foracid        = vforacid
      AND tran_date     &gt;= fromdate
      AND tran_date     &lt;= todate
      AND pstd_flg       = 'Y'
      AND htd.del_flg   != 'Y'
      AND acct_cls_flg  != 'Y'
      AND schm_type     IN ('CAA','ODA','LAA')
      AND schm_code NOT IN ('CTSTF')
      ORDER BY foracid,
        htd.pstd_date ;
  BEGIN
    FOR i IN c1
    LOOP
      rnum         := 0;
      filename     := 'statement_' || TO_CHAR(i.foracid) || '.TXT';
      wfile_handle := UTL_FILE.fopen ('DIR_TEMP', filename, 'W', 32767);
      BEGIN
        SELECT NVL (SUM (tran_date_bal), 0)
        INTO opbal
        FROM tbaadm.gam,
          tbaadm.eab
        WHERE gam.acid  = eab.acid
        AND gam.foracid = i.foracid
        AND eod_date    =
          (SELECT MAX(eod_date)
          FROM tbaadm.eab
          WHERE eod_date &lt; fromdate
          AND acid      IN
            (SELECT acid FROM tbaadm.gam WHERE foracid=i.foracid
            )
          );
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
        opbal := 0;
      END;
      BEGIN
        IF opbal &lt; 0 THEN
          SELECT REPLACE(concat(TO_CHAR(opbal,'9999999990D99') ,' DR'),'-')
          INTO balancebf
          FROM dual;
        ELSE
          SELECT concat(TO_CHAR(opbal,'9999999990D99') ,' CR') INTO balancebf FROM dual;
        END IF;
      END;
      BEGIN
        SELECT NVL (SUM (DECODE (part_tran_type, 'D', -1 * tran_amt, tran_amt ) ), 0 )
        INTO sumtrans
        FROM tbaadm.htd
        WHERE acid =
          (SELECT acid FROM tbaadm.gam WHERE foracid = i.foracid
          )
        AND pstd_flg   = 'Y'
        AND del_flg   != 'Y'
        AND tran_date &gt;= fromdate
        AND tran_date &lt;= todate;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
        sumtrans := 0;
      END;
      endbal := sumtrans + opbal;
      BEGIN
        IF endbal &lt; 0 THEN
          SELECT REPLACE(concat(TO_CHAR(endbal,'9999999990D99') ,' DR'),'-')
          INTO ebal
          FROM dual;
        ELSE
          SELECT concat(TO_CHAR(endbal,'9999999990D99') ,' CR') INTO ebal FROM dual;
        END IF;
      END;
      BEGIN
        SELECT NVL (SUM (DECODE (part_tran_type, 'D', 1, 0)), 0),
          NVL (SUM (DECODE (part_tran_type, 'C', 1, 0)), 0)
        INTO countdr,
          countcr
        FROM tbaadm.htd
        WHERE acid IN
          (SELECT acid FROM tbaadm.gam WHERE foracid = i.foracid
          )
        AND tran_date &gt;= fromdate
        AND tran_date &lt;= todate
        AND pstd_flg   = 'Y'
        AND del_flg   != 'Y';
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
        countcr := 0;
        countdr := 0;
      END;
      totaltrans := countcr + countdr;
      FOR j                IN c2 (i.foracid)
      LOOP
        rnum := rnum + 1;
        SELECT TO_CHAR (DECODE (j.part_tran_type, 'C', j.tran_amt, '0.00'),'9999999990D99')
          || '|'
          || TO_CHAR (DECODE (j.part_tran_type, 'D', j.tran_amt, '0.00'),'9999999990D99')
        INTO vtranamt
        FROM DUAL;
        SELECT DECODE (j.part_tran_type, 'D', -1 * j.tran_amt, j.tran_amt)
        INTO curamount
        FROM DUAL;
        opbal   := opbal + curamount;
        IF opbal &lt; 0 THEN
          SELECT REPLACE(concat(TO_CHAR(opbal,'9999999990D99') ,' DR'),'-')
          INTO lbal
          FROM dual;
        ELSE
          SELECT concat(TO_CHAR(opbal,'9999999990D99') ,' CR') INTO lbal FROM dual;
        END IF;
        BEGIN
          BEGIN
            SELECT DISTINCT purgeremarks
            INTO vremarksretail
            FROM crmuser.accounts
            WHERE orgkey =
              (SELECT cif_id FROM tbaadm.cmg WHERE cmg.cust_id =j.cust_id
              )
            AND rownum &lt; 2;
          EXCEPTION
          WHEN NO_DATA_FOUND THEN
            vremarksretail := NULL;
          END;
          IF (vremarksretail IS NULL) THEN
            BEGIN
              SELECT DISTINCT remarks
              INTO vremarkscorporate
              FROM crmuser.corporate
              WHERE corp_key =
                (SELECT cif_id FROM tbaadm.cmg WHERE cmg.cust_id =j.cust_id
                )
              AND rownum &lt; 2;
            EXCEPTION
            WHEN NO_DATA_FOUND THEN
              vremarkscorporate := NULL;
            END;
            remarks := vremarkscorporate;
          ELSE
            remarks := vremarksretail;
          END IF;
        END;
        BEGIN
          SELECT DISTINCT docremarks
          INTO vdocremarks
          FROM crmuser.entitydocument
          WHERE core_cust_id = j.cust_id
          AND rownum         &lt; 2;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
          vdocremarks := NULL;
        END;
        BEGIN
          SELECT SUBSTR (j.tran_particular, 1, 7) INTO vinstrnumcheck FROM DUAL;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
          vinstrnumcheck := NULL;
        END;
        IF (vinstrnumcheck = 'BY INST') THEN
          vpaart          := 'OUTWARD CLEARING KES';
        ELSE
          vpaart := j.tran_particular;
        END IF;
        IF (vinstrnumcheck = 'BY INST') THEN
          SELECT SUBSTR (j.tran_particular, 8, (INSTR (j.tran_particular, ':') - 8) )
          INTO vinstrnum
          FROM DUAL;
        ELSE
          vinstrnum := j.instrmnt_num;
        END IF;
        BEGIN
          SELECT FUTURE_UN_CLR_BAL_AMT
          INTO fuunclamt
          FROM tbaadm.gam g
          WHERE g.foracid=i.foracid;
        END;
        BEGIN
          UTL_FILE.put_line (wfile_handle, rnum || '|' || i.foracid || '|' || j.acct_crncy_code || '|' || j.tran_date || '|' || j.value_date || '|' || vinstrnum || '|' || vpaart || '|' || vtranamt || '|' || lbal || '|' || j.acct_name || '|' || mfi_cust_details (i.foracid) || '|' || ebal || '|' || countdr || '|' || countcr || '|' || j.un_clr_bal_amt || '|' || balancebf || '|' || fromdate || '|' || todate || '|' || todate || '|' || remarks || '|' || vdocremarks || '|' || fuunclamt );
        END;
      END LOOP;
      rnum := rnum - 1;
      UTL_FILE.fclose (wfile_handle);
    END LOOP;
  END mfi_main_spool;
  -- ---------------------------------------------------------------------------
  FUNCTION mfi_cust_details(
      vforacid VARCHAR2)
    RETURN VARCHAR2
  IS
    vcustdetails VARCHAR2 (300);
  BEGIN
    BEGIN
      SELECT a.cust_title_code
        || '|'
        || a.cust_id
        || '|'
        || b.address_line1
        || '|'
        || b.address_line2
        || '|'
        || mfi_citycountry (b.country, b.city)
        || '|'
        || b.zip
      INTO vcustdetails
      FROM tbaadm.cmg a,
        crmuser.address b
      WHERE TRIM (a.cif_id)          = TRIM (b.orgkey)
      AND UPPER (b.addresscategory) IN ('MAILING', 'REGISTERED')
      AND cust_id                   IN
        (SELECT cust_id FROM gam WHERE foracid = vforacid
        );
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
      vcustdetails := NULL || '|' || NULL || '|' || NULL || '|' || NULL || '|' || NULL || '|' || NULL || '|' || NULL;
    END;
    RETURN vcustdetails;
  END mfi_cust_details;
  FUNCTION mfi_citycountry(
      vcountrycode VARCHAR2,
      vcitycode    VARCHAR2)
    RETURN VARCHAR2
  IS
    vcountry      VARCHAR2 (50);
    vcity         VARCHAR2 (50);
    vcity_country VARCHAR2 (100);
  BEGIN
    BEGIN
      SELECT ref_desc
      INTO vcountry
      FROM tbaadm.rct
      WHERE ref_rec_type = '03'
      AND ref_code       = vcountrycode;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
      vcountry := NULL;
    END;
    BEGIN
      SELECT ref_desc
      INTO vcity
      FROM tbaadm.rct
      WHERE ref_rec_type = '01'
      AND ref_code       = vcitycode;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
      vcity := NULL;
    END;
    vcity_country := vcity || '|' || vcountry;
    RETURN vcity_country;
  END mfi_citycountry;
  -- -----------------------------------------------------------------------
  END mfi_statement_spool;
  /
  </code></pre>
  
  <p><strong>EDIT: THE ERROR IS STILL THERE</strong></p>
  
  <p>I have Two Cursors In the Procedure Above, Cursor 1 And Cursor Two. I have tried to add the Cursors into the declarations like:</p>
  
  <pre><code>...PROCEDURE mfi_main_spool (fromdate DATE, todate DATE,c1 out SYS_REFCURSOR,c2 out SYS_REFCURSOR).....
  </code></pre>
  
  <p>So that it I can use it with Jasper but the package Body does Not Compile with the Following Error:</p>
  
  <pre><code>PLS-00323: subprogram or cursor 'MFI_MAIN_SPOOL' is declared in a package specification and must be defined in the package body
  </code></pre>
  
  <p>Is there Something I am not doing Correctly?</p>