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