The most fundamental piece of an FTP server is the ability to send packets of data from one network location to another. Fortunately, the Oracle database has built-in networking functionality that is made accessible through the core utl_tcp package. Using this core functionality and a few pieces of information we can create an FTP interface that can be called directly from a custom PL/SQL package to send a file from one server to another.

There are certain security aspects that I will skim over in this article: 1) using FTP with secured user access, 2) document storage and access, and 3) access to the interface itself. Each of these aspects is important to overall system security, but I will not take the time to demonstrate secure solutions in this article.

To setup the system, we'll create two system defaults: FTP - HOST and FTP - PORT. These indicate the server and port (typically port 21) to which files will be sent. Because we need to collect system defaults programmatically, we will plan to use the sys_default function that was described in the Creating an Email Interface in SQL*LIMS article.

Here's the code that we'll use for the PL/SQL FTP interface with line numbers for clarity:

1  CREATE OR REPLACE PROCEDURE send_ftp(name_in IN VARCHAR2,
2        file_in IN VARCHAR2) AS
3     v_cmd VARCHAR2(4);
4     v_datconn utl_tcp.connection;
5     v_conn utl_tcp.connection;
6     v_port_add PLS_INTEGER;
7     v_port_dec PLS_INTEGER;
8     v_reply VARCHAR2(512);
9     v_stat PLS_INTEGER;
10 BEGIN
11    v_conn := utl_tcp.open_connection(sys_default('FTP - HOST'),
12       sys_default('FTP - PORT'));
13    
14    WHILE NVL(v_cmd,'x') != '221 ' LOOP
15       IF v_cmd = '220-' THEN
16          v_stat := utl_tcp.write_line(v_conn, 'USER anonymous');
17       ELSIF v_cmd = '331 ' THEN
18          v_stat := utl_tcp.write_line(v_conn, 'PASS me@here.com);
19       ELSIF v_cmd = '230 ' THEN
20          v_stat := utl_tcp.write_line(v_conn, 'PASV');
21       ELSIF v_cmd = '227 ' THEN
22          v_reply := substr(v_reply, instr(v_reply, '(',1,1)+1,
23             instr(v_reply, ')',1,1) - instr(v_reply,'(',1,1)-1);
24          v_port_dec := substr(v_reply, instr(v_reply,',',1,4)+1,
25             (instr(v_reply,',',1,5) - (instr(v_reply,',',1,4)+1)));
26          v_port_add := substr(v_reply, instr(v_reply,',',1,5)+1,
27             length(v_reply) - instr(v_reply,',',1,5));
28          v_datconn := utl_tcp.open_connection(
29             sys_default('FTP - HOST'),
30             (v_port_dec * 256)+v_port_add);
31          v_stat := utl_tcp.write_line(v_conn, 'STOR ' || name_in);
32       ELSIF v_cmd = '125 ' THEN
33          v_stat := utl_tcp.write_text(v_datconn, file_in);
34          utl_tcp.close_connection(v_datconn);
35       ELSIF v_cmd = '226 ' THEN
36          v_stat := utl_tcp.write_line(v_conn, 'QUIT');
37       END IF;
38       v_stat := utl_tcp.read_line(v_conn, v_reply);
39       v_cmd := SUBSTR(v_reply, 1, 4);
40    END LOOP;
41    
42    utl_tcp.close_connection(v_conn);
43 EXCEPTION WHEN OTHERS THEN
44       dbms_output.put_line('ERROR: ' || SQLERRM);
45       utl_tcp.close_all_connections();
46 END send_ftp;

Lines 11-12 use the System Default function to return the FTP host and port number and the UTL TCP Package Open Connection function to open a connection to the server.

Lines 14 and 40 open and close a loop to step through the server's responses. This is necessary because FTP servers return a varying number of text lines when responding to certain commands. Lines 15 through 37 contain an IF block that contains each of the commands to which we need to respond.

Line 16 and 18 send the username and password to the server. These values would normally be stored in a secure location, and would not use an anonymous login.

Line 20 tells the FTP server to enter passive mode, which allows the client to contact the server with a new data port.

Lines 22 through 27 calculate the port on which data will be sent. The standard FTP protocol operates on two ports, the command port, which is typically 21, and the data port, which can be any port above 1024. Because servers are frequently contacted from outside the current network, traffic on the command port usually passes through firewalls and routers easily. Once the connection is made, the FTP client specifies the passive mode and calculates a new, unprivileged, or private, port on which to send the file data.

Line 28 through 30 opens the data port, and line 31 tells the server to store the incoming data as a specific file name.

Lines 33 through 34 send the file data and close the data port.

Line 36 tells the FTP server that we intend to quit, and line 42 carries it out.

To test the interface, start an FTP server that accepts anonymous connections and modify the FTP - HOST and FTP – PORT SQL*LIMS system defaults to contain the server information. Open SQL*PLUS as the SQL*LIMS Owner and execute the following:

exec send_ftp('test.txt', 'Very cool FTP transfer, I must say!');

Look in the root directory of the FTP server for a file named test.txt, it should contain the sentence, "Very cool FTP transfer, I must say!" If it does not, try logging into the FTP server from a command prompt using the information stored in the FTP - HOST and FTP – PORT SQL*LIMS system defaults. This will verify that the server does, in fact, accept anonymous connections.

Overall, our FTP client is decently prepared for standard text file transfer, but lacks robustness. If we were looking to add features, we would allow the procedure to accept the file as an Oracle CLOB, support binary transfer modes, look to see if the filename already exists on the destination system, more adroitly handle common error conditions, etc. Finally, we’d want to build in some kind of queuing mechanism so that if the destination server were unavailable for some reason we wouldn’t loose our data. That would not make us happy!