debugging using dbms_output.put_line

Posted on 2003-02-28
Medium Priority
Last Modified: 2008-02-26
After sometime the output messages ceased to come on the screen.Is it because of a possible filling of buffer.If so is there a way to flush it ?
Question by:ora_user
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

SANAL earned 200 total points
ID: 8045187
i don't remember of any.
but u can increase the buffer size

buy calling the enable proc.

   buffer_size IN INTEGER DEFAULT 20000);


Expert Comment

ID: 8045266
Hi ora User,
you can use
  buffer_size IN INTEGER DEFAULT 20000); to increase the buffer and this will buy you some more time to get this error because the  maximum size of the buffer is 1,000,000 .

If you have too many debugging statement so you will get error once you exceed the 1000000 Limit.

so the best practice is to call
 DBMS_OUTPUT.DISABLE   in between. This will purge your all old buffer.


Expert Comment

ID: 8047867
If you have lots of debugging output to produce then you can use a table with a CLOB column ...

Here's a cut down version of a trace procedure I found very useful.

create table TraceTable (clob_col clob);

  Procedure MyTrace( instring varchar2, new_clob boolean := false ) is
    TraceClob clob;
    vc varchar2(32700);
    vc := sqlerrm;
      select clob_col into TraceClob from TraceTable
      for update of clob_col;
      if New_Clob then
        dbms_lob.trim(TraceClob, 0);
      end if;
        dbms_lob.writeappend( TraceClob, 15, to_char(sysdate, 'DD/MM HH24:MI:SS '));
      end if;
      dbms_lob.writeappend( TraceClob, length(instring), instring );
      dbms_lob.writeappend( TraceClob, 1, chr(10) );
      if vc not like 'ORA-0000:%' then -- ORA-0000: normal, successful completion
        dbms_lob.writeappend( TraceClob, 41, lpad(chr(10), 41, '-'));
        dbms_lob.writeappend( TraceClob, length(vc), vc);
        dbms_lob.writeappend( TraceClob, 1, chr(10) );
        dbms_lob.writeappend( TraceClob, 41, lpad(chr(10), 41, '-'));
      end if;
    end if;

In SQL*Plus you can select this clob_col value after each commit in the application or after the application call in the same db session/transaction.

Remember to start your application with a MyTrace('go', new_clob=>true); call to clear out the previous run.

You may need to specify lob storage parameters.

Hope that's useful...
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Expert Comment

ID: 8055936
in sql+ u can :
set serveroutput on size <number of bytes>

Expert Comment

ID: 8063309
Since the serveroutput only appears upon completition of the command you can cannot spool more than 1M.

We use a replacement with a tracetab, too. The only difference ist that our procedure uses
  pragma autonomous_transaction
and commits its changes after writing a trace entry. This is very useful, if your program issues a rollback upon an execption you want to analyze.

I think you can use table on a non-logged TS to accomplish the same effect, since there won't be no rollback for this table. (Please someone drop me a note if a told nonsense here:-)

Expert Comment

ID: 9933137
This question has been classified as abandoned.  I will make a recommendation to the moderators on its resolution in approximately one week.  I would appreciate any comments by the experts that would help me in making a recommendation.

It is assumed that any participant not responding to this request is no longer interested in its final deposition.

If the asker does not know how to close the question, the options are here:

EE Cleanup Volunteer

Featured Post

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many‚Ķ
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question