10 Replies Latest reply: Jul 19, 2012 10:01 AM by pnosko RSS

    utl_file simultaneous usage

    user605483
      i will use a stored procedure for db-logging in a file using utl_file (fopen/put_line/fflush/fclose)

      - may i have problems with simultaneous calls to this procedure within different sessions or i must use some kind of locking ?
        • 1. Re: utl_file simultaneous usage
          TheOtherGuy
          why don't you test it out? create small SP which inserts small amount of data to a file and start it several times in loop, I have not seen locking issues on solaris

          if you have high volume logging app, I would probably not write to a file from multiple sessions, if you must write to a file immediately after the log record is created, then why don't you write to a table and create trigger on that table which writes to a file or create audit on that table and let oracle take care of the logging (set audit trail to xml extended)
          • 2. Re: utl_file simultaneous usage
            damorgan
            Will multiple users read the same file or different files?
            Does your database have a version number?
            Have you tried, as suggested, testing?
            • 3. Re: utl_file simultaneous usage
              user605483
              normally i do logging in a log table. i have to maintenain a piece of code (not my code), which does exactly this file-logging (db is 10g)


              i tried it out with a small stored procedure (fopen/(with and without dbms_lock.sleep(3))/put_line/fflush/fclose) executing it on two different sessions (sys/scott)
              • 4. Re: utl_file simultaneous usage
                damorgan
                I'm not sure you answered the questions I asked but I am quite sure I think, from a design standpoint, it is a disaster waiting to happen. But then I don't like the idea of serializing in a multiuser/multiconsumer application. Why not write to a table?
                • 5. Re: utl_file simultaneous usage
                  Billy~Verreynne
                  user605483 wrote:
                  i will use a stored procedure for db-logging in a file using utl_file (fopen/put_line/fflush/fclose)

                  - may i have problems with simultaneous calls to this procedure within different sessions or i must use some kind of locking ?
                  Yes - but if you use text and "+write append+" to the file, your code is fairly thread safe.

                  The problem is with the file pointer in session A versus session B. Session B writes to the file. This does not advance the file pointer in session A. Thus when session A writes to the file, it overwrites the data just written by session B.

                  A write append automatically writes the data at the end of the file (the file pointer is moved to eof and the write happens). Write append is only valid for text files - not binary files (though you can likely simulate it, but it would not make much sense).

                  As writes to the same file is serialised (due to the file being opened in shared mode), there's no problem in both sessions doing a write append at the same time. (except for the performance issue of serialisation)

                  But the concept of writing log files from an Oracle RDBMS - that is just plain horrible. This is not the 80's anymore (despite us missing the music and hairstyles) and Oracle is not Cobol writing to ISAM files.

                  The correct place for logs is inside a plain Oracle SQL table - that can be indexed, partitioned. That can be read and filtered using SQL. That implements concurrency controls. That has Oracle security features. Forms part of the database backup strategy. Etc.

                  IMO there is no valid reason for storing PL/SQL processing logs externally. (a debate I've often had in the past with developers and one I have yet to lose)
                  • 6. Re: utl_file simultaneous usage
                    TheOtherGuy
                    user1148640 wrote:
                    IMO there is no valid reason for storing PL/SQL processing logs externally. (a debate I've often had in the past with developers and one I have yet to lose)
                    I wish there would be something more than 100% agreement :-)

                    If I can to modify your statement a bit ... IMO there is no valid reason for storing any processing logs externally. I've been fighting this battle for very long time and I am really sick of half baked ETL solutions with korn logs, sqlplus logs, informatica logs, tivoli logs ... files all over unix boxes with cryptic time stamps. There is no reason - repeat NO REASON not to store any logs (even very high volume) in a database. There might be special cases (compliance offside type requests), even those should go to table first and then to external file
                    • 7. Re: utl_file simultaneous usage
                      user605483
                      the problem in the existing code is a possible 'file rename' (during a log file size check) before the 'write append' - i think this sometimes gives me an ora file rename error, since only the file write append is serialised

                      ok - i can overcome the problem using DBMS_LOCK.ALLOCATE_UNIQUE/DBMS_LOCK.REQUEST/(rename if needed, write append) /DBMS_LOCK.release

                      but logging in a table would be the more convenient (and performant) way
                      • 8. Re: utl_file simultaneous usage
                        damorgan
                        If you log to a table you can query it and report on it. If you log to a file have fun with grep.

                        Grep is great for its intended use. Being a report writer isn't one of them.
                        • 9. Re: utl_file simultaneous usage
                          user605483
                          thanks all
                          • 10. Re: utl_file simultaneous usage
                            pnosko
                            Billy, thanks! This solves other related problems too, like having to use a temporary log file name when the name is based on parameters that need to be edited first for validity. It makes so much sense that I wish I had thought of it first. ;)

                            damorgan-- you too (sorry, I wasn't reading serially)!

                            Edited by: pnosko on Jul 19, 2012 11:00 AM