This is an APEX project but my issue seems to be PL SQL related so I'm posting here. The project lets the user upload a file which is transferred to the file system. A program executes on it generating a log file, the log file is downloaded and both files are deleted.
I found however I kept encounter a file access error when trying to delete the log file using utl_file.fremove:
ORA-29291: file remove operation failed
So I obviously need to close the file so I call DBMS_LOB.FILECLOSEALL, however doing that gets me this:
ORA-22289: cannot perform FILECLOSEALL operation on an unopened file or LOB
If I attempt to delete it manually through the file system I get the told the file is open in my service. Yet DBMS_LOB.FILEISOPEN returns false.
One final complication: this doesn't always happen. I'd say about 90% of the time the log file deletes successfully, however sometimes I wind up with a file like this that refuses to be deleted so it's not simply a permissions issue in the directory.
So I'm pretty confused. Everything is saying the file is closed, until I try and delete it, then the file is open.
OS: Windows Server 2008 R2 64-bit (6.1, Build 7601)
Oracle Version: 11g Enterprise Edition Release 11.2.0.4.0 64-bit