OraSFTP tutorial

This page is an online tutorial for DidiSoft OraSFTP. Read below if you are interested in implementing SFTP and FTPS communication with PL/SQL inside the Oracle(r) Database.

The ORA_NET tutorial can be found here

Setup and Update

Basic SFTP communication

Remote folders management

Logging

Appendix
A. Exception handling in network communication failure
B. List of supported algorithms

ORA_NET
Downloading web resources over HTTP/S

Disconnecting

The last thing when we finish our job is disconnect. Disconnecting is done with ORA_SFTP.DISCONNECT_HOST method.

1
2
3
4
 connection_id := ORA_SFTP.CONNECT_HOST(...);
 -- Do SFTP stuff here ...
 ORA_SFTP.DISCONNECT_HOST(connection_id);
/

Listing remote folders

A dedicated chapter described in details on how to retrieve the contents of a remote folder.

Read how to LIST a remote SFTP folder from PL/SQL

Get/Set the current folder

Note: Current folder support was added in version 1.2.6. In previous versions either absolute paths should be used or relative from the user remote home directory.

The current folder can be retrieved with ORA_SFTP.PWD or its alias ORA_SFTP.CURRENT_DIRECTORY:

1
2
 dbms_output.put_line('Current folder is ' || ORA_SFTP.CURRENT_DIRECTORY(connection_id));
 dbms_output.put_line('Current folder is ' || ORA_SFTP.PWD(connection_id));

The methods for changing it are ORA_SFTP.CD and ORA_SFTP.CD_UP for returning one level back. When changing the current folder a name relative from the current folder can be used or absolute path (starting with ‘/’ like ‘/home/deploy’) and the directory up ‘..’ and current directory ‘.’ special names.

1
2
 ORA_SFTP.CD(connection_id, '..'));
 ORA_SFTP.CD_UP(connection_id));

As of version 1.2.10 a new function CHDIR is available just like CD procedure, but returns TRUE on successful directory change :

1
2
3
 IF  ORA_SFTP.CHDIR(connection_id, '..')) THEN
   dbms_output.put_line('Successfully changed one folder up!');
 END IF;

Here is a complete example illustrating all the mentioned directory change routines:

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);
 
 ORA_SFTP.CD(connection_id, 'Logs'); dbms_output.put_line(ORA_SFTP.PWD(connection_id));
 
 -- go one directory up
 ORA_SFTP.CD_UP(connection_id);
 dbms_output.put_line(ORA_SFTP.CURRENT_DIRECTORY(connection_id));
 
 ORA_SFTP.DISCONNECT_HOST(connection_id); 
EXCEPTION
 WHEN OTHERS THEN
 DBMS_OUTPUT.PUT_LINE('General error : ' || SQLERRM );
END;
/

Uploading data

Uploading BLOB and CLOB fields into remote files.

Downloading data

Downloading data from the SFTP server is performed through ORA_SFTP.DOWNLOAD/GET. 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 a 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, otherwise, an 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');

Last modification time of file

We can get the time of the last modification of a remote file with ORA_SFTP.LAST_MODIFIED_TIME. This can be useful if we want to synchronize remote and local files/folders.

1
2
DBMS_OUTPUT.PUT_LINE('Last modified on ' ||
                       ORA_SFTP.LAST_MODIFIED_TIME(connection_id, 'MyDoc.pdf'));

Rename

A file or folder name can be changed with ORA_SFTP.RENAME.

 ORA_SFTP.RENAME(connection_id, 'MyDoc.pdf', 'MyDocNewName.pdf');

Change permissions

The permissions of a remote file or folder can be changed with ORA_SFTP.CHMOD or CHANGE_MODE.

 ORA_SFTP.CHMOD(connection_id, 'MyDoc.pdf', 
                                                  ORA_SFTP.PERMISSION_OWNER_READ + 
                                                  ORA_SFTP.PERMISSION_OWNER_WRITE + 
                                                  ORA_SFTP.PERMISSION_GROUP_READ);

Below is a list of the available self explanatory permission constants from the ORA_SFTP package:

ORA_SFTP.PERMISSION_OWNER_READ    -- owner has read permission
ORA_SFTP.PERMISSION_OWNER_WRITE   -- owner has write permission
ORA_SFTP.PERMISSION_OWNER_EXEC    -- owner has execute permission
ORA_SFTP.PERMISSION_GROUP_READ    -- group has read permission
ORA_SFTP.PERMISSION_GROUP_WRITE   -- group has write permission
ORA_SFTP.PERMISSION_GROUP_EXEC    -- group has execute permission
ORA_SFTP.PERMISSION_OTHERS_READ   -- others have read permission
ORA_SFTP.PERMISSION_OTHERS_WRITE  -- others have write permission
ORA_SFTP.PERMISSION_OTHERS_EXEC   -- others have execute permission

Touch

We can change the time of the last modification of a remote file with ORA_SFTP.TOUCH either to the current time just like the original Unix/Linux touch command or to a specified time. This can be useful if we want to synchronize remote and local files/folders. If the specified remote file doesn’t exist it will be created as an empty file.

