DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2001
    I am trying to dump the content of a view into a text file. I am following the steps below

    1. select the colmn to cursor
    2. Fetch cursors into variables
    3. concatinate all variables into a single string
    4. Write to string using UTL_FILE package.

    I have the problem in step 3, where I am trying to concatinate all the cursor values to a single varchar variable.
    I get the following error
    ERROR at line 1:
    ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes
    ORA-06512: at "SYS.DBMS_OUTPUT", line 106
    ORA-06512: at "SYS.DBMS_OUTPUT", line 65
    ORA-06512: at "MNDB.RESPONSE_FILE", line 310
    ORA-06512: at line 1

    Lenght of all the columns in the view will not exceed 1200 characters
    Please help me;

  2. #2
    Join Date
    Nov 2000
    Looks like you are using dbms_output for something. By default, you can only spool 2000 bytes using dbms_output. You can increase it by using the dbms_output.enable method:
    Jeff Hunter
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jan 2001



    Ok, here is what I know:

    DBMS_OUTPUT has a buffer, which default size is 20000. Your buffer size is set as 2000, the minimum possible.

    Here a note from the DBMS_OUTPUT header in the Oracle documentation:

    "Messages sent using DBMS_OUTPUT are not actually sent until the sending subprogram or trigger completes. There is no mechanism to flush output during the execution of a procedure. "

    That mean that your buffer don't get clear until the end of your PL-SQL block, so the problem is not the lenght of one of your line, but the sheer size of all the line of your view together.

    But there is a way to solve the problem: change the size of the buffer. You could set a size of maximum 1,000,000 using the following:


    It solve my problem not long ago: hope it help you as well!


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

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.