|
|
|
|
Oracle 8i PL/SQL FTP Protocol Implementation
Download your files
directly to the Oracle database
Dmitry Bouzolin. dbouzolin@yahoo.com
(413)-540-4451
| The TCP, HTTP and FTP are one of the basic" bricks" of the communication process between systems and applications over the Internet. TCP is more generic as others are based in it. |
Oracle database technologies hold universal niche in today's highly connected and computerized world. Oracle was one of the first companies, which integrated the Internet technologies into its core software product: Oracle Server. The one of the Oracle innovations were introducing special set of PL/SQL packages to work with TCP and HTTP protocols. Package UTL_HTTP was introduced in version 7.3.4 and allows downloading web pages to the Oracle database. Package UTL_TCP, which allows opening TCP connections and submit and retrieve raw TCP data, was introduced in version 8.1.6. However, at the present time, there is no support for the FTP protocol in Oracle Server in form of suitable PL/SQL package.
Developing package which implements FTP protocol support would give obvious benefits in some information system implementation projects:
1. Reduced amount of architectural layers of the information system. For instance, as soon as you can download files to Oracle database directly, you don't need to write special programs or interfaces to download data and upload them into Oracle database.
2. Reduced number of implementation errors and problems related with them.
As a result total system implementation time would be considerably decreased.
This paper provides an example of the FTP protocol implementation as an Oracle PL/SQL package. Writing a package which supports even minimal FTP functionality is complex and time consuming process, so I limit this paper just showing the basic examples: login onto ftp site, download text file and logout from site.
The FTP is a high-level communication protocol to transfer files between different computers possibly even with different underlying system architectures. The detailed FTP standard is described in (3). Here some key information how it works:
1. FTP model consists of server PI (protocol interpreter), user PI, server DTP (data transfer process), user DTP, control connection and data connection.
2. Server PI communicates with user PI over control connection. Server DTP communicates with user DTP over data connection. Server DTP works with ftp server file system and user DTP works with file system on user machine.
3. User PI sends commands to a server PI over control connection.
4. The format of the command is command <parameter>. For instance: USER dbouzolin.
5. Server PI responds to the user PI returning 3 digits (xyz) return code with possible descriptive text.
For instance: 331 User name ok enter password. Server reply may consists of several lines, and in this case, will be composed from 3-digit reply code followed by "-" symbol followed by descriptive text. In this article I use only small number of the ftp reply codes: "220 Service ready for new user", "221 Service closing control connection", "230 User logged in, proceed", "331 User name okay, need password", "150 File status okay; about to open data connection".
6. The meaning of the server reply codes are (other two digits give a finer gradation)
- 1yz: Positive Preliminary reply
- 2yz: Positive Completion reply
- 3yz: Positive Intermediate reply
- 4yz: Transient Negative Completion reply
- 5yz: Permanent Negative Completion reply
7. The data transfer takes place over data connection. Data connection can be either server initiated, where ftp server opens data port on user machine or user initiated, where user PI opens data connection on ftp server. In the first case user PI should send IP address and port number where it will listen for the server connection. In the second case server PI sends IP address and port number where it will listen for a user connection.
|
Java support must be enabled for an Oracle database, otherwise you can get an error: "Class oracle/plsql/net/TCPConnection Does Not Exist". To enable Java run following scripts as SYS: $ORACLE_HOME/javavm/install/initjvm.sql, $ORACLE_HOME/rdbms/admin/ initplsj.sql |
The following types were created to simplify program logic: TStringTable, TServerReply, TServerReplyA and TConnectInfo.
TStringTable is used to hold lines of the text file, which we are going to retrieve from ftp server.
TServerReply is intermediate record type, used to hold individual line of the ftp server response.
TserverReplyA is the table of "TServerReplies" to hold full server reply message.
TConnectInfo is used hold IP address and port number.
Here the defenition of the types:
TYPE TStringTable IS TABLE OF VARCHAR2(2000);
TYPE TServerReply IS RECORD (
Rpt CHAR, -- Holds symbol: "-" if ftp reply consist of several
-- lines, otherwise equal space
Code VARCHAR2(3), -- Tree digit reply code
Message VARCHAR2(256)); -- Descriptive Message
TYPE TServerReplyA IS TABLE OF TServerReply;
TYPE TConnectInfo IS RECORD (IP VARCHAR2(22),
Port PLS_INTEGER );
This function is most often used in the package, it submits the command over ftp control connection returning back server response. The function takes two input parameters: pointer to a UTL_TCP.connection structure and command. Connection must be already open. Return type is TServerReplyA, which holds array of replies from ftp server.
FUNCTION
WriteCommand( a_conn IN
utl_tcp.connection,
a_command IN VARCHAR2
) RETURN TServerReplyA
IS
v_conn utl_tcp.connection;
v_str VARCHAR2(500);
v_bytes_written NUMBER;
v_reply TServerReplyA;
BEGIN
v_reply:=TServerReplyA();
v_conn:=a_conn;
IF a_command IS NOT NULL THEN
v_bytes_written :=
utl_tcp.write_line(v_conn, a_command);
END IF;
v_conn:=a_conn;
WHILE 1
= 1
LOOP
v_str:=utl_tcp.get_line(v_conn, TRUE);
v_reply.extend;
v_reply(v_reply.count).code:=substr(v_str,1,3);
v_reply(v_reply.count).rpt:=substr(v_str,4,1);
v_reply(v_reply.count).message:=substr(v_str,5);
IF v_reply(v_reply.count).rpt = '
'
THEN
EXIT;
END IF;
END LOOP;
IF substr(v_reply(v_reply.count).code,1,1)
= '5'
THEN
raise_application_error(PERMANENT_NEGATIVE_REPLY, 'WriteCommand:
'||v_str,
TRUE);
END IF;
RETURN v_reply;
END;
If command is not NULL the function submits it to the control connection to ftp server and reads server reply then. Otherwise it just reads server reply. The interesting thing here (see WHILE … LOOP construction) is that we have to check for "-" symbol right after reply code. This does mean ftp server wants to say a "phrase" i.e. set of lines not just one line.
The purpose of this function is to open connection with ftp server and perform user logon, finally function returns valid UTL_TCP.connection structure. The function takes four input parameters: ftp site, ftp port, user name and user password. Return type is connection type defined in package UTL_TCP see (2) for details.
FUNCTION
Login( a_site_in IN VARCHAR2,
a_port_in IN VARCHAR2,
a_user_name IN VARCHAR2,
a_user_pass IN VARCHAR2
) RETURN
utl_tcp.connection
IS
v_conn
utl_tcp.connection;
v_reply
TServerReplyA;
BEGIN
v_conn :=
utl_tcp.open_connection(a_site_in, a_port_in);
v_reply:=WriteCommand(v_conn, NULL);
IF v_reply(v_reply.count).code <> '220'
THEN
utl_tcp.close_all_connections;
raise_application_error(FTP_SERVER_ERROR, 'Login:
'||v_reply(v_reply.count).code ||'
'||
v_reply(v_reply.count).message, TRUE);
RETURN v_conn;
END IF;
v_reply:=WriteCommand(v_conn,'USER
'||a_user_name);
IF substr(v_reply(v_reply.count).code,1,1)
= '5'
THEN
utl_tcp.close_all_connections;
raise_application_error(PERMANENT_NEGATIVE_REPLY, 'Login:
'||v_reply(v_reply.count).code ||'
'||
v_reply(v_reply.count).message, TRUE);
RETURN v_conn;
END IF;
IF v_reply(v_reply.count).code <> '331'
THEN
utl_tcp.close_all_connections;
raise_application_error(FTP_SERVER_ERROR, 'Login:
'||v_reply(v_reply.count).code ||'
'||
v_reply(v_reply.count).message, TRUE);
RETURN v_conn;
END IF;
v_reply:=WriteCommand(v_conn,
'PASS
'||a_user_pass);
IF substr(v_reply(v_reply.count).code,1,1)
= '5'
THEN
utl_tcp.close_all_connections;
raise_application_error(PERMANENT_NEGATIVE_REPLY,
'Login:
'||v_reply(v_reply.count).code ||'
'||
v_reply(v_reply.count).message, TRUE);
RETURN v_conn;
END IF;
IF v_reply(v_reply.count).code <> '230'
THEN
utl_tcp.close_all_connections;
raise_application_error(FTP_SERVER_ERROR, 'Login:
'||v_reply(v_reply.count).code ||'
'||
v_reply(v_reply.count).message, TRUE);
RETURN v_conn;
END IF;
RETURN v_conn;
END;
In first 8 lines of code, the function opens TCP connection with ftp server. It reads server response and if ftp server is not ready it closes all connections and rises exception. If ftp server accepted connection it submits USER command with user name, reads server reply and checks it against different errors, rising corresponding exceptions. If USER command was successful it then submits PASS command with password to ftp server and also performs some simple check for possible server errors. Finally function returns UTL_TCP.connection structure.
The main and only purpose of this function is to instruct ftp server enter in passive mode. The function accepts UTL_TCP.connection type as parameter and returns TConnectInfo structure. When ftp server gets PASV command it should return message which contains IP address and port number where server will "listen" for data connection, the format is "(aaa,bbb,ccc,ddd,p1,p2)" where first four digits denotes IP address and other used to encode port number which is equal 256*p1+p2 see (3) for details.
FUNCTION
GetPassive( a_conn IN
utl_tcp.connection ) RETURN
TConnectInfo
IS
v_reply TServerReplyA;
v_port PLS_INTEGER;
v_port1 VARCHAR2(10);
v_port2 VARCHAR2(10);
v_str VARCHAR2(64);
v_connectinfo TConnectInfo;
BEGIN
IF a_conn.remote_host IS NULL THEN
raise_application_error(NULL_CONNECTION, 'GetPassive:
Can not work with NULL connection',
TRUE);
RETURN NULL;
END IF;
v_reply:=WriteCommand(a_conn,'PASV');
IF v_reply(v_reply.count).code<>'227'
THEN
raise_application_error(FTP_SERVER_ERROR, 'GetPassive:
'||v_reply(v_reply.count).code ||'
'||
v_reply(v_reply.count).message, TRUE);
RETURN NULL;
END IF;
v_str:=replace(rtrim(substr(v_reply(v_reply.count).message,
1+instr(v_reply(v_reply.count).message, '(',1,1)
),').'
),',','.');
v_connectinfo.ip :=substr(v_str,
1,
instr(v_str, '.',
-1,2)-1);
v_str:=substr(v_str, 1+instr(v_str,
'.',
-1,2));
v_port1:=substr(v_str, 1,
instr(v_str,'.',1,1)-1);
v_port2:=substr(v_str, 1+instr(v_str,'.',1,1));
v_connectinfo.port := 256*to_number(v_port1)+to_number(v_port2);
RETURN v_connectinfo;
END;
The first thing we do here (first four lines of code) is checking that we work with opened TCP connection and if we don't the function rises an exception. The remaining code extracts IP address and port number from server reply message, puts it into TConnectInfo variable and return it.
This procedure is used to download text file from ftp server. It accepts connection and remote file name as parameters returning back file contents.
FUNCTION GetTextFile( a_conn IN utl_tcp.connection,
a_remote_file IN VARCHAR2 ) RETURN TStringTable
IS
v_file
TStringTable;
v_dataconn
utl_tcp.connection;
v_reply
TServerReplyA;
v_dataconnectinfo TConnectInfo;
BEGIN
| Ask ftp server listen for data connection by calling GetPassive function. If ip address is null raise an exception. |
v_file := TStringTable();
v_dataconnectinfo :=
GetPassive(a_conn);
IF v_dataconnectinfo.ip IS NULL THEN
raise_application_error(FTP_DEFAULT_DATA_CONN_ERROR, 'GetTextFile:
'||
v_reply(v_reply.count).code
||'
'||
v_reply(v_reply.count).message, TRUE);
END IF;
v_dataconn :=
utl_tcp.open_connection(v_dataconnectinfo.ip, v_dataconnectinfo.port);
| Open data connection and instruct server to start data transfer. If server replies with error raise an exception. |
v_reply:=WriteCommand(a_conn,'RETR '||a_remote_file);
IF substr(v_reply(v_reply.count).code,1,1)='5' THEN
utl_tcp.close_connection(v_dataconn);
raise_application_error(PERMANENT_NEGATIVE_REPLY, 'GetTextFile:'||
v_reply(v_reply.count).code
||'
'||
v_reply(v_reply.count).message, TRUE);
END IF;
IF v_reply(v_reply.count).code<>'150'
THEN
utl_tcp.close_connection(v_dataconn);
raise_application_error(FTP_SERVER_ERROR, 'GetTextFile:
'||
v_reply(v_reply.count).code
||'
'||
v_reply(v_reply.count).message, TRUE);
END IF;
BEGIN
LOOP
| Start file
downloading. Download until exception UTL_TCP.END_OF_INPUT occurs, which
tells us about end of the process.
Ask server about status of the transfer and if it reports an error raise an exception. |
v_file.extend;
v_file(v_file.count) := utl_tcp.get_line(v_dataconn,
TRUE);
END LOOP;
EXCEPTION
WHEN UTL_TCP.END_OF_INPUT THEN
v_file.trim(1);
WHEN OTHERS THEN
END;
v_reply:=WriteCommand(a_conn, NULL);
IF ( substr(v_reply(v_reply.count).code,1,1)
= '4'
OR
substr(v_reply(v_reply.count).code,1,1)
= '5'
)
THEN
utl_tcp.close_connection(v_dataconn);
raise_application_error(FTP_DOWNLOAD_PROBLEM, 'GetTextFile:
'||
v_reply(v_reply.count).code
||'
'||
v_reply(v_reply.count).message, TRUE);
END IF;
utl_tcp.close_connection(v_dataconn);
RETURN v_file;
EXCEPTION
WHEN OTHERS THEN
| Close data connection and return file. |
utl_tcp.close_connection(v_dataconn);
dbms_output.put_line('ERROR:
'||substr(SQLERRM, 1,
80));
END;
The logout procedure does nothing but sends QUIT command to ftp server telling that we want to close our connection.
PROCEDURE
Logout( a_conn IN
utl_tcp.connection)
IS
v_reply TServerReplyA;
BEGIN
v_reply:=WriteCommand(a_conn,'QUIT');
IF v_reply(v_reply.count).code <> '221'
THEN
raise_application_error(FTP_LOGOUT_PROBLEM, 'Logout:
'||v_reply(v_reply.count).code ||'
'||
v_reply(v_reply.count).message, TRUE);
RETURN;
END IF;
RETURN;
END;
Now it is time to put all stuff together and download something from Internet. The "Main" procedure simply runs previously described procedures and functions to download the small file "welcome.msg" from the server ftp.ftp.com.
PROCEDURE
Main
IS
v_reply TServerReplyA;
v_file TStringTable;
v_conn utl_tcp.connection;
BEGIN
v_conn := Login('ftp.ftp.com',
21,
'anonymous',
'dbouzolin@yahoo.com');
v_file := GetTextFile(v_conn,
'pub/support/banner.msg');
Logout(v_conn);
utl_tcp.close_all_connections;
FOR i IN 1
.. v_file.count LOOP
dbms_output.put_line(substr(v_file(i),1,100));
END LOOP;
v_file.delete;
EXCEPTION
WHEN OTHERS THEN
Logout(v_conn);
utl_tcp.close_all_connections;
dbms_output.put_line('ERROR:
'||substr(SQLERRM, 1,
80));
END;
Here the ftp server output from this program. As you can see I downloaded this file, what about you?
SQL> set serverout on
SQL> exec ftp.main;
_____________________________________________________________________
You have reached the Technical Support FTP server at NetManage Inc.
On this server you will find all technical support related issues for
all NetManage's products. If you have any questions or concerns,
Please send them to : ftp@netmanage.com.
_____________________________________________________________________
PL/SQL procedure successfully completed.
This paper shows basic ideas how to work with FTP protocol from an Oracle database, presenting PL/SQL package example, which allows you to download text files right inside your Oracle database. The task of building an Oracle package even with minimal ftp functionality is very complex and challenging, but feasible. In this paper author tried to show the basic approach of the process. Author hopes that Oracle will include ftp support in their next database releases.
Dmitry Bouzolin (dbouzolin@yahoo.com) is an Oracle Certified Professional. At present time he works as Senior Consultant in GRT INC. He has several successfully completed projects where he took part in as Oracle DBA, PL/SQL developer and project manager.
1. PL/SQL User's Guide and Reference, Release 8.1.7
2. Oracle8i Supplied PL/SQL Packages Reference, Release 8.1.7
3. FILE TRANSFER PROTOCOL (FTP) (ftp://ftp.isi.edu/in-notes/rfc959.txt)