1
2
 ORA_SFTP.TOUCH(connection_id, 'MyDoc.pdf');  -- last modification time will be SYSDATE
 ORA_SFTP.TOUCH(connection_id, 'MyDoc.pdf', TO_TIMESTAMP ('10-Sep-02 14:10:10', 'DD-Mon-RR HH24:MI:SS'));  -- last modification time will be the specified TIMESTAMP

Touch Local

ORA_SFTP.TOUCH_LOCAL changes the last modification of a local file either to the current time just like the original Unix/Linux touch command or to a specified time. This can be useful if we want to synchronize remote and local files/folders.

1
2
 ORA_SFTP.TOUCH_LOCAL('full path here\MyDoc.pdf');  -- last modification time will be SYSDATE
 ORA_SFTP.TOUCH_LOCAL('full path here\MyDoc.pdf', TO_TIMESTAMP ('10-Sep-02 14:10:10', 'DD-Mon-RR HH24:MI:SS'));  -- last modification time will be the specified TIMESTAMP

Note:
– this method assumes that the user account under which the Oracle DB process runs has write access to the file being ‘touched’!
– the database user (schema) that executes this call must have been granted write access to the folder where this file is located, like:

SQL>> call dbms_java.grant_permission( 'USER', 'SYS:java.io.FilePermission', 'full path here\*', 'read,write' );

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');

Checking is a remote object a folder

We can check if 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;

Verbose logging

In certain cases, we may need to inspect communication details. In this case, we can turn low-level debug logging, which will output debug information the same way that DBMS_OUTPUT does.
Enabling the verbose logging is done via the ORA_SFTP.SET_DEBUG method:

1
2
3
4
5
6
7
8
BEGIN
     DBMS_OUTPUT.ENABLE(100000);
     DBMS_JAVA.set_output(100000);
     ORA_SFTP.SET_DEBUG(TRUE);
 
-- invoke ORA_SFTP methods ...
 
END

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
 
-- invoke ORA_SFTP methods ...
 
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;
/

List of supported algorithms

 

Used for

 

 

Algorithm

Key exchange

diffie-hellman-group1-sha1

diffie-hellman-group14-sha1

diffie-hellman-group-exchange-sha1

diffie-hellman-group14-sha256

diffie-hellman-group15-sha384 *

diffie-hellman-group15-sha512 *

diffie-hellman-group16-sha512 *
diffie-hellman-group17-sha512 *

diffie-hellman-group18-sha512 *

diffie-hellman-group-exchange-sha256

ecdh-sha2-nistp256 *

ecdh-sha2-nistp384 *

ecdh-sha2-nistp521 *

ecdh-sha2-secp256k1 *

Public key

RSA (1024, 2048, 3072, 4096, 5120, 6144, 7168, 8192 bits)

DSA (1024, 2048, 3072 bits)

ECDSA (256, 384, 521 bits) *

Data integrity

hmac md5 (16-byte key)

hmac md5-96 (16-byte key)

hmac sha-1 (20-byte key, FIPS PUB 198)

hmac sha-1-96 (20-byte key, FIPS PUB 198)

hmac-sha2-256 (32-byte key, FIPS PUB 180-3)

hmac-sha2-512 (64-byte key, FIPS PUB 180-3) *

Session encryption

AES (128-, 192-, or 256-bit key, CBC or CTR mode)

Blowfish (128-bit key)

3DES (168-bit key)

Note:

* As of version 1.4.5

 

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 methods

 VERSION returns the current version of the package: SELECT ORA_SFTP.VERSION FROM dual
 IS_TRIAL_VERSION returns TRUE if this is an evaluation version, FALSE if a licensed version
 CONNECT_HOST connects to SFTP host
 CONNECT_WITH_KEY_AND_PASSWORD connects to SFTP host requiring both password and a key
 DISCONNECT_HOST disconnects from SFTP host
 LIST lists files and folder names from a remote host
 LIST_EX lists files and folders from a remote host with file information
 LIST_FILES lists file names from a remote host
 LIST_FILES_EX lists files from a remote host with file information
 LIST_DIRS lists folder names from a remote host
 LIST_DIRS_EX lists folders from a remote host with file information
 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 check is a remote object a directory
PWD retrieves the current remote directory
CURRENT_DIRECTORY retrieves the current remote directory (alias of PWD)
CD changes the current remote directory (procedure)
CHDIR changes the current remote directory with result (function)
CD_UP changes the current remote directory one level back
FILE_SIZE returns the size of a remote file
LAST_MODIFIED_TIME time (UTC) of last modification of a remote file
RENAME changes file or folder name
CHANGE_GROUP, CHGRP sets the group Id of a remote file
CHANGE_MODE, CHMODE sets the permissions of a remote file
TOUCH sets the time of last modification of a remote file
TOUCH_LOCAL sets the time of last modification of a local file