Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2844
  • Last Modified:

UTL_FILE REMOVE all files where filename like '%evaluation%'

Dear all!

I want to create a procedure to delete all files from a directory, whose names contain the string 'evaluation'. For instance the filename is "2010-11-05-01-25-16_QB-Evaluation_6333-372_LRT_9180_WG_6_BE_0.xml". I have plenty of theses files with different attributes denoted in the names.

I know how to access and remove files with static/specific filenames from the directory. However, I am not sure how to access the files by having only a part of the file name, hence the evaluation.

Can you please help me to get started with it!? I need to loop through the files from the directory and delete all files where the filename matches '%evaluation%'.

I hope it is even possible.

Many thanks,

skahlert2010

I appreciate your help.
0
skahlert2010
Asked:
skahlert2010
3 Solutions
 
Bhavesh ShahLead AnalysistCommented:
hi,

You can use this way

Select * from tablename
where filename like '%evaluation%'

you wanted this??
0
 
skahlert2010Author Commented:
@ Brichsoft!
UnfortunatelyI it's not that easy since my files are saved in a server directory as external BFILES.
No table where I could issue a select on...

Any other ideas?

0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
i think you can come up with a unix shell script or ( windows batch script ) to delete all those unwanted files which you need .. ( i,e those which have evaluation etc ).. and this script can be called from oracle database through a job/schedule.

Thanks
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
skahlert2010Author Commented:
Hello nav_kum! You're right! This is what I've done before. I was simply interested if the same can be achieved via pl/sql process. I have another attempt now....

I just created a table that logs the filenames and a couple of attributes whenever a file is created in my database directory.
Afterwards I'll create procedure using a cursor and delete all files using a database job each midnight.

I think that'll do it! What do you think?

Many thanks!!!

0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
yes, if you have a table on which you can run a query to determine which files need to be deleted. it becomes very simple to use ULT_FILE.FREMOVE(..) in a cursor loop to delete them.

Thanks
0
 
jocaveCommented:
Another option would be to use a Java stored procedure that lists the contents of a directory on the operating system and then iterate over the files you want to delete calling UTL_FILE.FREMOVE.  There are a few different implementations of the Java stored procedure floating around online but I've used Tom Kyte's DirList quite successfully

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:439619916584

If you go with the Java stored procedure approach, that eliminates the potential for the Oracle table and the files stored in the directory to get out of sync.
0
 
skahlert2010Author Commented:
All answers led to a successfully working solution! Thanks for your ideas and input!

0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now