PL/SQL buffer overflow

I am using DBMS_OUTPUT.PUT_LINE to debug some messages from my PL/SQL block. Of late, I have been getting the ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes errors when users try to execute this package. I read that every time the package finishes execution, the data from buffer is written to the screen and then flushed. I have confirmed that with a single execution, this proc does not write that many bytes to the output. What else could be the cause ? Can I manually free up the buffer contents ?

Thanks,
Shiva.

What's your Oracle version?

Ora10gR2?
If yes, try: dbms_output.enable(null);

Oracle version is 9.2.0.7.

That's bad news, you are stuck with 1,000,000 bytes max.

Since your error message already shows "limite of 1000000 bytes" I think is safe to assume you have already set the max value.

Remember your job is writting to DBMS_OUTPUT as it runs but what it writes will only be displayed when the job ends.

On top of it, since every single session has its own buffer it doesn't matter how many users are executing your procedure, that been said, my wild guess, your job is actually writing more than 1 million bytes.

Agreed that every single user gets his own buffer, is it true that the buffer gets flushed after every single PL/SQL execution that uses it or is it used cumulatively ?

Did you write a logging package? Does that package allow you to write to file with utl_file? You should think about having a logging package.

In the mean time send less output to dbms_output.

As far as I remember DBMS_OUTPUT buffer gets flushed when the host procedure ends meaning, if the procedure never ends your buffer never gets flushed.

I also remember an Oracle note stating that there is no way to flush the content of the buffer "during" the execution of the host procedure.

Maybe your procedure calls other procedures, which also uses dbms_output.
Other issue could be if you call dbms_output in a body of a loop. Then the amount of the generated output would depend on the number of iterations.
How to resolve that? - Agree with PANDO
1) Reduce the amount of output data - You do not need for example to output "Iteration 1 passed" "It 1" would bring the same info to you
2) You may use autonomous transaction procedure to log the data into a table instead of using DBMS_OUTPUT. That would be slower, so you should think of mechanism to suppress that in prod.
3) You may create your own package, where you define the size of the buffer. Then you can use dbms_output to dump the buffer on the screen, or try to use table function to cast the buffer to table and then select from it or....

Cheers

Here is the dbms_output contents by executing that pacakge in a different environment with same prod data.

Code:

---

SQL> set serveroutput on size 1000000;
SQL> exec pkg_get_exam.get_all_exams_by_user_id(2528666, 'C', 3, :c);
UserId= 2528666                                                               
in_student_type= C                                                             
v_Assessment_Locator = 8D9A40AF-728A-86DC-9BE4-B7C68FF6AD66-1::AS             
v_Assessment_Locator = 8DA04C61-728A-86DC-9BE4-B7C6AC2CAE1B-1::AS             
v_Assessment_Locator = 8D9BA966-728A-86DC-9BE4-B7C68D109065-1::AS             
v_Assessment_Locator = 8DA2310F-728A-86DC-9BE4-B7C665C6D818-1::AS             
v_Assessment_Locator = 8D9E08AD-728A-86DC-9BE4-B7C6E64CA4D7-1::AS             
v_Assessment_Locator = 8DA2A9A0-728A-86DC-9BE4-B7C6F0776018-1::AS             
v_Assessment_Locator = 9878E785-728A-86DC-9BE4-B7C6BE952F9D-1::AS             
v_Assessment_Locator = 9879786A-728A-86DC-9BE4-B7C66FF45CE2-1::AS             

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

---

As you can see, it is writing very minimal to the buffer and it does not call any other package and it is not inside a LOOP. How could this output have exceeded 1MB ? I am seeing this error in 1 enrionment, not in the other. They both are 9.2.0.7.

Thanks.

Here is my package body.

Code:

---

CREATE OR REPLACE PACKAGE BODY "PKG_GET_EXAM" IS
PROCEDURE get_all_exams_by_user_id(in_user_id IN NUMBER,
                                    in_student_type IN VARCHAR2,
                                                                        in_assessment_type IN NUMBER,
                                                out_ref_cur OUT generic_ref_cur) IS

i_count NUMBER := 0;

t_all_details type_table_allexams := type_table_allexams();

i_idx NUMBER := 1;

--Ref Cursor to get the list of exams
TYPE refcur_all_exams IS REF CURSOR;
cur_all_exams refcur_all_exams;
v_Assessment_Locator CLG_ASSESSMENT.LOCATOR%TYPE; --Assessment Locator
v_Assessment_Title CLG_ASSESSMENT.TITLE%TYPE; --Assessment Title
n_User_Status CLG_ASSESSMENT_RESULT.STATUS%TYPE; --Status of the Assessment, if taken by user earlier
n_Times_To_Take NUMBER; --Number of times the user can take the assessment
n_Assessment_Result_Id CLG_ASSESSMENT_RESULT.ASSESSMENT_RESULT_ID%TYPE;  --Id of the latest attempt on the asessment by the user

