Hi Tom,

I've made java stored procedure that retrives file from FTP. The java program works well on local system but on database it hangs and not working and I should stop the OracleService to stop it.

The below is what I did for making the java stored procedure.

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(version-4.2.0.17.089)

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

5. The below is the permissin list (only FilePermission and SocketPermission)

KIND GRANTEE_NAME TYPE_SCHEMA TYPE_NAME NAME ACTION
GRANT JAVASYSPRIV SYS java.io.FilePermission <<ALL FILES>> read,write,execute,delete
GRANT JAVAUSERPRIV SYS java.io.FilePermission <<ALL FILES>> 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.