• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3254
  • 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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