debugging using dbms_output.put_line

Hi ora User,
you can use
DBMS_OUTPUT.ENABLE (
  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.

Thanks
Mohit

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);
  begin
    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;
  end;

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...

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

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:-)

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:
https://www.experts-exchange.com/help.jsp#hs5

patelgokul
EE Cleanup Volunteer