Solved

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

Posted on 2010-11-08
7
2,567 Views
Last Modified: 2012-05-10
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
Comment
Question by:skahlert2010
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 167 total points
ID: 34090840
hi,

You can use this way

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

you wanted this??
0
 

Author Comment

by:skahlert2010
ID: 34090855
@ 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
 
LVL 28

Assisted Solution

by:Naveen Kumar
Naveen Kumar earned 166 total points
ID: 34090972
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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

Author Comment

by:skahlert2010
ID: 34091014
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
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 34091038
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
 
LVL 7

Assisted Solution

by:jocave
jocave earned 167 total points
ID: 34093213
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
 

Author Closing Comment

by:skahlert2010
ID: 34093478
All answers led to a successfully working solution! Thanks for your ideas and input!

0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup
Suggested Courses

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question