A:
There was a requirement to generate files as a result of Oracle database batch run. Those files would be first copied to a dedicated Linux server and then FTPed to appropriate sites. Depending on the success of the FTP step, the original files would get archived and another database step would take place afterwards. 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 the use of Java I would then execute the generated script from within the database. After the 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.