CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqlwildcardutl-file
Ben O'Neill
Ben O'Neill

Reputation: 75

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":

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!

Upvotes: 4

Views: 33909

Answers (4)

Pankaj
Pankaj

Reputation: 51

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;

Upvotes: 5

honey
honey

Reputation: 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;
    /

Upvotes: -1

Richard Gambrell
Richard Gambrell

Reputation: 1

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

Upvotes: 0

Turntablez
Turntablez

Reputation: 131

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

Upvotes: 2

Related Questions