Synchronize File Acces via PL/SQL UTL_FILE Package

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:

1. The file is being written to by the procedure when the cron job reads it and
2. 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) Received on Tue Aug 13 2002 - 17:43:43 CDT