USA and Canada: 866.253.7568   International: +1-501-313-0397
DidiSoft Ltd.

OraSFTP tutorial

This page is an online tutorial for DidiSoft OraSFTP. Read below if you are interested in implementing SFTP communication with PL/SQL inside an Oracle database.

SFTP authentication

Authenticate with Password or SSH keys

Basic SFTP communication

Remote folders management

Exceptions

Exception handling in network communication failure

SFTP authentication with username and password or SSH keys

The SFTP protocol connects allows us to connect to an SFTP server by identifying with a username and password valid for the remote machine or with an SSH key.

Password authentication

In the username and password scenario we shall know in advance those details and use them when invoking ORA_SFTP.CONNECT_HOST

SSH keys

When using the more secure way of identifying with an SSH key, the public SSH key must be installed (e.g. copied) on the remote SSH/SFTP server machine and associated with the username that we are going to use for the connection. When we start the connection we use our private SSH key to sign the initial SSH handshake and thus authenticate to the remote machine.

SSH keys can be met in various formats, the most common being OpenSSH format and PuTTY. OraSFTP recognizes both and uses them transparently and expects the key to be loaded into a BLOB field.

The example below illustrates how to load SSH private key test_putty_private.ppk from a local machine folder into a BLOB filed, which afterwards can be passed to ORA_SFTP.CONNECT_HOST:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
DECLARE 
 private_key_handle BFILE;
 private_key BLOB; -- the SSH key will be loaded here
BEGIN
 -- initialize the key storage
 DBMS_LOB.createtemporary(PRIVATE_KEY, TRUE);
 
 -- the direcory name must be created with CREATE DIRECTORY MY_KEYS_DIR AS '/my_folder_with_ssh_keys';
 private_key_handle := BFILENAME('MY_KEYS_DIR', 'test_putty_private.ppk'); -- directory name must be Upper case
 
 -- load the data into a BLOB
 DBMS_LOB.OPEN(private_key_handle, DBMS_LOB.LOB_READONLY);
 DBMS_LOB.LoadFromFile( DEST_LOB => private_key, 
                        SRC_LOB => private_key_handle, 
                        AMOUNT => DBMS_LOB.GETLENGTH(private_key_handle) );
 DBMS_LOB.CLOSE(private_key_handle);
END;
/

Connecting and disconnecting

The first thing to do when initialing SFTP communication session is connecting to the remote machine. The last thing when we finish our job is disconnect.
Connecting is done with ORA_SFTP.CONNECT_HOST and disconnecting is done with ORA_SFTP.DISCONNECT_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

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 username and SSH private key.

