Connecting to SFTP server from PL/SQL

Table of Contents

Connecting

The first thing to do when initialing SFTP communication session is connecting to the remote machine. Connecting is done with ORA_SFTP.CONNECT_HOST.

The CONNECT_HOST method returns a connection identifier (PL/SQL datatype NUMBER) which is required by all the other functions of the package.

Connecting with username and password

In the most simple scenario, a connection is authenticated with a username and password:

1
2
3
4
5
6
7
8
9
10
11
DECLARE 
 connection_id NUMBER;
 remote_ssh_port NUMBER := 22;
 username VARCHAR2(200);
 password VARCHAR2(200); 
BEGIN
 username:= 'myuser'; 
 password:= 'mypass';
 connection_id := ORA_SFTP.CONNECT_HOST('localhost', remote_ssh_port, username, password);END;
/

Connecting with a username and SSH private key.

The more secure connection option is when the connection is signed with a private key and a corresponding public key is already installed on the SFTP server instance.

The SSH private key must be located in a BLOB field.  A full PL/SQL example can be found in [orasftp.zip]/Examples/CONNECT_WITH_SSH_KEY.sql script

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
DECLARE 
 connection_id NUMBER; 
 private_key BLOB; 
 private_key_handle BFILE;
 PRIVATE_KEY_PASSWORD VARCHAR2(500); 
BEGIN
 -- load the key data into private_key 
 DBMS_LOB.createtemporary(PRIVATE_KEY, TRUE);
 private_key_handle := BFILENAME('MY_KEYS_DIR', 'test_putty_private.ppk'); -- directory name must be Upper case
 DBMS_LOB.OPEN(private_key_handle, DBMS_LOB.LOB_READONLY);
 DBMS_LOB.LoadFromFile( private_key, private_key_handle,  DBMS_LOB.GETLENGTH(private_key_handle) );
 
 PRIVATE_KEY_PASSWORD := 'changeit';
 
 connection_id := ORA_SFTP.CONNECT_HOST('sftp host name or IP', 22, 'my sftp user name', private_key, private_key_password); -- Do SFTP stuff here ...
 ORA_SFTP.DISCONNECT_HOST(connection_id);
END;
/

Connecting with both password and SSH private key.

Although not very common it is possible for an SFTP server to be configured to require both username/password and private key for authentication. In order to establish a connection, we must use a dedicated method CONNECT_WITH_KEY_AND_PASSWORD

A full PL/SQL example can be found in [orasftp.zip]/Examples/CONNECT_WITH_SSH_KEY_AND_PASSWORD.sql script

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
DECLARE 
 connection_id NUMBER; 
 private_key BLOB; 
 private_key_handle BFILE;
 private_key_password VARCHAR2(500); 
BEGIN
 -- load the key data into private_key 
 DBMS_LOB.createtemporary(PRIVATE_KEY, TRUE);
 private_key_handle := BFILENAME('MY_KEYS_DIR', 'test_putty_private.ppk'); -- directory name must be Upper case
 DBMS_LOB.OPEN(private_key_handle, DBMS_LOB.LOB_READONLY);
 DBMS_LOB.LoadFromFile( private_key, private_key_handle,  DBMS_LOB.GETLENGTH(private_key_handle) );
 
 private_key_password := 'changeit';
 
 connection_id := ORA_SFTP.CONNECT_WITH_KEY_AND_PASSWORD('host', 22, 'user', 'password', private_key, private_key_password); -- Do SFTP stuff here ...
 ORA_SFTP.DISCONNECT_HOST(connection_id);
END;
/

HTTP Proxy support

The method used for connecting through HTTP proxy is ORA_SFTP.CONNECT_HOST_PROXY. It expects the same key or password parameters as the CONNECT_HOST method and an additional proxy parameter of type ORA_SFTP_HTTP_PROXY.

Below is an example code block where the ORA_SFTP_HTTP_PROXY structure is initialized. We have to set Host, IP address, username/password, and additional HTTP headers for the HTTP proxy server connection, like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
DECLARE 
  connection_id NUMBER;
  proxy ORA_SFTP_HTTP_PROXY;
 
  private_key_handle BFILE;
  private_key BLOB;  
  private_key_password VARCHAR2(500);  
BEGIN    ... -- load key
 
    -- OK, this is just a test, we run a local HTTP Proxy on port 8000 and our test SFTP server is on port 22
    -- in a real world scenario your Proxy and SFTP servers probably won't be on the same machine
    proxy := ORA_SFTP_HTTP_PROXY(Address => '127.0.0.1', Port => 8000, Username => NULL, Password => NULL, AdditionalHeaders => NULL);
 
    connection_id := ORA_SFTP.CONNECT_HOST_PROXY('localhost', 22, 'nasko', private_key, '', proxy => proxy);
    ORA_SFTP.DISCONNECT_HOST(connection_id);
END;
/

The Username and Password fields of ORA_SFTP_HTTP_PROXY must be filled if the HTTP Proxy requires authentication.
The AdditionalHeaders field can hold HTTP headers needed by the HTTP proxy. We can pass several header lines separated with “|” like

AdditionalHeaders => 'Accept-Language: en-US|From: johndoe@acm.com'

A complete example is available in \Examples\CONNECT_WITH_SSH_KEY_PROXY.sql file

Connection timeout

In network communications, a timeout is a maximum period that we are eager to wait for an action to complete.

ORA_SFTP accepts two last parameters in the CONNECT_HOST method: connection_timeout and read_timeout, of type PLS_INTEGER and representing the timeout periods in milliseconds (1000 milliseconds = 1 second). By default, both have a value of 0 (zero) which means wait infinitely. There are situations when we may prefer the operations to fail (with SFTP exception) after a certain amount of time. In that case, we have to provide initial values when connecting to a remote host.

The example below will wait for 0.5 (half) second for connection and maximum 1 (one) second for an operation to complete.

1
2
3
4
5
6
7
8
9
10
DECLARE 
 connection_id NUMBER;
 connection_timeout PLS_INTEGER;
 read_timeout PLS_INTEGER;
BEGIN
 connection_timeout:= 500; -- 0.5 seconds 
 read_timeout:= 1000; -- 1 second
 connection_id := ORA_SFTP.CONNECT_HOST('localhost', 'myusername', 'mypassword', connection_timeout, read_timeout);END;
/

Summary

This chapter is an introduction to how to connect to an SFTP server with PL/SQL from inside the Oracle(r) Database version 11 and above.

The next step that you may want to check is how to disconnect an already established connection.