30. Juni 2008

Einfacher FTP Client mit PL/SQL

English title: Simple PL/SQL FTP Client

Wusstet Ihr, dass man mit der Datenbank auch recht einfach auf einen FTP-Server zugreifen kann ...? Die Java-Engine in der Datenbank macht es möglich. Probiert mal das Skript hier aus ...
Did you know that you can also access an FTP server from within the database using SQL or PL/SQL? The databases' java engine makes it possible. Just try this script:
create or replace java source named ftp as 
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.*;
import java.net.*;
import java.io.*;

public class SimpleFtp {
  public static BLOB download(String psUrl) throws Exception {
    URL url = new URL(psUrl + ";type=i");
    URLConnection ucon = url.openConnection();
    BufferedInputStream in = new BufferedInputStream(ucon.getInputStream());

    Connection con = DriverManager.getConnection("jdbc:default:connection:");
    BLOB blob = BLOB.createTemporary(con, true, BLOB.DURATION_SESSION); 
    OutputStream out = blob.getBinaryOutputStream();
    int i = 0;
    byte[] bytesIn = new byte[blob.getChunkSize()];
    while ((i = in.read(bytesIn)) >= 0) {
      out.write(bytesIn, 0, i);
    }
    out.close();
    in.close();
    con.close();
    return blob;
  }

  public static void upload(String psUrl, BLOB blob) throws Exception {
    URL url = new URL(psUrl + ";type=i");
    URLConnection ucon = url.openConnection();
    BufferedInputStream in = new BufferedInputStream(blob.getBinaryStream(0L));

    OutputStream out = ucon.getOutputStream();
    int i = 0;
    byte[] bytesIn = new byte[blob.getChunkSize()];
    while ((i = in.read(bytesIn)) >= 0) {
      out.write(bytesIn, 0, i);
    }
    out.close();
    in.close();
  }
}
/
sho err


alter java source ftp compile
/
sho err

create or replace package simple_ftp is
 function ftp_download(p_url in varchar2) return blob;
 procedure ftp_upload(p_url in varchar2, p_blob in blob);
end simple_ftp;
/ 
sho err

create or replace package body simple_ftp is
 function ftp_download(p_url in varchar2) return blob
 as language java name 'SimpleFtp.download(java.lang.String) return oracle.sql.BLOB';
 procedure ftp_upload(p_url in varchar2, p_blob in blob)
 as language java name 'SimpleFtp.upload(java.lang.String, oracle.sql.BLOB)';
end simple_ftp;
/ 
sho err
Ihr bekommt mit dem Paket SIMPLE_FTP einen stark vereinfachten FTP-Client, mit dem Ihr Dateien von einem FTP-Server als BLOB herunterladen oder einen BLOB auf einen Server hochladen könnt. Mehr kann er nicht - Folder anlegen, Dateien löschen oder ähnliche Dinge gehen nicht.
The script provides the PL/SQL package SIMPLE_FTP, a very simple ftp client. You can now upload a BLOB to an FTP server or download a file as BLOB with simple calls to this package. This client can just up- and download files. Further FTP actions like renaming files, creating folders or similar are not possible.
Normalerweise fehlen euch noch die nötigen Java-Privilegien, die es euch erlauben, über das Netz auf einen fremden Server zuzugreifen. Entweder gebt Ihr eurem DB-User die Rolle JAVASYSPRIV (sehr mächtig) oder Ihr vergebt die Zugriffsrechte auf die FTP-Server feingranular; was ich empfehlen würde.
If you are not the DBA your DB user normally has not the appropriate privileges in order to perform the network connection to the FTP server. Either you grant the JAVASYSPRIV role (very powerful) or grained prilileges just to connect to a particular FTP server to your DB user. I'd recommend the latter approach.
call  dbms_java.grant_permission( '[DB-User]', 'SYS:java.net.SocketPermission', '[host name]', 'resolve' );
call  dbms_java.grant_permission( '[DB-User]', 'SYS:java.net.SocketPermission', '[IP address]', 'connect, resolve' );
Nun testen: Hochladen ...
Now testit: Upload a file ...
SQL> exec simple_ftp.ftp_upload('ftp://ftpserver.mydomain.de/path/to/target-file', [blob]);
Herunterladen ...
... and download it ...
SQL> select simple_ftp.ftp_download('ftp://ftpserver.mydomain.de/path/to/target-file') from dual;

SIMPLE_FTP.FTP_DOWNLOAD('FTP://FTPSERVER.MYDOMAIN.DE/PATH/TO/TARGET-FILE')
--------------------------------------------------------------------------------
2020202076617220675F636F6C5769647468203D2033303B0A2020202076617220675F6B65796D61
70456E7472696573203D206E756C6C3B0A2020202076617220675F6D65737361676573203D206E65

1 row selected.
Mit Login geht's auch ... einfach die URL wie folgt angeben: ftp://username:password@host:port/rest/der/url. Aber bei FTP und Passwörtern immer dran denken: Sie werden stets im Klartext übertragen.
Logging into the FTP server using a username and password is also possible - just provide the credentials within the URL as follows: ftp://username:password@host:port/rest/of/the/url. But regarding FTP and passwords keep in mind: they're always transmitted in clear text.

Kommentare:

Patrick Wolf hat gesagt…

Wieder mal eine nette und effektive Verwendung von Java in der Datenbank. Fuer OracleXE Benutzer gibt es auch eine FTP Package welches nicht Java benoetigt, zu finden am AMIS Blog.

Lg
Patrick

Anonym hat gesagt…

Sehr nett, jedoch habe ich noch Probleme, eine Datei downzuloaden und anschließend zu verarbeiten (konkret will ich eine CSV-Datei downloaden und weiterverarbeiten)

Leider wird darauf nicht eingegangen, sondern nur eine Ausgabe erzeugt :/

Carsten Czarski hat gesagt…

Hallo,

nein - denn dieses Blog-Posting kümmert sich in der Tat nur um den FTP-Transport. Wenn das eine CSV-Datei ist, könnte man diese zunächst mit DBMS_LOB.CONVERTBLOBTOCLOB in einen CLOB umwandeln und dann mit PL/SQL parsen und zerlegen. U.U. hilft der "String Tokenizer für PL/SQL" noch weiter ... http://sql-plsql-de.blogspot.com/2008/09/string-tokenizer-fr-plsql.html

Welche Probleme bestehen denn genau?

Grüße

-Carsten

Anonym hat gesagt…

Ja Ihren String-Tokenizer habe ich schon gesehen und werde wohl versuchen das ganze mit den beiden Skripten zu realisieren.

Das größte Problem ist wohl meine bisher sehr geringe Erfahrung mit PL/SQL ;)

Beliebte Postings