utl_file
Freek, December 14, 2003 - 3:24 pm UTC
You could use the utl_file.fremove procedure to delete a file from disk.
But storing them in the db would be indeed better.
December 14, 2003 - 5:51 pm UTC
doh, i keep forgetting about that -- utl_file was greatly expanded in 9i
o support for directory objects (no more utl_file_dir)
o fcopy
o fgetattr (file attributes)
o fgetpos
o FREMOVE
o frename
o fseek
o get_RAW
o put_RAW
thanks :)
cheating...
Connor, December 15, 2003 - 12:17 am UTC
Or you could have a tinker with DBMS_BACKUP_RESTORE for those on pre-9 databases.
Remove documents from file system
Gus Spier, December 15, 2003 - 8:37 am UTC
Yes, sir! You are right and I've told them and told them ... and in the next release of the system, I think the documents will be in the database. For now, the operation insists on storing in the file system.
Thanks for the answer! (fremove, huh? Where's that documentation again?)
Gus
using isopen function
Duncan Mein, January 22, 2004 - 5:59 am UTC
Great advice tom!!
Tell me. Can you give me an example of using the UTL_FILE.isopen function.
I need to check if a file is open(locked) before executing the UTL_FILE.fremove procedure.
i.e.
Before running
UTL_FILE.fremove('DATA_DIR', 'Tester.csv');
i need to check is the file tester.csv is open. If so then I need to exit before the fremove procedure is run.
Any help would be much appreciated.
Thanks in advance
Duncan
January 22, 2004 - 7:13 am UTC
how would your logic allow you to get into a situation where you would even consider erasing a file you have open? seems like a "bug in the algorithm" to me.
The problem here is -- in order to see if tester.csv was opened by you you would need to look at the plsql variable that is the "open file" handle -- but if you knew that - well, you wouldn't probably even need to see if it was "open", you'd know.
You cannot get here from there. You'd have to
a) know of every variable in your code that is a file type
b) know the name of the file associated with it
c) so you could "isopen" that variable
but if you know "b", you would not need to do "c".
seems like a problem in your implementation here.
Just to clarify!
Duncan, January 23, 2004 - 7:03 am UTC
Thanks for your prompt response.
Just to clarify I do not want to perform check to see if I have the file open because that as you so rightly pointed out would be a complete waste of time.
The files may be opened by multiple users who have access to the server drive and the check i want to perform is based on the fact that one or many of the users may have the file open when the fremove procedure is run.
In all reality this should never happen but i need to code for such an eventuality. I realize that this is not an ideal situation however and would be grateful of an example.
Thanks in advance again
Duncan
January 23, 2004 - 8:08 am UTC
cannot do it.
but still - the logic here is "warped".
"if the file isn't open right at this second, let's erase it. So what if someone just decided to open the file -- erase it."
On some OS's you won't be able to erase a file others have open (eg: windows)
On other OS's it won't MATTER if you erase a file others have open (eg: unix) as they will continue to be able to read and process that file -- when the last user actually closes the file -- poof -- it goes away.
What you could do is
a) in your code, use DBMS_LOCK to get a shared lock named after the OS file. You would do this in ALL CODE that opens the file
b) in the code that erases the file -- you would get an exclusive lock named after the file -- this will block (or not, you decide) if anyone has a shared lock
that is -- you would need to use semaphores in your code (via locks) in order to make sure you don't erase a file other sessions have open.
Randy, February 08, 2006 - 12:53 pm UTC
Tom,
I have a similar situation where I output a user report to the server using UTL_FILE. I then redirect the user to that report using OWA_UTIL.redirect_url. However, I would like to delete that report from the server once it has been opened by the client. I'd like to think of this as a temp file that gets created on the server until the file has been opened. Any ideas on how to achieve this? I tried UTL_FILE.fremove after the OWA_UTIL but the file gets removed before the browser opens the file.
Thanks in advance
February 09, 2006 - 4:43 am UTC
well, intead of creating the file with utl_file, why not just change the utl_file.put_lines into htp.p()'s?????
(meaning, you do not need the overhead of creating the file in the first place at all!)
and if that is not possible, you'll have to redirect them to a routine that gets the filename, opens the file, reads the file, htp.p's the file back to the client and then erases it (and hope the user never ever hits reload!)
Randy, February 09, 2006 - 10:16 am UTC
Hi Tom,
utl_file is required because the report is exported to a word file, then pushed back to the client. I tried the following:
utl_file.fclose(l_file);
OWA_UTIL.redirect_url('/docs/my_archive_export.doc');
UTL_FILE.fremove('MY_DIRECTORY', 'my_archive_export.doc');
end;
However, when I run this, the report is deleted before the client receives the file. Any thoughts on how can I avoid the file from being removed until after the file is downloaded?
Thank you,
Randy
February 10, 2006 - 11:13 am UTC
you are writing a binary file?
but even so, utl_file is NOT required.
If you can return it via a file that plsql generates
THEN you can return it via plsql directly (must be true, has to be true!)
Randy, February 10, 2006 - 3:44 pm UTC
Hi Tom,
Yes, I was able to do this WITHOUT using UTL_FILE -- which did solve my problem.
Thank you for pointing me in the right direction!
Appreciate your time,
Randy