Steve Montgomerie, December 27, 2001 - 12:35 pm UTC
Tks Tom,
By polling the file do you mean maybe running something through dos using 'AT' to check for existance of the file?
That is the route I am thinking and was just wondering if that was the only way to fly.
PS Any plans for a an Ask Tom DBA book? I've worn out the cover on your latest release!
December 27, 2001 - 1:41 pm UTC
Being a database guy -- I would use DBMS_JOB to schedule a recurring thing (you only want to check when the database is available as well, no sense in looking for the file if you won't be able to process it right?)
No plans for a DBA only book (as the current book isn't Developer only ;) Don't know if/when another one would be forthcoming, they are a ton of work.
Do U have a script available for this
Pawan, March 06, 2002 - 9:58 am UTC
Nice idea. If I have understood correctly then this is what you are saying - " When the FTP is complete i.e. 100% of the file,file.txt,has been FTPed then AUTOMAGICALLY another file,file.don, will appear in another or same directory. The DBMS_JOB should keep polling this directory and once it sees the file.don file the OTHER process should start"
What I need help in is how to use DBMS_JOB to poll this directory and then start another Oracle Process. I tried reading but got confused. I will realy appreciate if you could provide me with an example of say looking for the completion of the FTP process and then loading the data in file.txt into a table.
Thanks in advance for yr help.
March 06, 2002 - 10:38 am UTC
The file "file.don" does not automagically appear -- the person doing the ftp of file.txt would have to add a step to their script to ftp this file "file.don" to let us know the FTP is complete.
Your job would be something like:
.....
is
begin
begin
file := utl_file.fopen( 'some_dir', 'file.don', 'r' );
exception
when utl_file.invalid_path then
-- no data yet, just return
RETURN;
end;
close file, open file.txt instead and process it
.....
You would have that job run every so often, it would "poll" the directory looking for file.don and process file.txt when it found it.
If you don't KNOW the file name (eg: it could be abc.don or xyz.don or whatever) you would use:
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:439619916584 <code>
to look for the names of "*.don" files in a directory and process them instead.
ftp
Matt Anderson, March 06, 2002 - 12:54 pm UTC
Wouldn't it be easier just to rename the file after it has been ftp'ed.
for instance...
put file.dat.inuse
rename file.dat.inuse file.dat
Then just look for *.dat when processing. You know it is done uploading if it has been renamed.
March 06, 2002 - 1:06 pm UTC
is is easier? maybe, maybe not -- sort of a 6 one way, 1/2 dozen the other.
sure you can do that, variation on a theme -- if your ftp server supports rename.
Rename vs ftp.done
Raghu Srinivasan, March 06, 2002 - 1:47 pm UTC
I have had occasion to do it both ways and I would go with the separate .done file. The rename approach is just as good but only when you are expecting just one file. Say you are expecting 5 files a.txt, b.txt and so on. In that case you dont want to go about renaming all of these. Just remember to delete the .done file at the receiving end!
Maybe using Oracle would be better?
Harrison Picot, March 15, 2002 - 4:15 pm UTC
Hi:
I have had to do this, and we used what, to me anyway, is infinitely better. Assuming that this file is part of some process, you open a record when the process starts, and then you update it when the file is transfered ( a C program did the transfer in our case, so it was plenty robust to update the database when the file was ready) and then whatever does the final processing can update the record to indicate it was processed, or there was a problem. The C application polled the database at intervals looking for files that were indicated as ready to process. This can be a lot faster and handle a lot more traffic than sending a file and creating it on both ends. The amount of information you can get back by logging your processes as they move files (or do anything really) is well worth the little bit of effort required to get what ever process you are using, to log the result. Putting the record in the database gives you a lot of flexibility to do other things with that record, like indicate you want email when it is done, or you want timestamps showing when it was done, or you want a work-flow process passed on, or anything else you care to add. In our process, we were updating softare, and all the clients could could check to see if they had the latest version, and if not, download it, but do it when the software was fully copied to the server (of course, it was unix so it was easy).
Cheers,
harrison
Oh, and if you put the size in
Harrison Picot, March 15, 2002 - 4:32 pm UTC
Hi:
I forgot to add, you should put the size of the file in the record, just in case, and you could (but we didn't) put in the result of sum -r (checksum, if you are NT). sorry about following myself but putting the size in was a life saver a couple of times where the transfer croaked.
Cheers,
Harrison
More about ftp'd files
David Scott, January 07, 2003 - 10:33 pm UTC
I've done this a number of ways, and the solution depends on the ability to control the ftp job. My current situation uses an ftp process that automatically renames the file from *tmp to *dat when it's complete; life is simpler this way. In addition, each of my data files includes a trailer record; if that record does not exist, the file is invalid.
Thanks Tom (and others) for a fine thread.
A reader, October 16, 2005 - 10:27 am UTC
Hi tom,
I am stucked up into a big problem plz help me out.I don't know much abt ftps stuff i am an oracle guy what my client is complaining.Scenario is something like this
1)We have some file progress file in y: say x.p.
2)We transfer those files to J: and x: that is mapped to client's drive.
Client is complaing sometimes some files missed out while moving from one location to another.What we have to do we have to check for the deleivery of every file to client side .Either we caqn use timestamp method or ftp method .I was thinking to take the timestamp of a file at y: and compare it to the last modified date from j: and x: if timestamp is equal to or greater than it will replace the existing file .Another method is to send the file by making the ftp connection but i dnt know how to handle exception if ftp failed and how to check it.One file is transmitting from windows NT to windows NT and same file is copied to unix box . How can we make sure that the file reached properly to its destination .can u suggest some' or write some code for us.According to you which method is best to check some file from source to destination.
I am standing at my window and can jump anytime so plz help me out.
Thanks guru in advance
October 16, 2005 - 4:16 pm UTC
sorry, not really sure what to say to you. not really sure what the problem is or how exactly it relates to Oracle?