A:
There was a requirement to generate files as a result of Oracle database batch run and copy those files to a designated Linux server. And then FTP those files to appropriate sites. Depending on the success of the FTP the original files would get archived and consequently there was another database step that took place afterward. And it all should happen in one transaction.
The requirement started and finished with a database task, indicating a database scope. Therefore I decided on a database solution. First, I would generate all the data files from the database, using Oracle packages. The combination of conditional file copying and FTP-ing required host OS commands, a task the plain Oracle database is not capable of. However, from the database I could generate whatever OS shell script I needed. With a use of java I would then execute generated script from within the database. After script's successful completion the last database step would be executed in the same transaction. So in this case I had to utilize Oracle packages, Oracle scripting language PL/SQL, Linux shell scripting and Java.