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

• Setup of OraSFTP
• Upgrade
• Uninstall

Basic SFTP communication

• Connect
• Disconnect
• Get/Set current folder
• Upload
• Download
• Check if a file exist
• Deleting file
• Size of a file
• Last modified time
• Rename
• Change permissions
• Touch
• Touch Local

Remote folders management

• List remote folder
• Create folder
• Delete folder
• Check is a remote object a folder

Logging

• Verbose log

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

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