Solved

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

Posted on 2010-11-08
7
2,396 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
Independent Software Vendors: 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!

 

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

Independent Software Vendors: 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!

Question has a verified solution.

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

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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…

756 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