The SSH private key must be located into a BLOB field (check above).  Check also the [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;
/

Listing remote folders

Listing remote files located on the SFTP server is like running the DIR command in a Windows command prompt or ls on Unix/Linux shell.

The list command is ORA_SFTP.LIST. It returns named result typed ORA_SFTP_FILES_LIST (internally it is table of varchar2(2000))

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
DECLARE 
 files ORA_SFTP_FILES_LIST;
 connection_id NUMBER; 
 private_key_handle BFILE;
 private_key BLOB; 
 PRIVATE_KEY_PASSWORD VARCHAR2(500); 
BEGIN
 DBMS_LOB.createtemporary(PRIVATE_KEY, TRUE);
 private_key_handle := BFILENAME('PGP_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';
 
 connection_id := ORA_SFTP.CONNECT_HOST('localhost', 22, 'nasko', private_key, private_key_password);
 
 files := ORA_SFTP.LIST(connection_id, '.'); 
 
 -- print the listed file names 
 FOR i IN files.first .. files.last loop
 dbms_output.put_line('file(' || i || ') = ' || files(i));
 END loop;
 
 ORA_SFTP.DISCONNECT_HOST(connection_id); 
EXCEPTION
 WHEN OTHERS THEN
 DBMS_OUTPUT.PUT_LINE('General error : ' || SQLERRM );
END;
/

List only the files

In order to list only the file names we shall use ORA_SFTP.LIST_FILES instead of ORA_SFTP.LIST

List only the folders

In order to list only the folder names we shall use ORA_SFTP.LIST_DIRS instead of ORA_SFTP.LIST

Uploading data

With ORA_SFTP we can upload data directly from the Oracle database into a remote SFTP location. The source data must be in a BLOB field. If it is stored as another datatype you must first convert it into BLOB (check the Oracle PL/SQL documentation for converting between PL/SQL datatypes). The method for upload is ORA_SFTP.UPLOAD

In the example below we simply upload the SSH private key just for the sake of the example as it is a BLOB field anyway. You have to adopt 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
DECLARE 
 connection_id NUMBER; 
 private_key_handle BFILE;
 private_key BLOB; 
 PRIVATE_KEY_PASSWORD VARCHAR2(500); 
BEGIN
 DBMS_LOB.createtemporary(PRIVATE_KEY, TRUE);
 private_key_handle := BFILENAME('PGP_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';
 
 connection_id := ORA_SFTP.CONNECT_HOST('localhost', 22, 'nasko', private_key, private_key_password);
 
 ORA_SFTP.UPLOAD(connection_id, private_key, 'private_key.txt');
 
 ORA_SFTP.DISCONNECT_HOST(connection_id);
END;
/

Downloading data

Downloading data from the SFTP server is performed through ORA_SFTP.DOWNLOAD. It returns the downloaded data into BLOB data type format. You have to convert it to other formats if you need to take additional actions with the data.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
DECLARE 
  connection_id NUMBER;
  private_key_handle BFILE;
  private_key BLOB;  
  PRIVATE_KEY_PASSWORD VARCHAR2(500);  
  downloaded_file BLOB;
BEGIN
    DBMS_LOB.createtemporary(PRIVATE_KEY, TRUE);
    private_key_handle := BFILENAME('PGP_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';
 
    connection_id := ORA_SFTP.CONNECT_HOST('localhost', 22, 'nasko', private_key, private_key_password);
 
    downloaded_file := ORA_SFTP.DOWNLOAD(connection_id, 'remote_file.dat');
 
    ORA_SFTP.DISCONNECT_HOST(connection_id);
END;
/

Check if file exists

We can check does a remote file exists with ORA_SFTP.EXISTS. The method is applicable for both files and folders.

1
2
3
4
5
IF ORA_SFTP.EXISTS(connection_id, 'remote_file.pdf') THEN
 BEGIN
   DBMS_OUTPUT.PUT_LINE('file exists'); 
 END;
END IF;

Deleting remote files

To delete a remote file we use shall ORA_SFTP.DELETE. A good practice is to place it into ORA_SFTP.EXISTS check, othrerwise exception will be thrown.

ORA_SFTP.DELETE(connection_id, 'remote_file.dat');

Size of a remote file

We can grab the size of a remote file in bytes with ORA_SFTP.FILE_SIZE. This can be useful to check after upload the amount of the uploaded data.

1
2
3
 DBMS_OUTPUT.PUT_LINE('File size is ' ||
                      ORA_SFTP.FILE_SIZE(connection_id, 'remote_file.pdf')
                      || ' bytes');

Creating folder

We can create a remote folder with ORA_SFTP.CREATE_DIR. The created directory will have permissions 700 (owner can read, write and execute)

ORA_SFTP.CREATE_DIR(connection_id, 'NewFolder');

Delete folder

Removing remote folders is done through ORA_SFTP.DELETE_DIR.

ORA_SFTP.DELETE_DIR(connection_id, 'NewFolder');

Check is a remote object a folder

We can check is a remote object a folder with ORA_SFTP.IS_DIRECTORY. Here we have to keep in mind that the result will be FALSE even if there is no such object with the specified name:

1
2
3
4
5
IF ORA_SFTP.IS_DIRECTORY(connection_id, 'NewFolder') THEN
 BEGIN
  DBMS_OUTPUT.PUT_LINE('it is a directory'); 
 END;
END IF;

Exception handling

In our PL/SQL exception handling section we can distinguish is the error related to the ORA_SFTP package by checking for the string ‘SFTPException’  in the error message:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
BEGIN
 
ORA_SFTP...
 
EXCEPTION
 WHEN OTHERS THEN
 BEGIN
 IF INSTR (SQLERRM, 'SFTPException') = 0
 THEN
 DBMS_OUTPUT.PUT_LINE('SFTP related error : ' || SQLERRM );
 ELSE 
 DBMS_OUTPUT.PUT_LINE('General error : ' || SQLERRM );
 END IF;
 END;
END;
/

Summary

This chapter was a starting tutorial for using DidiSoft OraSFTP (ORA_SFTP). The next step from here is to study the example source code that ships with the product in the [product ZIP file]/Examples folder.

List of ORA_SFTP package method

 VERSION returns current version of the package
 IS_TRIAL_VERSION returns TRUE if this is an evaluation version, FALSE if a licensed version
 CONNECT_HOST connects to SFTP host
 DISCONNECT_HOST disconnects from SFTP host
 LIST lists files and folders from a remote host
 LIST_FILES lists files from a remote host
 LIST_DIRS lists folders from a remote host
 UPLOAD uploads BLOB field to a remote host
 DOWNLOAD downloads a remote file into a BLOB field
 DELETE removes remote file
 DELETE_DIR removes remote folder
 CREATE_DIR creates a remote folder
 EXISTS check does a remote file or folder exist
 IS_DIRECTORY checks is a remote object a directory
FILE_SIZE returns the size of a remote file