buffer overflow, limit of 1000000 bytes in oracle database-CodePudding

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 ='[email protected]' where person_id =c_id;
        update t_person_login_history set username ='[email protected]' 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 ='[email protected]' 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 = '[email protected]' 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.

set serveroutput on size unlimited

dbms_output.enable(NULL)

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

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 ='[email protected]' where person_id =c_id;
        update t_person_login_history set username ='[email protected]' 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 ='[email protected]' 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 = '[email protected]' 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.

CodePudding user response：

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 ='[email protected]' where person_id =c_id;
        update t_person_login_history set username ='[email protected]' 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 ='[email protected]' 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 = '[email protected]' 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;