Implementing an FTP server in PL/SQL

The problem: I recently had to resolve a problem at a client’s site where an automated database provisioning process was regularly failing due to LAN outages.

Under normal circumstances, a DOS batch file was scheduled to fetch a list of files from an FTP server and then call Oracle stored procedures to generate further FTP scripts. After fetching and importing these files, import logs would be sent back to the FTP server.

However, the LAN used between the database server and the FTP server proved to be exceptionally unreliable, causing the Windows-based FTP client to fail in an unpredictable manner and stopping the batch in mid-stride. Quite often this meant that the correct sequence of stored procedures also failed to execute, leaving the import incomplete.

The solution:

Due to the fact that the Windows FTP client does not provide the necessary error/return codes, I decided to re-design the process by bringing all FTP activities into Oracle, treating the whole process as a series of transactions. This makes it possible to keep track of the process status in the database and to rollback incomplete imports. Incomplete activities (eg. sending log files back) can be restarted properly when the next provisioning job runs.

A search for a (free) PL/SQL FTP client directed me to MyOraclePortal, where I found FTP_INTERFACE v.2.0.0.

While this version supports PUT, GET, REMOVE and RENAME functionality, I also needed to see what files were available on the FTP server.

Accordingly, I made the following changes to the package:

•      Added dir and ls functionality,
•     Changed RENAME to REN, since RENAME is an Oracle reserved word (it isn’t a problem, I’m just fussy about reserved words),
•     The variable v_mode is now only used for deciding what to do on local filesystem (otherwise more values are needed for different handling of dir and ls compared to get)
• The variable v_tsfr_mode is used instead of v_mode to determine actions on remote server.

In the current process, only text file transfers are required and the package works well. Before sending my changes to Barry Chase at MyOraclePortal, I (unsuccessfully) tested binary transfers. I haven’t resolved the errors I get with binary transfers, so be forewarned that your mileage may vary (YMMV) if you use my package. To date, Barry hasn’t completed testing my changes yet.
Here’s my version:

FTP interface package