Home » SQL & PL/SQL » SQL & PL/SQL » Problem moving files after using UTL_FILE package (P/SQL, Oracle 8i)
Problem moving files after using UTL_FILE package [message #577838] Thu, 21 February 2013 02:27 Go to next message
dev_777
Messages: 30
Registered: June 2010
Member
Hi

We have a p/slq procedure that reads a *.txt file using the UTL_FILE package. The contents of the file are then inserted into a database table.

At the end of the procedure we close the open file using UTL_FILE.FCLOSE.

There is a program (non-oracle)that attempts to move the file to a new location after being read into Oracle. The problem is that the application cannot move the file as the file is locked. ie message displays that the file is open and cannot be moved to a new location.

Is there anything else that we are missing besides the UTL_FILE.FCLOSE.




Re: Problem moving files after using UTL_FILE package [message #577840 is a reply to message #577838] Thu, 21 February 2013 02:47 Go to previous messageGo to next message
dariyoosh
Messages: 531
Registered: March 2009
Location: Iran / France
Senior Member
According to the online UTL_FILE documentation:
http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/u_file.htm#i1003326
Quote:

Usage Notes:

If there is buffered data yet to be written when FCLOSE runs, then you may receive a WRITE_ERROR exception when closing a file.

Exceptions:

WRITE_ERROR
INVALID_FILEHANDLE




Do you check for any of these in your procedure?


Regards,
Dariyoosh
Re: Problem moving files after using UTL_FILE package [message #577846 is a reply to message #577838] Thu, 21 February 2013 03:11 Go to previous messageGo to next message
Michel Cadot
Messages: 58641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What is your OS and version?

Regards
Michel
Re: Problem moving files after using UTL_FILE package [message #577851 is a reply to message #577846] Thu, 21 February 2013 04:13 Go to previous messageGo to next message
dev_777
Messages: 30
Registered: June 2010
Member
Windows Server 2008 r2
Re: Problem moving files after using UTL_FILE package [message #577854 is a reply to message #577851] Thu, 21 February 2013 04:53 Go to previous messageGo to next message
Michel Cadot
Messages: 58641
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I didn't see Oracle 8i.
File is closed but lock is not released.
Windows 2008 + Oracle 8i is not a supported combination.
Either upgrade to Oracle 11g or downgrade OS to Windows NT4 or XP.

Regards
Michel

[Updated on: Thu, 21 February 2013 05:45]

Report message to a moderator

Re: Problem moving files after using UTL_FILE package [message #577858 is a reply to message #577854] Thu, 21 February 2013 05:17 Go to previous message
dev_777
Messages: 30
Registered: June 2010
Member
Thanks for info. Would convey suggestion.
Previous Topic: Help me on Basics in PL/SQL
Next Topic: Splitting the Give string into multiple sub strings
Goto Forum:
  


Current Time: Fri Aug 01 04:56:12 CDT 2014

Total time taken to generate the page: 0.05442 seconds