How to create a for loop statement for removing files listed in Oracle table?

Shell Programming and Scripting
1

Hello Frens,

I am a newbie to shell scripting. I need a help on creating a for loop script (shell script) for removing files. I have a table called a_table with column name fil_name which contains all the files that need to be removed.

Thank you in advance

2

Hi manisha,

do you have already an sql-query which gets you the files you want to delete? If yes please show the query if possible as a command like it is run directly from the shell. That will be a good starting point to help you.

I don't know how that looks like with oracle. Here is a statement with MySQL:

echo "SELECT name,phone,mail FROM address;" | mysql DB_NAME

Ideally check the documentation for your oracle-command-line client to leave out any headers, footers and produce output in easy parsable format. This is not required, but makes it easier for you, so you do not have to bother filtering all the superfluous junk out.

3

Maybe something like (completely untested; be VERY careful when executing!)

sqlplus user/password 'select "rm " fil_name from a_table;' | sh
4

Thank you for your reply. My sql query is very simple. select filename from a_table. There are like 45 files, taking that filename, I have to run 'rm' command a shell script.

---------- Post updated at 07:57 PM ---------- Previous update was at 07:55 PM ----------

Thank you for your reply. My SQL query is very simple. select filename from a_table. There are like 45 files, taking that filename, I have to run 'rm' command a shell script. How can I put a for loop in this command?

5

extract the filename and store in temporary file (/tmp/extracted_file)

#!/bin/bash
while read filename
do
    echo "removing $filename"
    rm -f ${filename}
done < /tmp/extracted_file
6

@itKamaraj
Thank you so much. I will try this. Is there any other way to put sql statement to remove the files?

7

It worked. thank you so muc.