SFTP Upload data with PL/SQL

This chapter illustrates how to upload data from the Oracle® Database into a remote SFTP server with PL/SQL code. We can upload BLOB and CLOB fields directly into files.

List of Examples

Uploading data

The method for uploading BLOB fields with ORA_SFTP is UPLOAD (or PUT)

In the example below, we will upload an image contained in a customers’ record to a remote SFTP server file location. You have to adapt the code for uploading your own BLOB data:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

DECLARE 
 connection_id NUMBER; 
 private_key_handle BFILE;
 private_key BLOB; 
 private_key_password VARCHAR2(500); 
 DATA BLOB;
BEGIN
 DBMS_LOB.createtemporary(private_key, TRUE);
 private_key_handle := BFILENAME('SFTP_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) );
 DBMS_LOB.CLOSE(private_key_handle);
 private_key_password := 'changeit';
 
  SELECT picture_blob INTO DATA
  FROM customers
  WHERE customer_id = 100;
 
 connection_id := ORA_SFTP.CONNECT_HOST('localhost', 22, 'nasko', private_key, private_key_password);
 
 ORA_SFTP.UPLOAD(connection_id, DATA, 'image.png'); 
 ORA_SFTP.DISCONNECT_HOST(connection_id);
END;
/

Uploading CLOB fields

The method for uploading CLOB fields with ORA_SFTP is UPLOAD_CLOB.

CLOB fields contain character data and it has to be encoded into bytes in order for the remote file to have a meaning. By default, UPLOAD_CLOB will encode the character data in UTF-8 format, or we can specify the target file encoding as an additional parameter.

In the example below we are going to create dynamically a CSV (comma separated values) report and upload it to an SFTP server. The resulting remote file encoding will be UTF-8.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

DECLARE 
 connection_id NUMBER; 
 private_key_handle BFILE;
 private_key BLOB; 
 private_key_password VARCHAR2(500); 
 v_clob CLOB;
 
 CURSOR C1 IS SELECT EMPNO, ENAME, SAL, E.DEPTNO, DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO ORDER BY EMPNO;
 C1_R C1%ROWTYPE;
BEGIN
 DBMS_LOB.createtemporary(private_key, TRUE);
 private_key_handle := BFILENAME('SFTP_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) );
 DBMS_LOB.CLOSE(private_key_handle);
 private_key_password := 'changeit';
 
 FOR C1_R IN C1
    LOOP
        v_clob := v_clob || C1_R.EMPNO || ',' || C1_R.ENAME || ',' || C1_R.SAL;
        v_clob := v_clob || CHR(10) || chr(13);
    END LOOP;
 
 connection_id := ORA_SFTP.CONNECT_HOST('localhost', 22, 'nasko', private_key, private_key_password); 
 ORA_SFTP.UPLOAD_CLOB(connection_id, v_clob, 'EmpoleesReport.csv');
 
 ORA_SFTP.DISCONNECT_HOST(connection_id);
END;
/

If for example, we went to specify encoding other than UTF-8 for the remote file, we can achieve this by specifying the encoding as the last parameter like this:

1

 ORA_SFTP.UPLOAD_CLOB(connection_id, v_clob, 'EmpoleesReport.csv', 'UTF-32');

A list of the available encodings that we can use is available here.

Retry strategy

Network connections are not reliable and an upload may fail. This is normal and in that case, we have to retry the upload again. Our suggestion is to retry using the overloaded variant of the PUT/UPLOAD method that can customize the upload read/write buffer, by using a rather small value like:

BEGIN
 Ora_SFTP.put(retry_connection_id, data_blob, 'data.dat');
EXCEPTION
 WHEN OTHERS THEN
 BEGIN
  IF INSTR (SQLERRM, 'Unexpected end of sftp stream') <> 0
  THEN
    Ora_SFTP.DISCONNECT_HOST(retry_connection_id);
    retry_connection_id := ORA_SFTP.CONNECT_HOST(...);
    ORA_SFTP.UPLOAD(retry_connection_id, data_blob, 'data.dat', 1024); -- 1KB transfer buffer
  ELSE
    ... handle other cases 
  END IF;
 END;
END;

Secure Copy (SCP)

Most SFTP servers, especially on Unix systems also provide an SCP (secure copy) service that can be used from the same SFTP connection.

SCP is useful when we need more speed (see below) as its protocol doesn’t have the mandatory acknowledgments for each SFTP packet. And despite that, it is as secure as SFTP as it relies on the same authenticated and encrypted connection.

We can perform SCP copy with ORA_SFTP.SCP_UPLOAD:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

DECLARE 
 connection_id NUMBER; 
 private_key_handle BFILE;
 private_key BLOB; 
 private_key_password VARCHAR2(500); 
 DATA BLOB;
BEGIN
 DBMS_LOB.createtemporary(private_key, TRUE);
 private_key_handle := BFILENAME('SFTP_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) );
 DBMS_LOB.CLOSE(private_key_handle);
 private_key_password := 'changeit';
 
  SELECT picture_blob INTO DATA
  FROM customers
  WHERE customer_id = 100;
 
 connection_id := ORA_SFTP.CONNECT_HOST('localhost', 22, 'nasko', private_key, private_key_password);
 
 ORA_SFTP.SCP_UPLOAD(connection_id, DATA, 'image.png'); 
 ORA_SFTP.DISCONNECT_HOST(connection_id);
END;
/

Performance issues

SFTP speed is limited to around 1.5 MB/s due to the data encryption overhead and the acknowledgment of each SFTP packet.

As of version 1.9.3 OraSFTP provides support for transparent ZLib compression which reduces the data being transferred and increases the speed by 33%. SCP provides a faster transfer speed and if your remote SFTP server supports SCP you can use SCP_UPLOAD instead of UPLOAD.

Below is an excerpt comparing the SFTP upload and SCP upload speeds

File Size ORA_SFTP.UPLOAD ORA_SFTP.SCP_UPLOAD
90 MB 7 min 38 sec 5 min 09 sec
150 MB 12 min 42 sec 8 min 56 sec

 

Summary

This article discussed how to upload data from inside the Oracle(c) database to a remote SFTP server.

You may also like to read how to download remote files into BLOB and CLOB fields with PL/SQL.