DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: dbms_output.put_line problem

  1. #1
    Join Date
    Aug 2003
    Posts
    54

    dbms_output.put_line problem

    on one of the application, they have dbms_output.put_line and it launch with application, every ten days their services crashed and I think what happened was dbms_output.put_line was writing to the buffer. my question is does Oracle have package to clean up the buffer per session????

  2. #2
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    You can empty the pipe using DBMS_OUTPUT.get_line or DBMS_OUTPUT.get_lines. This is how SQL*Plus and other Oracle tools retrieve the server output.

    A better approach is to wrap the output lines in your own procedure that can be switched off. That way you only put things on the pipe if you are using them. I use the following:

    http://www.oracle-base.com/DBA/Miscellaneous/dsp.pks
    http://www.oracle-base.com/DBA/Miscellaneous/dsp.pkb

    It allows you to switch off output, output to files, output different datatypes and wrap output that is longer than 255 etc. Several people have similar code, but this is my take on it.

    You should be able to replace your DBMS_OUTPUT calls with a simple search and replace then you're sorted.

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Tim,

    I see you open & close the file for each line. Wouldn't using utl_file.fflush be more efficient? (Assuming heavy use.) Of course you have to handle the open and close differently.

    BTW, this is an excellent technique for debugging - with a flag to turn it on/off, you can leave it in the production system for those occasions when the test data works OK, but production stuff falls flat on its face.

  4. #4
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684
    Hi.

    I agree that the FOPEN and FCLOSE for each action is an overhead but I've found that more reliable than worrying about the state of the file between runs. In addition the close makes it possible for multiple sessions to write to the same file with fewer clashes.

    The loss of speed doesn't bother me too much because it wouldn't "normally" be running in production.

    It's proved very usefull for me. I'm using it as we speak to debug some dataloads I'm doing.

    Cheers
    Tim...
    OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
    OCA PL/SQL Developer
    Oracle ACE Director
    My website: oracle-base.com
    My blog: oracle-base.com/blog

  5. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by TimHall
    In addition the close makes it possible for multiple sessions to write to the same file with fewer clashes.
    I generate the file name from the AUDSID to avoid this - depends what you want to do.
    Originally posted by TimHall
    The loss of speed doesn't bother me too much because it wouldn't "normally" be running in production.
    Candyman doesn't say, but I got the impression that there was a lot of i/o.

    This oracle-base site has some good stuff on it. Any idea who runs it?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width