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

From: Igor Laletin <ivl5_at_hotmail.com>
Date: 2 Dec 2004 19:23:07 -0800
Message-ID: <1e266e0d.0412021923.7b23a52d@posting.google.com>

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.

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.

Cheers,
Igor Received on Thu Dec 02 2004 - 21:23:07 CST