-
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);
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.
-
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.
Last edited by PAVB; 08-28-2007 at 04: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.
-
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.
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.
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|