PL/SQL buffer overflow
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: PL/SQL buffer overflow

  1. #1
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343

    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.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    What's your Oracle version?

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

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    Oracle version is 9.2.0.7.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.
    Last edited by PAVB; 08-28-2007 at 05:36 PM.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    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 ?

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    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.
    this space intentionally left blank

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    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

  9. #9
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    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.

  10. #10
    Join Date
    Sep 2000
    Location
    VA
    Posts
    343
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width