oraclestored-proceduresplsqlwildcardutl-file

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


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

  1. Loads a table with a query I wrote.
  2. 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!


Solution

  • 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