FTP and SFTP on the Database…
When designing your application with an Oracle database, you may find more productive to trigger FTP jobs from inside the database itself.
If you rely on simple FTP protocol, you can use two main approaches:
- Build a Java procedure to run shell scripts (with a PL/SQL wrapper), like the one available here, and invoke a third party FTP software. This option has the advantage of less code to manage, but of course, you will have to depend on third party software, which depending on your application’s context may be an unwanted feature.
- Develop a pure PL/SQL package to send and receive files. Using the UTL_TCP and UTL_FILE packages, you can easily implemente a FTP API. Even easier is using an already implemented API, like the one available on Oracle-Base and the XUTL_FTP package. You may find more examples throughout the Oracle Bloggers or on the OTN network.
Now, if your application is evolving, and you now wish to use SFTP directly from the database, the PL/SQL native packages don’t allow you to implemente a SFTP API, like the FTP APIs shown above.
First of all, do not confuse SFTP with FTPS. SFTP is based on the SSH protocol, and is called SFTP simply merely to “marketing techniques” by the SSH group. FTPS is the secure version of the original FTP protocol.
In my case I was called to implement a SFTP API for the database. Well, the lack of PL/SQL infrastructure to do so I turned to JAVA! Using the ChannelSFTP class of the JSch package, I easily implemented a simple API to send and receive files via SFTP.
Finally with the .jar deploy I loaded my package to the Oracle database (after loading the JSch jar and the Jzlib jar) and build the PL/SQL wrappers.
The first battery of tests demonstrated: success! SFTP from the database!
In conclusion, Oracle’s architecture allows multiple solutions to a problem, and with Java there’s always a way out!
If you want the code for the Java SFTP API or/and PL/SQL wrappers fell free to email me.
