0 
0 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:
Related posts:
About nulls in the database 
Dinsdag 18 september: Oracle Open World & JavaOne 2012 Preview mini-conferenties – 12 presentaties op 1 avond 
Hotsos 2013 – Presentation material “Creating Structure in Unstructured Data” 
Het Oracle OpenWorld Preview Evenement (5 september 2013) – 15 sprekers & sessies 
The AMIS Summary of Oracle OpenWorld 2013 is available for download – 60-page white paper
Just an FYI… I did resolve the binary file transfer issue and added a number of additional features, using some of what Paul did regarding the listing of remote files. I also added handling for BLOB and CLOB so that you don’t even need to technically write the file to the filesystem but instead insert directly into a table or even attach to the PLSQL email solution I also have and email the file elsewhere.
Still free for the downloading and it is now at 3.1.3… http://www.myoracleportal.net
P.S. Thanks for the plug Paul. I need to add your credits to the package header with respect to the directory listing feature. When you get a chance you might take a look at the new version.
A little late perhaps but you could try this free, fully featured PL/SQL FTP client, which is as close to UTL_FTP as is possible to get (IMHO):
http://www.chrispoole.co.uk/apps/xutlftp.htm
I’m a bit late replying I know, but FYI I’m using your package for transferring binary files and it’s working like a dream, nice work!
Cheers
Graham
Hi Paul,
There is also a source forge project UTL_FTP – http://sourceforge.net/projects/plsqlftp/ –
hth
cheers
pete
Hi.
I wrote something similar that may be of interest to you:
http://www.oracle-base.com/articles/9i/FTPFromPLSQL9i.php
I’m surprised Oracle have not released an official FTP API, like UTL_MAIL.
Cheers
Tim…