Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Synchronize File Acces via PL/SQL UTL_FILE Package

Re: Synchronize File Acces via PL/SQL UTL_FILE Package

From: Mark D Powell <mark.powell_at_eds.com>
Date: 14 Aug 2002 06:30:40 -0700
Message-ID: <178d2795.0208140530.7c84915e@posting.google.com>


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)

Jim, here are a couple of ideas:

Create a lock file. Have both the shell and the db procedure open this file and insert a record on startup if the file is empty. If the file is not empty then sleep and try again a few seconds later.

Upon completion of the main processing open/close the file as output in create mode.

Or convert the shell to Pro*C and have it and the db procedure use dbms_lock./allocate/request/release to control access.

HTH -- Mark D Powell -- Received on Wed Aug 14 2002 - 08:30:40 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US