n_Assessment_Status CLG_ASSESSMENT_RESULT.STATUS%TYPE; --Status of the latest attempt on the assessment
n_Temp_Status NUMBER; --Temporary Status
n_Remaining_time CLG_SECTION_RESULT.TIME_SPENT%TYPE; --Total time remaining to complete all the sections
n_Total_Sections CLG_SECTION_RESULT.DEFAULT_SEQUENCE%TYPE; --Total sections in the assessment
n_Current_Section CLG_SECTION_RESULT.DEFAULT_SEQUENCE%TYPE; --Current section in the assessment that the user is working on

v_Section_Locator CLG_SECTION_RESULT.SECTION_LOCATOR%TYPE; --Locator of the current section
n_taken_online CLG_ASSESSMENT_RESULT.WAS_TAKEN_ONLINE%TYPE; --Was the assessment taken online ?
n_essay_scoring_cd CLG_ASSESSMENT_RESULT.essay_scoring_cd%TYPE; --who scored the Assessment essay?
n_instance_essay_scoring_cd CLG_ASSESSMENT_RESULT.essay_scoring_cd%TYPE; --who scored the Assessment essay?
n_is_essay_updatable CLG_ASSESSMENT_RESULT.IS_ESSAY_UPDATABLE%TYPE; --flag if assessment was entered as free student and then imported
v_document_id CLG_ASSESSMENT.DOCUMENT_ID%TYPE; --DocumentId of the assesment - is required for the PRINT option

BEGIN

 dbms_output.put_line ('UserId= ' || in_user_id);
 dbms_output.put_line ('in_student_type= ' || in_student_type);

 IF ( in_student_type = 'C' ) OR ( in_student_type = 'S' ) THEN
 BEGIN
  -- Are there any exams that he has already taken ?
  i_count := 0;
  SELECT COUNT(*)
  INTO i_count
  FROM CLG_ASSESSMENT_RESULT inner_ar, CLG_ASSESSMENT inner_ca
  WHERE inner_ar.assessment_locator = inner_ca.locator
  AND inner_ca.assessment_type_cd = in_assessment_type
  AND inner_ar.taker_id = in_user_id;
  IF (i_count > 0) THEN
  OPEN cur_all_exams FOR
  SELECT ca.locator,
                  ca.title AS title,
                  NULL,
                  -1,
                  inner_query.result_id,
                  ar.status,
                  ar.was_taken_online,
                  ar.essay_scoring_cd essay_scoring_cd,
                  ar.essay_scoring_cd "INSTANCE_ESSAY_SCORING_CD",
                                  ar.is_essay_updatable,
                                  ca.document_id
  FROM  CLG_ASSESSMENT ca,
                  CLG_ASSESSMENT_RESULT ar,
                  (SELECT result_id, assessment_locator
                  FROM(  SELECT  inner_ar.assessment_result_id AS result_id, inner_ar.assessment_locator AS assessment_locator,
                                                              row_number() over (PARTITION BY  inner_ar.taker_id,  inner_ar.assessment_locator
                                                                                                                                                  ORDER BY inner_ar.started_date DESC) rn
                                            FROM CLG_ASSESSMENT_RESULT inner_ar, CLG_ASSESSMENT inner_ca
                                                            WHERE  inner_ar.taker_id =  in_user_id
                                                                                                              AND inner_ar.assessment_locator = inner_ca.locator
                                                                                            AND inner_ca.assessment_type_cd = in_assessment_type
                                                        )
                                WHERE rn  = 1
                                ) inner_query
  WHERE ca.locator = inner_query.assessment_locator
                    AND inner_query.result_id = ar.assessment_result_id
                    AND ar.taker_id =  in_user_id
                    AND ca.assessment_type_cd =  in_assessment_type
  UNION
  SELECT ca.locator,
                  ca.title AS title,
                  NULL ass_user_status,
                  -1,
                  NULL,
                  NULL,
                  NULL,
                  NULL,
                  NULL,
                                  NULL,
                                  ca.document_id
  FROM  CLG_ASSESSMENT ca
  WHERE ca.assessment_type_cd = in_assessment_type
  AND NOT EXISTS
  (SELECT *
    FROM CLG_ASSESSMENT_RESULT
        WHERE taker_id = in_user_id
                              AND assessment_locator = ca.locator)
        ORDER BY title;

  ELSE
  OPEN cur_all_exams FOR
    SELECT ca.locator,
                  ca.title title,
                  NULL ass_user_status,
                  -1,
                  NULL,
                  NULL,
                  NULL,
                  NULL,
                  NULL,
                                  NULL,
                                  ca.document_id
  FROM  CLG_ASSESSMENT ca
  WHERE ca.assessment_type_cd = in_assessment_type
        ORDER BY title;

  END IF;

