Delete file from server using UTL_FILE.FRemove without knowing the file name

Question

I am a reporting analyst, who was asked to learn some PL/SQL to automate some processes. So I am almost finished, with one step standing in the way. I have a package that

Loads a table with a query I wrote.
Exports the results from that table to a .txt file on the server with the current_date tacked onto the file name .

I am trying to delete the 3 files it creates using a wildcard, but I continually get errors such as "vendor_file.ia.*.txt is not defined":

file_location is my path

I can delete it no problem with:

    utl_file.fremove(file_location,'vendor_file.ia.09.02.2015.txt');
    utl_file.fremove(file_location,'vendor_file.il.09.02.2015.txt');
    utl_file.fremove(file_location,'vendor_file.sd.09.02.2015.txt');

But obviously that won't delete it when it gets run next month. So am I missing a simple wildcard to search just for 'vendor_file.ia.*' And does the syntax look in Oracle?

If I didn't provide enough information please let me know!

Thanks a lot!

You'd have to know the exact file name in order to call `fremove`. You can't pass a wild card. If you don't mind some hackery (particularly relying on `x$` tables) you can use `dbms_backup_restore` to list the files in a directory https://davidalejomarcos.wordpress.com/2011/09/13/how-to-list-files-on-a-directory-from-oracle-database/ Or you can use a Java stored procedure https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:439619916584 — Justin Cave, Sep 02 '15 at 21:10
My solution would be to create a table with two fields 1) filename, 2) deleted_flag. Write the file-name to this table when it is created and then when you are ready to delete the file look for any filenames that are not marked as deleted and delete them and mark the record as deleted in the table. — Shaun Peterson, Sep 02 '15 at 23:29
@ShaunPeterson - or just a table with filename in it. Delete the row in the table when the file is deleted. — Bob Jarvis - Слава Україні, Sep 03 '15 at 01:48
@BobJarvis that would work to, I would just lean towards having an audit, so that I can see what files have been created / deleted, for debugging / tracing. — Shaun Peterson, Sep 03 '15 at 04:46
Thanks a lot for your help, fellas! I am going to try and make the table with the name. The hackery might be out of my skill set. — Ben O'Neill, Sep 03 '15 at 13:19

score 4 · Answer 1 · answered Jun 13 '19 at 21:55

4

This can be used to clear multiple files from the Oracle directory
begin for i in (select filename from (select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DIR_NAME')) order by mtime) where filename like 'backup%') loop UTL_FILE.FREMOVE ('DIR_NAME', i.filename ); end loop; end; /

answered Jun 13 '19 at 21:55

Pankaj

41
1

1

Note: `RDSADMIN` is an AWS package, so it only makes sense in RDS, obviously. – rgoliveira Nov 12 '19 at 13:19

score 2 · Accepted Answer · answered Mar 22 '16 at 16:00

2

Old post but...

You can make an external table list out a directory file contents. You could then write a loop to get your files names from the external table and execute utl_file.fremove

answered Mar 22 '16 at 16:00

Turntablez

131
3

score 0 · Answer 3 · answered Nov 15 '17 at 15:51

0

You could use the preprocessor to run a shell script to delete the files. Oracle docs on preprocessor

answered Nov 15 '17 at 15:51

Richard Gambrell

1

score -1 · Answer 4 · edited Sep 03 '20 at 09:35

-1

How we schedule this script using Oracle scheduler:

 begin
    for i in (select filename from (select * from 
    table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DIR_NAME')) order by mtime) where filename like 
    'backup%')
    loop
    UTL_FILE.FREMOVE ('DIR_NAME', i.filename );
    end loop;
    end;
    /

edited Sep 03 '20 at 09:35

David Buck

3,752
35
31
35

answered Sep 02 '20 at 12:40

honey

1

This answer is already posted by @pankaj – khalidmehmoodawan Mar 06 '23 at 22:42

Delete file from server using UTL_FILE.FRemove without knowing the file name

4 Answers4