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
- 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
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-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 |