END;
END IF;

 LOOP
    FETCH cur_all_exams
        INTO v_Assessment_Locator,
              v_Assessment_Title,
              n_User_Status,
                  n_Times_To_Take,
                  n_Assessment_Result_Id,
                  n_Assessment_Status,
                  n_taken_online,
                  n_essay_scoring_cd,
                  n_instance_essay_scoring_cd,
                                  n_is_essay_updatable,
                                  v_document_id;
        EXIT WHEN cur_all_exams%NOTFOUND;

        n_Temp_Status := NULL;
        n_Current_Section := NULL;
        v_Section_Locator := NULL;
    dbms_output.put_line ('v_Assessment_Locator = ' || v_Assessment_Locator);
    n_Current_Section := 1;

  IF n_Assessment_Result_Id IS NOT NULL THEN
    IF n_Assessment_Status <> 20 THEN
          SELECT MAX(status)
                  , MAX(default_sequence)
          INTO n_Temp_Status , n_Total_Sections
          FROM CLG_SECTION_RESULT
          WHERE assessment_result_id = n_Assessment_Result_Id;

          SELECT SUM(duration) - SUM(NVL(time_spent,0))
          INTO  n_Remaining_Time
          FROM CLG_SECTION_RESULT
          WHERE assessment_result_id = n_Assessment_Result_Id
                                  AND status<15;

        IF n_Temp_Status = 20 THEN
                  SELECT MAX(default_sequence) INTO n_Current_Section
                FROM CLG_SECTION_RESULT
                WHERE assessment_result_id = n_Assessment_Result_Id
                AND status = 20;

              ELSIF n_Temp_Status = 15 THEN
        SELECT MIN(default_sequence) INTO n_Current_Section
                FROM CLG_SECTION_RESULT
                WHERE assessment_result_id = n_Assessment_Result_Id;
          ELSE
            n_Current_Section := 0 ;
      END IF;

        dbms_output.put_line ('v_Assessment_Title = ' || v_Assessment_Title);

    END IF;
        IF n_Current_Section > 0 THEN
      SELECT section_locator
      INTO v_Section_Locator
      FROM CLG_SECTION_RESULT
      WHERE assessment_result_id = n_Assessment_Result_Id
      AND default_sequence = n_Current_Section;
        ELSE
            v_Section_Locator := NULL;
    END IF;
  ELSE
    SELECT section_locator
        INTO v_Section_Locator
        FROM CLG_ASSESSMENT_SECTION_MAP
        WHERE assessment_locator = v_Assessment_Locator
        AND default_sequence = 1;
  END IF;



  t_all_details.extend;
  t_all_details(i_idx) := type_object_allexams(v_Assessment_Locator,
                                                                                                  v_Assessment_Title,
                                                                                                  n_User_Status,
                                                                                                  n_Times_To_Take,
                                                                                                  n_Assessment_Result_Id,
                                                                                                  n_Assessment_Status,
                                                                                                  v_Section_Locator,
                                                                                                  n_Remaining_Time,
                                                                                                  n_Total_Sections,
                                                                                                  n_Current_Section,
                                                                                                  n_taken_online,
                                                                                                  n_essay_scoring_cd,
                                                                                                  n_instance_essay_scoring_cd,
                                                                                                                                                                                                  n_is_essay_updatable,
                                                                                                                                                                                                  v_document_id);

  i_idx := i_idx + 1;



  END LOOP;
  CLOSE cur_all_exams;



  OPEN out_ref_cur FOR
  SELECT *
  FROM TABLE(CAST(t_all_details AS type_table_allexams));

  EXCEPTION
    WHEN NO_DATA_FOUND THEN
        NULL;
        WHEN OTHERS THEN
                RAISE;

 END get_all_exams_by_user_id;

END Pkg_Get_Exam;

---

And another interesting point is that this package has existed for about 2 years now and only as of last month we are seeing these buffer overflow errors. Nothing significant was changed in the database config in the past 2 months.

Thanks.

its your data, you explain it - but you are going over the limit

I did not see dbms_output.enable(1000000); in your package. Try adding that right after the begin line.