SFTP from Oracle

So, the goal: upload/download files from Oracle server to/from SFTP server.
Constraints: the operation must be able to be triggered from within pl/sql by an Oracle job.

As far as I know by now there are no means to implement this functionality in pure pl/sql.

However there is a number of Java libraries which provide required API. Below is an example of unilizing Jsch library.

There four main steps required:

1. Install Jsch library into Oracle


2. Create your API for aforesaid functionality and install it to Oracle


3. Create pl/sql wrappers for your API


4. Grant java permissions


5. Enjoy results

Step 1. Install Jsch library into Oracle

Download Jsch library from here (version jsch-0.1.42.jar). At the time of reading this post the version may have been changed and the link been broken. Anyway you must be able to find downloadable jar here.

Install the downloaded jar into Oracle following these guidelines here.

Make sure that installed classes are valid. You can do it by examining result of the following query:

SELECT object_name, status FROM user_objects WHERE object_name LIKE 'com/jcraft/%';

In my particular case I had to recompile the classes to have their status VALID. Below is an example of recompilation statement for class com/jcraft/jsch/Buffer:

alter java class "com/jcraft/jsch/Buffer" resolve;

See details on alter java statement here (amidst other locations of course).

Step 2. Create your API for aforesaid functionality and install it to Oracle

Create a Java project in your preferred IDE (Eclipse, Netbeans, JDeveloper etc.) and add to it the downloaded jar.

Download Jsch source code and examples from here. If this link is broken you can find the correct link here.

In examples find Sftp.java and put it in your project.

Refactor Sftp.java so that you have two static public classes for get and put operations.


Of course this is easer to say than to do. Below you will find the result of my prototyping efforts. You can use this as a starting point.

But if you wish to do it all by yourself you could be interested in knowing what difficulties I faced. First of all, no Jsch java doc exist. Jsch documentation is its source code itself. However, in my particular case, Jsch classes (in the downloaded jar) were compiled in the manner precluding from examining variables at debug time. So I had to throw Jsch jar out of my java project and include source files into it.

The next point is that Sftp.java has generically interactive architecture. That is, it requres User intervention and even imports visual library (swing, awt). Meanwihle, remember the uploads/download will be triggered by jobs and consequently can not interact with user.

Thus, every dialog must by abandoned. Fortunately, most dialogs merely prompts for password and other connection details which we instead can preconfigure.

One dialog however is worth some extra words: user's acceptance of host key. This dialog is triggered when the server's public key Jsch knows does not coincide with actual server's public key. So if we replace the dialog by constant negative answer the connection will fail when the keys do not coinside.

Jsch keeps known keys in so called known hosts file. In this file Jsch keeps host urls and correspondent keys. The path to the file can by set by (for example) method jsch.setKnownHosts.

To make the keys coinside Oracle administrator has to have a tools allowing identification of keys and saving them into the faile. However, this task goes beyond the subject of this page. Possibly, I will return to this issue afterwords.

And one more thing not to forget. Exit channels and sessions if you do not want to restart Oracle database :)

In my my particular case the class looks like this:

package org.ssh;

import java.io.BufferedWriter;
import java.io.FileWriter;
import java.io.IOException;

import com.jcraft.jsch.Channel;
import com.jcraft.jsch.ChannelSftp;
import com.jcraft.jsch.JSch;
import com.jcraft.jsch.JSchException;
import com.jcraft.jsch.Session;
import com.jcraft.jsch.SftpException;
import com.jcraft.jsch.UserInfo;

