debugging using dbms_output.put_line

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 ?
ora_userAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SANALCommented:
i don't remember of any.
but u can increase the buffer size

buy calling the enable proc.

DBMS_OUTPUT.ENABLE (
   buffer_size IN INTEGER DEFAULT 20000);

~Sanal
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
saxena_mohitCommented:
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
0
gulbrainCommented:
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...
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

konektorCommented:
in sql+ u can :
set serveroutput on size <number of bytes>
0
csarCommented:
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:-)
0
patelgokulCommented:
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:
http://www.experts-exchange.com/help.jsp#hs5

patelgokul
EE Cleanup Volunteer
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.