Oracle java存储过程从FTP检索文件

嗨，汤姆，

我已经制作了从FTP检索文件的java存储过程。java程序在本地系统上运行良好，但在数据库上它挂起并且无法正常工作，我应该停止OracleService来停止它。

下面是我为制作java存储过程所做的工作。

1. Load JAR file for FTP(apache-jakarta-oro.jar，commons-net-1.4.1.jar)

loadjava -u gilbert/password -resolve -schema GILBERT C:\apache-jakarta-oro.jar
loadjava -u gilbert/password -resolve -schema GILBERT C:\commons-net-1.4.1.jar

2. Create java source

create or replace 
and compile java source named "FtpTransfer"
as 
 
import java.io.*; 
import java.net.*; 
import org.apache.commons.net.ftp.*; 
 
public class FtpTransfer 
{ 
    public static void ftpTransfer()
    {    
        FTPClient ftp = null;
        try {
            ftp = new FTPClient();
            ftp.setControlEncoding("UTF-8");
            ftp.connect("SERVERNAME", PORTNUMBER);
            ftp.login("ID", "PASSWORD");
            ftp.changeWorkingDirectory("/test_dir");
 
            File f = new File("C:/TEST", "test.txt");
            FileOutputStream fos = null;
 
            try {
                fos = new FileOutputStream(f);
                boolean isSuccess = ftp.retrieveFile("test.txt", fos); //ftp.retrieveFile makes stop!!!!
            } catch(IOException ex) {
                System.out.println(ex.getMessage());
            }             
            finally {
                if (fos != null) {
                    try { fos.close(); } 
                    catch(IOException ex) {System.out.println(ex.getMessage());}
                }
            }
            
            ftp.logout();
            
        } catch (SocketException e) {
            System.out.println("Socket:"+e.getMessage());
        } catch (IOException e) {
            System.out.println("IO:"+e.getMessage());
        }
    }
}

3. Wrappers for the java

create or replace
procedure p_ftpTransfer
as language java
name 'FtpTransfer.ftpTransfer()';

4. Run the procedure from SQL Developer(版本-4.2.0.17.089)

begin
p_ftpTransfer(); --it hangs and should stop oracle service!!
end;

5. The below is the permissin list(仅FilePermission和SocketPermission)

KIND GRANTEE_NAME TYPE_SCHEMA TYPE_NAME NAME ACTION
GRANT JAVASYSPRIV SYS java.io.FilePermission <> read,write,execute,delete
GRANT JAVAUSERPRIV SYS java.io.FilePermission <> read
GRANT GILBERT SYS java.io.FilePermission C:\TEST read, write, delete, execute
GRANT JAVAUSERPRIV SYS java.net.SocketPermission * connect,resolve
GRANT JAVASYSPRIV SYS java.net.SocketPermission * accept,connect,listen,resolve
GRANT GILBERT SYS java.net.SocketPermission * connect,resolve
GRANT GILBERT SYS java.net.SocketPermission 220.93.76.37:1024- listen,resolve

也许试试这个-我过去用过这个，它似乎对我来说很好。

SQL> create or replace
  2  and compile java source named SCOTT."FtpDownload"
  3  as
  4  import java.io.*;
  5  import java.net.*;
  6
  7  public class FtpDownload {
  8
  9  private static final int BUFFER_SIZE = 4096;
 10
 11  public static void ftpTransfer() {
 12      String ftpUrl = "ftp://%s:%s@%s/%s;type=i";
 13      String host = "192.168.1.10";
 14      String user = "hamcdc";
 15      String pass = "mookster";
 16      String localPath = "C:/TEMP/archives.txt";
 17      String remotePath = "/ftp_upload.txt";
 18
 19      ftpUrl = String.format(ftpUrl, user, pass, host, remotePath);
 20      System.out.println("URL: " + ftpUrl);
 21
 22      try {
 23              URL url = new URL(ftpUrl);
 24              URLConnection conn = url.openConnection();
 25              InputStream inputStream = conn.getInputStream();
 26
 27              FileOutputStream outputStream = new FileOutputStream(localPath);
 28
 29              byte[] buffer = new byte[BUFFER_SIZE];
 30              int bytesRead = -1;
 31              while ((bytesRead = inputStream.read(buffer)) != -1) {
 32                  outputStream.write(buffer, 0, bytesRead);
 33              }
 34
 35              outputStream.close();
 36              inputStream.close();
 37
 38              System.out.println("File downloaded");
 39      } catch (IOException ex) {
 40          ex.printStackTrace();
 41      }
 42  }
 43  }
 44  /

Java created.

SQL> create or replace
  2  procedure scott.p_FtpDownload
  3  as language java
  4  name 'FtpDownload.ftpTransfer()';
  5  /

Procedure created.

SQL>
SQL> exec scott.p_FtpDownload();

PL/SQL procedure successfully completed.