Using multiple procedures and multiple cursors in a package

Question

I have created a package with two procedures and two cursors in it, but while executing the procedure, it is executed successful, but same record executed multiple times and a buffer overflow occurred.

I also tried removing the loop from the cursor but for 1 record that will be fine and for multiple record it won't work as expected.

EXPECTED

I just need to remove multiple execution of same record from the procedure where i am getting multiple execution of same record

for single procedure and single cursor it is working properly but for multiple cursor and multiple procedure i am getting problem here which caused buffer overflow too where i need different record

Is there any alternative way that I can fix the problem ?

CREATE OR REPLACE PACKAGE test.report AS
    PROCEDURE distribution (
        code_in   IN             user.test.code%TYPE,
        fromdate      date,
        todate          date
    );

    PROCEDURE tdvalue (
    id   IN          user.test.custid%TYPE
    );

END report;
/

Package Body

CREATE OR REPLACE PACKAGE BODY test.report as

----------VARIABLE DECLARATION----------------

    code_in             user.test.code%TYPE;
    custidin                user.test.custid%TYPE;
    fromdate                 DATE;
    todate                   DATE;
    diff                    number(17,2);
---------------CURSOR DECLARATION--------------


CURSOR td_data(code_in  user.test.code%TYPE,
                fromdate date,
                todate date
) IS

    ( SELECT
        test.code,
        COUNT(test.code) AS count,
        SUM(test2.Deposit_amount) AS total,
        test.currency
    FROM
        user.test2
        JOIN user.test ON test2.acid = test.acid
    WHERE
        user.test2.open_effective_date 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.currency
    );

    td__data        td_data%rowtype;


CURSOR C_DATA(custidin   user.test.custid%TYPE)  IS   SELECT
            test.custid,
            test2.id,
            TO_DATE(test2.initial_date, 'dd-mm-yyyy') - TO_DATE(test2.end_date, 'dd-mm-yyyy') AS noofdays,
            round(((test2.deposit_amount *((TO_DATE(test2.initial_date, 'dd-mm-yyyy') - TO_DATE(test2.end_date, 'dd-mm-yyyy'
            )) / 365) * test4.interest_rate) / 100), 2) + test2.deposit_amount AS calculated_amount,
            SUM(test.flow_amt) + test2.deposit_amount AS system_amount
        FROM
            user.test
            JOIN user.test2 ON test3.entity_id = test2.id
        WHERE
            test.custid = custidin
        GROUP BY
            test.custid,
            test2.id;

    c__data         c_data%ROWTYPE;


PROCEDURE distribution 
(
    code_in   IN             user.test.code%TYPE,
    fromdate in date,
    todate in  date
)

AS
BEGIN
    OPEN td_data(code_in,fromdate,todate);
    loop
        FETCH td_data INTO td__data;
        dbms_output.put_line(td__data.code
                             || '             '
                             || td__data.count
                             || '                '
                             ||td__data.currency
                             ||' '
                             ||td__data.total
                             );
            end loop;                
    CLOSE td_data;
END distribution;

PROCEDURE tdvalue (
    custidin   IN          user.test.custid%TYPE
    )
AS
BEGIN   
    open c_data(custidin);
    fetch c_data into c__data;
    loop
    diff:= c__data.calculated_amount- c__data.system_amount;        
        dbms_output.put_line(c__data.custid
                             || '   '
                             || c__data.noofdays
                             || '          '
                             || c__data.end_date
                             || '               '
                             || c__data.initial_date
                             || '                 '
                             || c__data.calculated_amount
                             ||'     '
                             ||diff
                             );
    end loop;
    close c_data;
END tdvalue;
END report;
/

To run

ALTER SESSION set nls_date_format='dd-mm-yyyy';
SET SERVEROUTPUT ON;

EXEC REPORT.DISTRIBUTION('872328','01-02-2016','08-02-2019');
/
EXEC REPORT.tdvalue('S9292879383SS53');

Answer 1

Buffer overflow - ORU-10027 - happens when the total number of bytes displayed through DBMS_OUTPUT exceeds the size of the serveroutput buffer. The default is only 20000 bytes (who knows why?). Your session is using that default because of how you enable serveroutput. Clearly one record is less than 2000 and you only hit that limit when you run for multiple records.

To fix this try this

SET SERVEROUTPUT ON size unlimited

It's not actually unlimited, but the upper bound is the PGA limit (session memory) and you really shouldn't hit that limit with DBMS_OUTPUT. Apart from anything else who would read all that?

---

So the other problem with your code - as @piezol points out - is that your loops have no exit points. You should test whether the FETCH actually fetched anything and exit if it didn't:

loop
    FETCH td_data INTO td__data;
    exit when td_data%notfound; 

    dbms_output.put_line(td__data.code
                         || '             '
                         || td__data.count
                         || '                '
                         ||td__data.currency
                         ||' '
                         ||td__data.total
                         );
 end loop;  

Remembering to do this is just one reason why implicit cursors and cursor for loops are preferred over explicit cursors.

The second cursor loop is even worse because not only does it not have an exist point, the fetch is outside the loop. That's why you have repeated output for the same record.

So let's rewrite this ...

open c_data(custidin);
fetch c_data into c__data;  -- should be inside 
loop
diff:= c__data.calculated_amount- c__data.system_amount;      

… as a cursor for loop:

PROCEDURE tdvalue (
    custidin   IN          user.test.custid%TYPE
    )
AS
BEGIN   
    for c__data in c_data(custidin)
    loop
        diff:= c__data.calculated_amount- c__data.system_amount;        
        dbms_output.put_line(c__data.custid
                             || '   '
                             || c__data.noofdays
                             || '          '
                             || c__data.end_date
                             || '               '
                             || c__data.initial_date
                             || '                 '
                             || c__data.calculated_amount
                             ||'     '
                             ||diff
                             );
    end loop;
END tdvalue;

No need for OPEN, CLOSE or FETCH, and no need to check when the cursor is exhausted.

Answer 2

In PL/SQL, the preferred mechanism for setting the DBMS_OUTPUT buffer size would be within your procedure. This has the benefit of working in any client tool, such as Java or Toad (though it is still up to the client tool to retrieve the output from DBMS_OUTPUT).

DBMS_Output.ENABLE

Pass in a parameter of NULL for unlimited buffer size.

It would go like this:

BEGIN
  DBMS_OUTPUT.ENABLE(NULL);
  FOR I IN 1..1000 LOOP
    DBMS_OUTPUT.PUT_LINE('The quick red fox jumps over the lazy brown dog.');
  END LOOP;
END;
/

Bonus fact:

You can use the other functions and procedures in DBMS_OUTPUT to roll your own if you aren't using SQL*Plus or a DBMS_OUTPUT-savvy tool like Toad. You can use the GET_LINE or GET_LINES procedures from your client code to get whatever may have been written to DBMS_OUTPUT.

GET_LINE