Find files not loaded on another server

[commatom]

Im an Oracle guy looking for some help here.

I have to go out to another server and FTP a file back to my UNIX box. I then use SQL*Loader to load the file into an Oracle table. Thats the easy part.

The part that is messing me up is how to I find the files that I have not loaded yet? The file has a name like file_20071015000000.log the number there being a date. Now there is only one file a day but sometime the file is late and I need to load to days worth of logs.

I dont expect anyone to write this for me but can someone point me in the write direction on how to do the following.

1) Know which was the last file name I loaded.

2) Know what files I need to get.

Now once I get it onto my unix box I can do the rest. Im thinking I should write something like "The last file I loaded was file_20071012000000. Today is 10/16 so I should look for
file_20071013000000
file_20071014000000
file_20071015000000
file_20071016000000

Suggestions?

 

[commatom]

OK so I have made some progress

I was able to use AWK to get the date from the file I most recently loaded.

20071014

And I was able to find the same format of the system date.

20071016

Now I just need to figure out how to generate the ftp script to go get my missing files.

20071014
20071015
20071016

 

[cyoung]

Why wouldn't you remove the files from the FTP server after you have copied them?

That way the only files available on the FTP server will be the files that you need to process.

 

[commatom]

Spooky...you responded at the exact same time 8 days later.

Anyway, I cant delete the files because other people use them as well.

What I did was write to an Oracle table the last file loaded. Then I use SQL to generate the "get" commands and look for files I may need.

There is probably a more efficient way to do it but Oracle is my forte and it works.