Package is not executing while using cursor input

Question

I have created a Package from existing procedure , procedure only is giving exact output but while creating package there is no errors but it is not showing the output file that i passed to be input in cursor

---

------------------PACKAGE AS FOLLOW-------------------------

SET SERVEROUTPUT ON;
CREATE OR REPLACE PACKAGE user.report AS
    PROCEDURE distribution (
        code_in   IN             user.test.schm_code%TYPE,
        fromdate       IN             DATE,
        todate         IN             DATE
    );

END report;
/
-------------------------------------------------
----------------Package Body---------------------
-------------------------------------------------

CREATE OR REPLACE PACKAGE BODY user.report as
----------------------------------------------
----------VARIABLE DECLARATION----------------
----------------------------------------------

    code_in             user.test.code%TYPE;
    fromdate                 DATE;
    todate                   DATE;
    v_code              user.test.code%TYPE;    
    v_count                 number;
    v_code          user.test.acct_crncy_code%TYPE;
    v_amount        number(17,4);
-----------------------------------------------
---------------CURSOR DECLARATION--------------
-----------------------------------------------


CURSOR td_data IS
    ( SELECT
        test.code,
        COUNT(test.code) AS count,
        test.crncy
    FROM
        user.table1
        JOIN user.test ON test.id = table1.id
    WHERE
        user.test.opendate BETWEEN TO_DATE(fromdate, 'dd-mm-yyyy') AND TO_DATE(todate, 'dd-mm-yyyy')
        and
        user.test.code = code_in
    GROUP BY
        test.code,test.crncy
    );

------------------------PROCEDURE CREATION-----------------------    
PROCEDURE distribution 
(
    code_in   IN             user.test.code%TYPE,
    fromdate       IN          DATE,
    todate         IN          DATE
)
AS
BEGIN
    dbms_output.put_line('code'||'    '||'COUNT'||'       '||'TOTAL');
    OPEN td_data;
    LOOP
        FETCH td_data INTO v_code,v_count,v_code,v_amount;
        EXIT WHEN td_data%notfound;
        dbms_output.put_line(v_code
                             || '             '
                             ||v_count
                             || '                '
                             ||v_code
                             ||' '
                             ||v_amount
                             );

    END LOOP;

    CLOSE td_data;
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line(SQLERRM);
END distribution;
END report;
/
BEGIN
user.report.distribution('x1fsr','01-02-2016','01-07-2020');
END;
/

alter session set nls_date_format='dd-mm-yyyy'; 

I have tried creating package without cursor working fine I have tried creating cursor without user input workking fine but with user input it is not working properly

Answer 1

Variables you used were not initialized so cursor didn't fetch anything. That's because you declared it (as well as bunch of variables) globally (for the whole package body), not locally (for the procedure, while you're passing parameters to a procedure and never passed any of them to cursor. If you insist, create a parametrized cursor).

Also, you've declared V_CODE twice; why?

Anyway: I'd suggest you to do it this way:

• use a cursor FOR loop as it is easier to maintain (you don't have to open, fetch, exit, close - Oracle does that for you)
• don't declare any additional variables - use implicit cursor variables
• remove such an exception handler, it does no good at all as you aren't handling anything. If you called the procedure from some front-end application (such as Apex or Forms) and something ("others", eh?) happens, you wouldn't see anything as DBMS_OUTPUT.PUT_LINE doesn't work there
• as fromdate and todate are DATEs, no need to TO_DATE them

OK, here you go:

CREATE OR REPLACE PACKAGE BODY USER.report
AS
   PROCEDURE distribution (code_in   IN USER.test.code%TYPE,
                           fromdate  IN DATE,
                           todate    IN DATE)
   AS
   BEGIN
      DBMS_OUTPUT.put_line (
         'code' || '    ' || 'COUNT' || '       ' || 'TOTAL');

      FOR cur_r
         IN (  SELECT test.code, COUNT (test.code) AS COUNT, test.crncy
                 FROM USER.table1 JOIN USER.test ON test.id = table1.id
                WHERE     USER.test.opendate BETWEEN fromdate AND todate
                      AND USER.test.code = code_in
             GROUP BY test.code, test.crncy)
      LOOP
         DBMS_OUTPUT.put_line (
               v_code
            || '             '
            || cur_r.code
            || '                '
            || cur_r.COUNT
            || ' '
            || cur_r.crncy);
      END LOOP;
   END distribution;
END report;