public class Sftp {
private static final String PASS = "test";
private static final String KNOWN_HOSTS = "C:\\Users\\Mike\\Documents\\KNOWN_HOSTS";

private static ChannelSftp getChannel() throws JSchException {
JSch jsch = new JSch();
jsch.setKnownHosts(KNOWN_HOSTS);
final String user = "me";
final String host = "localhost";
final int port = 22;
Session session = jsch.getSession(user, host, port);
session.setPassword(PASS);
UserInfo ui = new MyUserInfo();
session.setUserInfo(ui);
session.connect();
Channel channel = session.openChannel("sftp");
channel.connect();
return (ChannelSftp) channel;
}

private static void finalizeConnection(ChannelSftp channel, Session session) {
channel.exit();
session.disconnect();
}

public static void main(String[] arg) {
// get("put_by_me11.txt", "c:\\ftp\\big_to_upload111.txt");
writeFile("adasf");

}

public static class MyUserInfo implements UserInfo {
public String getPassword() {
return passwd;
}

public boolean promptYesNo(String str) {
// TODO: enter sane code
return true;
}

String passwd;

public String getPassphrase() {
return null;
}

public boolean promptPassphrase(String message) {
return true;
}

public boolean promptPassword(String message) {
throw new UnsupportedOperationException();
}

public void showMessage(String message) {
throw new UnsupportedOperationException("No dialogs suppported");
}
}

public static String get(String src, String dst) {
return doCommand("get", src, dst);
}

public static String doCommand(String command, String src, String dst) {
String errMsg = null;
ChannelSftp c = null;
Session s = null;
try {
c = getChannel();
s = c.getSession();
if (command.equals("get")) {
c.get(src, dst);
} else {
c.put(src, dst);
}
} catch (SftpException e) {
errMsg = e.toString();
System.out.println(e.toString());
} catch (JSchException e) {
errMsg = e.toString();
System.out.println(e.toString());
} finally {
finalizeConnection(c, s);
}
return errMsg == null ? "Ok" : errMsg;
}

public static String put(String src, String dst) {
return doCommand("put", src, dst);
}

public static void writeFile(String s) {
try {
BufferedWriter out = new BufferedWriter(new FileWriter(
"c:\\test.txt"));
out.write(s);
out.close();

} catch (IOException e) {
}
}
}



The above implementation is only part of prototyping efforts and therefore can not be used as is.

KNOWN_HOSTS constant, in the above source, is a path to a file which contains list of known hosts where each known host entry has a host url together with the host public key(s). You must know Jsch methods setKnownHosts accepts only stream or file but not just host url for example.

Now, that we have the API, we have to install it to Oracle. You can do it again with loadjava or compile the java source directly in Sqlplus. The latter solution is preferrable if you expect future modification of your API. This is because, in contrast with loadjava, sqlplus requires only oracle ordinary staff to be accomplished (you do not have to copy files with your java source to Oracle server).

Below is the statement to install and compile Java source into Oracle from within Sqlplus (or PlsqlDeveloper or TOAD):

create or replace and compile java source named mike_sftp as
JAVA_SOURCE
;


In the statement above the JAVA_SOURCE is actually the full code of the Sftp class exposed in this blog page too. I replaced it here to keep this page brief (while this may not be possible :) ).

Step 3. Create pl/sql wrappers for your API

This is quite easy step :). The code is below:

CREATE OR REPLACE FUNCTION mike_java_put(src VARCHAR, dst VARCHAR) RETURN VARCHAR AS
LANGUAGE JAVA NAME 'org.ssh.Sftp.put(java.lang.String, java.lang.String) return java.lang.String';

Step 4. Grant java permissions.
This is the step required for GET operation. One has to know that for for Java installed in oracle one have grant permissions to write files. In my case the code was:

BEGIN
dbms_java.grant_permission('SYSTEM', 'SYS:java.net.SocketPermission', 'localhost', 'resolve');
dbms_java.grant_permission('SYSTEM', 'SYS:java.io.FilePermission', '<<all>>', 'write');
END;

If you are lacking file write permissions it is quite possible that you will not obtain cleare error message from Jsch. This is because the library can not handle gracely all huge number of various errors which do not deal with SHH. Therefore, if your make your put command function correctly and the get command does not function with no clear error message then it is probably a security issue and you should grant some additional permissions.

Step 4 Enjoy your job ;)

DECLARE
s_result VARCHAR(100);
BEGIN
s_result := mike_java_get('to_download_by_oracle.txt', 'downloaded_by_oracle.txt');
dbms_output.put_line(s_result);
END;