SQL & PL/SQL

Hi Guys,

I have this procedure that gets data from the database and spool it into a text file (pipe delimeted). My problem is when I run the script it gives me a ORA-20000: ORU-10027: buffer overflow, limit of 20000 bytes ERROR. Any idea what this means? or should I write this differently (not use dbms_output??).

Thank you and I appreciate all your help. FYI, I have 35 columns in my query and I was expecting 12,000 rows from my output.

CREATE OR REPLACE PROCEDURE test(p_term_code_in IN stvterm.stvterm_code%TYPE)
AS
        CURSOR c_students( p_term_code_in  IN stvterm.stvterm_code%TYPE) 
        IS
        SELECT  id AS student_id,
                first_name AS student_first_name,
                last_name AS student_last_name 
        FROM    students

        v_StudentsData  c_students%ROWTYPE;
        
BEGIN

    DBMS_OUTPUT.PUT_LINE('student_id|student_first_name|spriden_mi|student_last_name|');

    OPEN c_students(p_term_code_in);
    
    FETCH c_students INTO v_StudentsData;
    
    WHILE c_students%FOUND LOOP
        dbms_output.put_line(v_StudentsData.student_id||'|'||
     			     v_StudentsData.student_first_name||'|'||
     			     v_StudentsData.student_last_name||'|');
    
    FETCH c_students INTO v_StudentsData;
    END LOOP;
    CLOSE c_students;
    
        DBMS_OUTPUT.PUT_LINE('|');

END;
/


accept term prompt 'enter term code: '
set serveroutput on
set newpage 0
set linesize 750
set termout off
set feedback off
set echo off
set pagesize 0
spool test.txt
exec test(&term);
spool off;
exit;

Edited by: user5737516 on May 21, 2012 12:46 PM

I added DBMS_OUTPUT.ENABLE(2000000) to my procedure and it looks like that fix the buffer issue. If you have suggestion on how to do this otherwise feel free to comment. Thanks!!!