Re: Open a File from Oracle but only if it is close

From: Galen Boyer <galenboyer_at_hotpop.com>
Date: 3 Dec 2004 08:07:14 -0600
Message-ID: <uacsv1npy.fsf@standardandpoors.com>

On 2 Dec 2004, ivl5_at_hotmail.com wrote:
> Galen Boyer <galenboyer_at_hotpop.com> wrote in message
> news:<upt1sxyrp.fsf_at_standardandpoors.com>...

>> On 2 Dec 2004, shirley_sanjuan_at_tutopia.com wrote:
>> > Hi,
>> > 
>> > I am doing a program in Delphi that writes a file, and the
>> > file is read from Oracle using UTL_FILE. But I need Oracle
>> > reads the file only if the another program finish to write
>> > the file.
>> > 
>> > ¿Somebody knows how I validate that?

>
> You may have better chances for a good answer in some other
> newsgroup.
>
> I presume your Delphi program doesn't connect to the database
> (otherwise you'd have several option to pass info between two
> sessions). If Delphi program writes all it needs in a file and
> exits you may get away with a simple lock file. Your program
> can create it before it opens file for writing and deletes
> after it closes the output. Oracle session checks if lock file
> exists, if so, waits till it's gone.
>
> If both processes are writing and reading at the same time,
> consider writing some sort of a delimiter into the file (and
> flush the output after that). Something which tells Oracle
> session "OK, this is a boundary, the info before this point is
> complete".
>

>> Did you test whether utl_file.is_open solves this?  I'm not
>> sure, but it might.  The documentation suggests this, but
>> Oracle might only be able to tell if another Oracle process
>> has it open.

>
> My understanding is utl_file.is_open returns TRUE only if the
> file was successfully opened by the session it was called
> from. The fact it's opened by another Oracle session doesn't
> make it opened in yours. It's actually easy to test: - open two
> sqlplus sessions - open file in both of them and check that
> is_open's return true - close the file in one of the session
> and check that is_open = false and is_open is still true in
> another.

Yeah, I know how to test. I was asking the OP to give it a go.

> I tried to copy utl_file.file_type variable from session where
> file is opened to another where it's not. Is_open returns true
> in this case (I think it just checks that id and datatype
> fields are not nulls). Read attempt gives 'ORA-29282: invalid
> file ID'. Fair enough.

-- 
Galen Boyer

Received on Fri Dec 03 2004 - 08:07:14 CST