oracle没有ftp包,Oracle 自带包中有没有类似于ftp的函数？

How to perform FTP operations from within PL/SQL (part I)

Introduction

There are times where it would be useful to be able to move files across systems from within a PL/SQL program. The usual tool for doing this is the ftp command. There are at least 4 ways to realize this.

The first one is to interface the O/S ftp command to the database so it can be used from within PL/SQL. An implementation of this alternative will likely use an external procedure to spawn the program. Actually, such a solution already exists, see Note:111780.1. Another implementation will be found in a future article.

The second one is to grab a library offering an ftp client API callable from C. A quick search on the Web brought back several hits, among which FCE4C "a library of functions providing direct and simple control of the FTP protocol". This is a shareware available from http://www.marshallsoft.com/fce4c.htm.

The third way is to use the functions in PL/SQL packages utl_tcp and utl_http, either to realize ad hoc file retrieve/put functionality or to (re-)implement the ftp protocol. This can be a fun project but requires some insight of its innards as described in the RFC 959 document, far too more than an application programmer cares to know about.

The fourth one is to publish the java class FtpClient from the package sun.net.ftp, contained in the JDK�s rt.jar library. This solution is similar to the second one but for java. While the first is more universal because it is based on the external procedure mechanism existing since Oracle v8, the fourth one is cleaner because it relies on the built-in JVM that exists since Oracle v8i, and is therefore an internal solution. There is one catch though: as it uses the Sun's package sun.net.ftp, which is not part of the JDK and is not supported by Sun, there is no warranty nor official documentation. However, it is easy to use and seems to work fine so far � minus one small glitch, see below � and there are free alternative implementations of it in source form on the Internet. See http://www.enterprisedt.com/downloads/ftp.html for a FTPClient implementation, and http://www.enterprisedt.com/downloads/ftp/doc/index.html for its documentation.

In this 3-part article, I have chosen the fourth alternative. I am presenting its implementation here and will provide the code listing in Note.159061.1 and Note.159062.1.

Interfacing to FtpClient

This java class belongs to the Sun�s package sun.net.ftp. It conveniently exports the following main methods: openServer() and closeServer(), login() (log in onto a given host), get() (retrieve a list of remote files), put() (store a list of files to the remote host), list() (get the current directory�s list of entries), binary() (set file transfer to binary), and ascii() (set file transfer to ASCII), and cd() (change to directory). As no mget() is exported by the class, this function is not available, though it could relatively easily be implemented using list() and get().

As usual with java in the database, the FtpClient class will be invoked through static public methods published as stored procedures (JSP). Because of this, a stateless java interface has to be written, which one can take profit of to make it more synthetic and comfortable. Here are the retained public methods:

static public String FTPGet(String ServerName,

String UserName,

String Password,

String SourceFileList,

String FileTypeIsBinaryList,

String DestFileList,

String ListSeparator);

static public String FTPGet(String ServerName,

String UserName,

String Password,

String[] SourceFiles,

boolean[] FileTypeIsBinary,

String[] DestFiles);

static public String FTPPut(String ServerName,

String UserName,

String Password,

String SourceFileList,

String FileTypeIsBinaryList,

String DestFileList,

String ListSeparator);

static public String FTPPut(String ServerName,

String UserName,

String Password,

String[] SourceFiles,

boolean[] FileTypeIsBinary,

String[] DestFiles);

static public String FTPDir(String ServerName,

String UserName,

String Password,

String RemoteDir);

The functions return a string which is the eventual error message, or an empty string if no error occurred.

FTPGet() and FTPPut() functions are overloaded because PL/SQL index-by tables cannot currently be passed to java (an error "PLS-00999: implementation restriction (may be temporary) INDEX TABLE parameters are disallowed" is issued). Therefore, lists of files to put or get are passed as strings containing file names separated by a given separator string. On the java side however, it is more natural to resort to array of file names as collection, so a higher-level profile is also given.

The function�s profile is quite intuitive and does not need any explanation except parameter FileTypeIsBinaryList. This is a string of ListSeparator-separated "T" or "F" characters which respectively stand for TRUE and FALSE. This is needed because PL/SQL BOOLEAN type is not mappable to java boolean.

To be used as JSP, the above static methods need to be interfaced to PL/SQL through PL/SQL functions, which do not need to be public. Here is the profile of the publicly accessible functions:

