buffer overflow, limit of 1000000 bytes in oracle database

Question

I'm facing buffer overflow issue in oracle db.There are some similar questions to my question in stackoverflow. But no one could solve my problem. So I am adding below my sql query and the solutions I tried earlier. Please let me know if there are any errors in my query.

set serveroutput on;

DECLARE
    c_id    t_person.id%TYPE;
    c_value t_person_communication_method.value%TYPE;
    c_comm_id t_person_communication_method.id%TYPE;
    CURSOR cr IS
 SELECT
        fppc.id as id, fppc.value as value, fppc.person_id as person_id
    FROM
        (
            SELECT
                ppc.*,
                ROW_NUMBER()
                OVER(PARTITION BY ppc.value
                     ORDER BY
                         ppc.value
                ) rn
            FROM
                (
                    SELECT
                        cm.id,
                        cm.person_id,
                        p.first_name,
                        p.last_name,
                        p.business_flow_id,
                        cm.type,
                        cm.value,
                        cm.is_verified,
                        cm.created_date,
                        cm.created_channel
                    FROM
                             t_person p
                        JOIN t_person_communication_method cm ON p.id = cm.person_id
                    WHERE
                           cm.type = 'EMAIL'
                        AND cm.is_verified = 'Y'
                    ORDER BY
                        p.id ASC
                ) ppc
        ) fppc
    WHERE
        fppc.rn > 1;

BEGIN
    OPEN cr;
    LOOP
       update t_person_login set username ='anonymous@gmail.com' where person_id =c_id;
        update t_person_login_history set username ='anonymous@gmail.com' where person_id =c_id;
        update t_person set first_name ='anonymous',last_name ='anonymous' where id = c_id;
        update t_person_communication_method set value ='anonymous@gmail.com' where type = 'EMAIL' and person_id =c_id;
        update t_person_communication_method set value ='00000000000' where type = 'PHONE' and person_id =c_id and id=c_comm_id;
        update t_person_comm_method_history set value = 'anonymous@gmail.com' where type ='EMAIL' and person_id =c_id;
        update t_person_address set postcode ='anonymous',address_1 ='anonymous',city='anonymous',county ='anonymous' where person_id =c_id;        
        EXIT WHEN cr%notfound;
        dbms_output.put_line(c_id || ' ' || c_value || '' || c_comm_id);
    END LOOP;

    CLOSE cr;
END;

The solutions I tried. I replaced below query line for set serveroutput on; line. Other parts of the query are always the same.

1

set serveroutput on size unlimited

2

dbms_output.enable(NULL)

3

SET SERVEROUTPUT ON size '10000000'
and
DBMS_OUTPUT.ENABLE(10000000);

4

SET SERVEROUTPUT ON size 10000000
and
DBMS_OUTPUT.ENABLE(10000000);

Error I got

Error starting at line : 3 in command -
DECLARE
    c_id    t_person.id%TYPE;
    c_value t_person_communication_method.value%TYPE;
    c_comm_id t_person_communication_method.id%TYPE;
    CURSOR cr IS
 SELECT
        fppc.id as id, fppc.value as value, fppc.person_id as person_id
    FROM
        (
            SELECT
                ppc.*,
                ROW_NUMBER()
                OVER(PARTITION BY ppc.value
                     ORDER BY
                         ppc.value
                ) rn
            FROM
                (
                    SELECT
                        cm.id,
                        cm.person_id,
                        p.first_name,
                        p.last_name,
                        p.business_flow_id,
                        cm.type,
                        cm.value,
                        cm.is_verified,
                        cm.created_date,
                        cm.created_channel
                    FROM
                             t_person p
                        JOIN t_person_communication_method cm ON p.id = cm.person_id
                    WHERE
                           cm.type = 'EMAIL'
                        AND cm.is_verified = 'Y'
                    ORDER BY
                        p.id ASC
                ) ppc
        ) fppc
    WHERE
        fppc.rn > 1;

BEGIN
    OPEN cr;
    LOOP
       update t_person_login set username ='anonymous@gmail.com' where person_id =c_id;
        update t_person_login_history set username ='anonymous@gmail.com' where person_id =c_id;
        update t_person set first_name ='anonymous',last_name ='anonymous' where id = c_id;
        update t_person_communication_method set value ='anonymous@gmail.com' where type = 'EMAIL' and person_id =c_id;
        update t_person_communication_method set value ='00000000000' where type = 'PHONE' and person_id =c_id and id=c_comm_id;
        update t_person_comm_method_history set value = 'anonymous@gmail.com' where type ='EMAIL' and person_id =c_id;
        update t_person_address set postcode ='anonymous',address_1 ='anonymous',city='anonymous',county ='anonymous' where person_id =c_id;        
        EXIT WHEN cr%notfound;
        dbms_output.put_line(c_id || ' ' || c_value || '' || c_comm_id);
    END LOOP;

    CLOSE cr;
END;
Error report -
ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 97
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at line 54
20000. 00000 -  "%s"
*Cause:    The stored procedure 'raise_application_error'
           was called which causes this error to be generated.
*Action:   Correct the problem as described in the error message or contact
           the application administrator or DBA for more information.

Hope your help. Thank you.

Answer 1

As @David pointed out, you don't need necessarily need to do this with a cursor loop or with PL/SQL at all; but at least part of the problem with your code is that you are looping forever.

Your loop is essentially:

OPEN cr;
LOOP
    -- do stuff
    EXIT WHEN cr%notfound;
END LOOP;
CLOSE cr;

You are not ever fetching data from the cursor. As it says in the documentation:

named_cursor%NOTFOUND has one of these values:

• If cursor is not open, INVALID_CURSOR.
• If cursor is open but no fetch was tried, NULL.
• If the most recent fetch returned a row, FALSE.
• If the most recent fetch did not return a row, TRUE.

You have opened the cursor, but haven't fetched, so the second bullet applies and cr%notfound is null - which is not true (or false), so the exit doesn't happen.

So you loop forever; or in your actual code, until you hit the buffer limit.

You need to add a fetch, and should also test for %notfound immediately rather than after your updates, otherwise the last found values will be applied twice (which might not really matter here, but usually will).

OPEN cr;
LOOP
    FETCH cr INTO c_id,c_value, c_comm_id;
    EXIT WHEN cr%notfound;
    -- do stuff
END LOOP;

So your code becomes:

BEGIN
    OPEN cr;
    LOOP
        FETCH cr INTO c_id,c_value, c_comm_id;
        EXIT WHEN cr%notfound;
        dbms_output.put_line(c_id || ' ' || c_value || '' || c_comm_id);

        update t_person_login set username ='anonymous@gmail.com' where person_id =c_id;
        update t_person_login_history set username ='anonymous@gmail.com' where person_id =c_id;
        update t_person set first_name ='anonymous',last_name ='anonymous' where id = c_id;
        update t_person_communication_method set value ='anonymous@gmail.com' where type = 'EMAIL' and person_id =c_id;
        update t_person_communication_method set value ='00000000000' where type = 'PHONE' and person_id =c_id and id=c_comm_id;
        update t_person_comm_method_history set value = 'anonymous@gmail.com' where type ='EMAIL' and person_id =c_id;
        update t_person_address set postcode ='anonymous',address_1 ='anonymous',city='anonymous',county ='anonymous' where person_id =c_id;        
    END LOOP;

    CLOSE cr;
END;