Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

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...
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.


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

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

580 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