FUNCTION FTPGet(ServerName STRING,

UserName STRING,

Password STRING,

SourceFiles STRING_TABLE,

FileTypeIsBinary BOOLEAN_TABLE,

DestFiles STRING_TABLE) RETURN STRING;

FUNCTION FTPPut(ServerName STRING,

UserName STRING,

Password STRING,

SourceFiles STRING_TABLE,

FileTypeIsBinary BOOLEAN_TABLE,

DestFiles STRING_TABLE) RETURN STRING;

FUNCTION FTPDir(ServerName IN STRING,

UserName IN STRING,

Password IN STRING,

RemoteDir IN STRING,

DirList OUT STRING_TABLE) RETURN STRING;

FUNCTION FTPDir(ServerName IN STRING,

UserName IN STRING,

Password IN STRING,

RemoteDir IN STRING) RETURN STRING;

The profiles are closely related to their java counterparts.

Function FTPGet() connects to the remote machine whose host name is in ServerName using user account UserName/Password. If successful, it attempts to get the files whose names are in the PL/SQL index-by table of strings SourceFiles, and whose type (binary or ASCII) is contained in the PL/SQL index-by table of booleans FileTypeIsBinary (entry at index i is TRUE iff SourceFiles(i) is binary). The retrieved files are stored in the PL/SQL index-by table of strings DestFiles.

Index-by tables were chosen as collections here because they support the PL/SQL BOOLEAN data type and despite they don�t have constructors (each item in the table must be individually assigned, this cannot be done globally in one shot). Alternatively, higher-level nested tables of user-defined types could be used here but such types must be declared and defined in the database outside of PL/SQL packages, and they do not support the BOOLEAN data type. They have a constructor though which makes it nicer to initializes nested table literals. The reader is free to choose the interface s?he bests sees fit.

FTPPut() works similarly, excepted that it copies the files passed in SourceFiles onto the remote host as files whose names are in DestFiles.

SourceFiles and DestFiles must obey the file name syntax of their respective host platforms. E.g. when getting files from a Unix host into a PC host running NT, SourceFiles must be a �/� delimited list of sub-path names whereas DestFiles must be a �\� delimited list of sub-path names with an optional starting drive letter�:� sequence.

As PL/SQL index-by tables can be sparse, there is no guarantee that their first element is at index 0 or even 1. Therefore, instead of coercing the user to some arbitrary starting index or to dense tables only, I leave up to (him)|(her) this choice. Internally, functions Table.FIRST, Table.LAST, Table.EXISTS(index) are used to accommodate that freedom, which only slightly complicates the code.

FTPDir() exists in 2 flavors. The first version gets a directory listing of the files in the directory RemoteDir and returns it in raw format, i.e. the same as the one returned by the ftp "dir" command. Each line contains a file name preceded by its permissions, owner name, group name, size, and last access date. All the linefeed-separated lines are concatenated together into one string. The caller is responsible for parsing it and extracting the information of interest. The second flavor partially parses the raw output and returns it into a PL/SQL index-by table, one file entry per table element.

The functions return an error message if an error occurs, or an empty string if all the files could be processed correctly. If an error occurs, the current file plus the rest of the files are discarded and the functions return immediately. Programmatically, this was the easiest thing to do and justifies itself by the fact that a transfer error usually means a permission issue, a disk full or a network problem, which jeopardizes all subsequent file transfer as well so that an abort is the most sensible thing to do here.

Permission considerations

As resources such as TCP/IP sockets and files are being accessed by java functions, special permissions are required. Firstly, if the remote host is accessed through its hostname, a DNS must be queried to get the host�s IP address. This requires the resolve permission. Secondly, sockets operations such as accept(), listen(), and connect() also demand adequate permissions. Thirdly, files accessed remotely to be copied locally require the write permission and files accessed locally to be copied remotely require the read permission. All those permissions must be granted to the calling user through the function dbms_java.grant_permission().

Examples of use

In this example, all the permission granting stuff is done automatically since the running user name is grabbed from the session�s environment and the file names to be granted permission on are obviously known at run-time.

As error messages and the directory listing must be printed by the SQL*PLUS environment (see below why), host string variables are used.

CONNECT scott/tiger

SET SERVEROUTPUT ON

exec dbms_java.set_output(5000);

VAR DirFiles VARCHAR2(4000);

VAR ErrorMessage VARCHAR2(4000);

DECLARE

S FTP.STRING_TABLE;

B FTP.BOOLEAN_TABLE;

D FTP.STRING_TABLE;

DirList FTP.STRING_TABLE;

