I am looking for some experties relative to unloading data from a couple of tables to flat files (to be FTPed during night) as records are INSERTed into the DB.
There is a solution to run UNIX scripts which will query and join the tables as data is needed to a specific format and unload records to the flat files. This job will run a couple of times per day and my first question is how error prone is such process when it is possible to deal with a table that will potentially hold millions of recs (until they are purged at the end of the month). Also, running scrits is not OOP, no code modules and all the benefits of OOP.
Second solution is to write to the flat file(s) thru the call of Java strored proc once an the INSERT occurs for each table. Actually, Java stored proc will append to a collection only, and I plan to have a Java listener class that will check the length of the collection (static member) and attempt to write to the file for which I already have a handle on, without having to open and close the file everytime I write/append.
Which one would you choose? For my own curiosity please mention if you are a DB person/admin or a Java developer with knowledge of SQL and feel comfortable to work with DB procs.
Any additional comments, pls feel free.