Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2008
    Posts
    99

    Unanswered: buffer overflow, limit of 10000 bytes problem

    Hello,
    I m having this problem while I m running a procedure. I have one procudure which calls anohter package,these two creates an excell output.

    I googled and I found that I should use dbms_output_enable(); I tried this
    dbms_output_enable(20000); in the procedure code.I got an emptry excel,If I run this procedure against a small table it works.

    .ORA-20000: ORU-10027: buffer overflow, limit of 10000 bytes

    anyone has any idea about this

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking UTL_FILE is better.

    1) The buffer limit is 1,000,000 "DBMS_OUTPUT.ENABLE(1000000);"
    2) You could use "DBMS_OUTPUT.DISABLE;" followed by "DBMS_OUTPUT.ENABLE(1000000); " to periodically empty the buffer.
    3) BEST to use the UTL_FILE utility to write your data directly to disk.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Dec 2008
    Posts
    99
    Quote Originally Posted by LKBrwn_DBA
    1) The buffer limit is 1,000,000 "DBMS_OUTPUT.ENABLE(1000000);"
    2) You could use "DBMS_OUTPUT.DISABLE;" followed by "DBMS_OUTPUT.ENABLE(1000000); " to periodically empty the buffer.
    3) BEST to use the UTL_FILE utility to write your data directly to disk.
    Hello,
    If I set buffer to "DBMS_OUTPUT.ENABLE(1000000);" in the procedure code,it's gonna be ok ?

    I donot get 2nd option,how can I use it ? I donot want to go further to use utl_package.

    Kind Regards

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Count the rows

    For the second option, just make an estimate of how many output rows would fill 1,000,000 bytes, count the rows and every time you reach that number issue the disable/enable commands.

    Something like this:
    Code:
        -- Etc...
        lines_out := lines_out + 1;
        if MOD(line_out, 3000) = 0 Then
            DBMS_OUTPUT.DISABLE;
            DBMS_OUTPUT.ENABLE(1000000); 
        end if;
        -- Etc...
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Dec 2008
    Posts
    99
    Quote Originally Posted by LKBrwn_DBA
    For the second option, just make an estimate of how many output rows would fill 1,000,000 bytes, count the rows and every time you reach that number issue the disable/enable commands.

    Something like this:
    Code:
        -- Etc...
        lines_out := lines_out + 1;
        if MOD(line_out, 3000) = 0 Then
            DBMS_OUTPUT.DISABLE;
            DBMS_OUTPUT.ENABLE(1000000); 
        end if;
        -- Etc...
    Hello,
    I tried first method but got this
    .ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
    do I miss anything? I donot have millions of rows from this query? It has 2-3 thousand rows

    Thanks

  6. #6
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Thumbs down 1,000,000 Characters !!!!!!!!!!

    You have to estimate the number of rows that would ADD UP to 1,000,000 characters or less and set your limit to that.

    If 3000 failed try 2000 or 1000 or 500 0r ...whatever...
    .
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  7. #7
    Join Date
    Dec 2008
    Posts
    99
    Quote Originally Posted by LKBrwn_DBA
    You have to estimate the number of rows that would ADD UP to 1,000,000 characters or less and set your limit to that.

    If 3000 failed try 2000 or 1000 or 500 0r ...whatever...
    .

    Hi
    the number of rows is around 1200.

    I run commands in sqlplus in this order
    First
    set serveroutput on size 1000000;
    execute myproc;

    but I m still getting this error.

    What do I miss?

    Thanks

  8. #8
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Angry Stilll more than 1,000,000 characters

    Even if you are only processing 1200 rows, the SUM of all output exceeds the 1,000,000 character limit. Try reducing the number in the rows limit:
    Code:
    -- Etc...
        lines_out := lines_out + 1;
        if MOD(line_out, 100) = 0 Then
            DBMS_OUTPUT.DISABLE;
            DBMS_OUTPUT.ENABLE(1000000); 
        end if;
        -- Etc...
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  9. #9
    Join Date
    Dec 2008
    Posts
    99
    Quote Originally Posted by LKBrwn_DBA
    Even if you are only processing 1200 rows, the SUM of all output exceeds the 1,000,000 character limit. Try reducing the number in the rows limit:
    Code:
    -- Etc...
        lines_out := lines_out + 1;
        if MOD(line_out, 100) = 0 Then
            DBMS_OUTPUT.DISABLE;
            DBMS_OUTPUT.ENABLE(1000000); 
        end if;
        -- Etc...
    Hi,
    I donot know why but when I run same package as a schedules procedure.it runs without problem.If I run this package sqlplus/toad/oracle sqldeveloper manually I get this error
    Kind Regards

  10. #10
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Question Activate?

    In TOAD and SQL Developer you have to click somewhere to "activate" (and set up) the buffer.

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  11. #11
    Join Date
    Dec 2008
    Posts
    99
    Quote Originally Posted by LKBrwn_DBA
    In TOAD and SQL Developer you have to click somewhere to "activate" (and set up) the buffer.

    Hi,
    Do I understand right? When I use toad query tries to run on my computer and cannot find enough memory for buffer that's why it gives this error

    If I run this query on server,just by letting oracle scheduler run it on its time,it runs because it has enough memory for buffer

    Kind Regards

  12. #12
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool Not the same!

    TOAD and SQL Developer seems do not recognize the buffer settings embedded in the script, you have to explicitly "click" on the corresponding button and set it.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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