Solved

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

Posted on 2010-11-08
7
2,194 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
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

705 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now