Skip to Main Content
  • Questions

  • Created file using UTL_FILE. How to prevent file from being picked up before it has been fully created

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, rupa.

Asked: February 06, 2019 - 6:46 pm UTC

Last updated: February 07, 2019 - 2:15 am UTC

Version: 12c

Viewed 1000+ times

You Asked

We have an integration where the file is being created in oracle ebs using utl_file utility. The file is placed on the server where biztalk pings every 60 sec and picks it up. In one scenario the file was picked up before it was written out resulting in 0 byte file being transmitted.
How can i lock the file or what is the best way to prevent this issue

and Connor said...

Couple of options

1) second file

- write 'x' bytes to MY_REAL_FILE.DAT
- write 'x' bytes to MY_REAL_FILE.DAT
- write 'x' bytes to MY_REAL_FILE.DAT
- write LAST 'x' bytes to MY_REAL_FILE.DAT
- write 'DONE' to a file called MY_REAL_FILE.DONE

The monitor program watched for the existence of MY_REAL_FILE.DONE before attempting to get MY_REAL_FILE.DAT

2) rename

- write 'x' bytes to MY_REAL_FILE.WORK_IN_PROGRESS
- write 'x' bytes to MY_REAL_FILE.WORK_IN_PROGRESS
- write 'x' bytes to MY_REAL_FILE.WORK_IN_PROGRESS
- write 'x' bytes to MY_REAL_FILE.WORK_IN_PROGRESS
- rename MY_REAL_FILE.WORK_IN_PROGRESS to MY_REAL_FILE.DONE


Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library