DummyKey NUMBER;

I NUMBER;

BEGIN

-- grant required socket permissions;

dbms_java.grant_permission(USER,

'java.net.SocketPermission',

'*:*',

'accept,listen,connect,resolve',

DummyKey);

-- get list of files;

:ErrorMessage := FTP.FTPDir(ServerName => 'chaos7a.ch.oracle.com',

UserName => 'osupport',

Password => 'password',

RemoteDir => '/usr/users/osupport',

DirList =>

irFiles);

:ErrorMessage := FTP.FTPDir(ServerName  => 'chaos7a.ch.oracle.com',

UserName    => 'osupport',

Password => 'password',

RemoteDir   => '/usr/users/osupport/ccervini',

DirList     => DirList);

FOR I IN 1 .. DirList.COUNT LOOP

dbms_output.put_line(DirList(I));

END LOOP;

-- get files;

S(1) := '/export/home/osupport/ccervini/1-65.pdf';

B(1) := true;

D(1) := '/u02/home/usupport/ccervini/1.pdf';

S(2) := '/export/home/osupport/ccervini/KO11.xls';

B(2) := true;

D(2) := '/u02/home/usupport/ccervini/3.xls';

S(3) := '/export/home/osupport/ccervini/Anatomy_of_an_Asp.pdf';

B(3) := true;

D(3) := '/u02/home/usupport/ccervini/2.pdf';

-- test sparse table here;

S(10) := '/export/home/osupport/ccervini/ODCI/JSSIndex.java';

B(10) := false;

D(10) := '/u02/home/usupport/ccervini/4.java';

FOR I IN D.FIRST .. D.LAST LOOP

IF D.EXISTS(I) THEN

dbms_java.grant_permission(USER,

'java.io.FilePermission size=+1>                                    D(I),

'write');

END IF;

END LOOP;

:ErrorMessage := FTP.FTPGet(ServerName => 'chsuncl1.ch.oracle.com',

UserName => 'osupport',

Password => 'password',

SourceFiles => S,

FileTypeIsBinary => B,

DestFiles => D);

-- put files;

S(1) := '/u02/home/usupport/ccervini/RegularExpressions.c';

B(1) := false;

D(1) := '/usr/users/osupport/100.c';

S(2) := '/u02/home/usupport/ccervini/analyze.sql';

B(2) := false;

D(2) := '/usr/users/osupport/101.sql';

S(-10) := '/u02/home/usupport/core';

B(-10) := true;

D(-10) := '/usr/users/osupport/ccervini/102';

FOR I IN S.FIRST .. S.LAST LOOP

IF S.EXISTS(I) THEN

dbms_java.grant_permission('SCOTT',

'java.io.FilePermission size=+1>                                    S(I),

'read');

END IF;

END LOOP;

:ErrorMessage := FTP.FTPPut(ServerName => 'chaos7a.ch.oracle.com',

UserName => 'osupport',

Password => 'password',

SourceFiles => S,

FileTypeIsBinary => B,

DestFiles => D);

END;

/

print DirFiles

print ErrorMessage

In the above demonstration block, the error message is returned into a SQL*PLUS host variable. This is because it was not always possible to use the dbms_output.put_line() function as the string often was too large (larger than 255 characters). Thus, this output task is left to the SQL*PLUS client. There is a java implementation of put_line() that works around those limits. Please see Note:136486.1.

The reader is invited to play with different combinations of host O/S, account and file status (existing/non existing, binary/ASCII, existing/non existing user, correct/wrong password) to see which error messages are returned.

A small glitch in Sun's class FtpClient

As is, method FtpClient.get() does not handle well multiple file cases, and fails with a java.lang.NullPointerException error after the first file was successfully retrieved.

The bug is on the last line of FtpClient.get(), i.e. instead of

return new FtpInputStream(this,

socket.getInputStream(),

binaryMode);

it should read:

return new TelnetInputStream(socket.getInputStream(),

binaryMode);

Once this change is made and the whole class recompiled and reloaded, the method works as expected.

The original code can be gotten from the following URL:

http://www.sourcebot.com/sourceb ... t/ftp/FtpClient.cla size=+1>References

For the full code of the java and PL/SQL interfaces, please check Note.159061.1 and Note.159062.1.

For an alternative implementation of FTPClient, see source code and documentation here:

http://www.enterprisedt.com/downloads/ftp.html

http://www.enterprisedt.com/downloads/ftp/doc/index.html