Re: Synchronize File Acces via PL/SQL UTL_FILE Package

jp_mcmahon_at_hotmail.com (Jim McMahon) wrote in message news:<3d598aae.166352732_at_news.charter.net>...
> I'm trying to synchronize the writing and reading of a file created
> and/or appended to via a PL/SQL stored procedure using the UTL_FILE
> package and read by an intermittent cron job executing a shell script
> to securely copy the file to another server.
>
> In the stored procedure I open the file with file mode 'a' for append,
> and the file is properly built or appended to as appropriate when I
> fire the procedure from an application running on a client. This
> works for mutliple executions of the procedure - if the file is
> already there I append to it, if not I create it.
>
> The problem arises (sometimes) when I introduce a periodic cron job
> running external to the database to move the file at the same time it
> is written to.
>
> As an internal test, I looped through a condition that would invoke
> the stored procedure to write 4 lines to the file 2500 times, while at
> the same time ensuring that the cron job would be invoked while this
> was going on. Specifically, it takes about 10 minutes to call the
> procedure 2500 times (writing 4 lines each time) and the cron job is
> set up for a 2 minute schedule to move the file to another directory
> and timestamp the moved file. On some runs, the total number of lines
> in all moved files is 10,000 as it should be, but on some runs it is
> only 9996 - I lose 4 lines, the exact number of lines produced by a
> single invocation of the stored procedure.
>
> My guess is that between the time a particular invocation of the
> stored procedure opens and closes the file, the external cron job
> comes along and moves it.
>
> My simulation is not fully realistic - it's very unlikely that the
> procedure will be invoked 2500 times in immediate sucession - but I am
> tryin to simulate the possibilities that:
> a. The file is being written to by the procedure when the cron job
> reads it and
> b. Multiple users execute the stored procedure at the same time (on
> different records) from the client application
>
> I've looked, but haven't found any documentation on the UTL_FILE
> package to indicate a standard method for checking if someone else has
> a file open (file busy). If there is a condition I can check for
> within the proc, I could simply loop until the file is not busy, and
> then either append to it or create a new one. Alternatively, I'm
> thinking there must be some way to temporarily lock the file when the
> cron job grabs it. Either way would be OK with me.
>
> Does anyone have any suggestions for how to synchronize such file
> access between a stored PL/SQL procedure using UTL_FILE and a shell
> script?
>
> I'd rather not have to create unique file names for each proc
> invocation, but can if I have to - but that still wouldn't solve the
> problem of the proc and the cron job hitting the file simultaneously.
>
> I'm certainly open to even the most obvious of solutions. I'm not a
> regular PL/SQL or shell programmer - my main job is the client
> application that fires this procedure.
>
>
> Being ordinary and nothing special is a full-time job.
> jp_mcmahon_at_hotmail.com (Jim McMahon in real life)

Would'nt it be easier to have your cron job determine if a file is opened by another process. If a file is not open then move it else wait until next iteration. Try,
$ man fuser OR
$ man pstat
Or some other utility/command on Unix that you can use and message the output to detect an open file. comp.os.unix may also help.

If above is not an option, then writing and renaming a file could be an option. e.g.
utl_file will always open a file with .processing or similar extension. Append/Write to it and change filename to something like .done right after it. Your cron job then will process *only* .done file extensions. If no files found with .done extension, wait for next iteration.

Now this would be perfect if UTL_FILE could rename a file...which ofcourse it cannot. Which brings you to java cartridge in Oracle database. Create a java stored procedure using java.io API and you can do all kinds of file tricks even completely bypassing utl_file package.

HTH
//Rauf Sarwar Received on Wed Aug 14 2002 - 12:28:34 CDT