OraSFTP supports methods that return only file/folder names and another set of methods that return a file information structure.
The methods that return only file names are LIST, LIST_FILES, and LIST_DIRES and they all return a result of type ORA_SFTP_FILES_LIST which is TABLE OF VARCHAR2(2000)
The other set of methods is LIST_EX, LIST_FILES_EX, LIST_DIRS_EX (EX as Extended) returning TABLE type ORA_SFTP_FILES_LIST_EX of ORA_SFTP_FILE objects:
ORA_SFTP_FILE type ---------------------- FileName VARCHAR(255) FileSize NUMBER, FileTime DATE, FileType NUMBER
This chapter will illustrate both the old LIST methods with their new Sorting and Filtering capabilities along with the new LIST_EX methods and how to utilize the ORA_SFTP_FILE data type.
Table of contents
- Current folder and file path
- LIST function
- LIST_EX function
- Sorting and filtering
- List only files
- List only folders
Current folder and file paths
All ORA_SFTP.LIST and ORA_SFTP.LIST_EX methods respect the current folder, but overloaded versions exist where an absolute path can be specified like:
1 2 | ORA_SFTP.LIST(connection_id) -- retrieves in current folder ORA_SFTP.LIST(connection_id, '/myfolder/mysubfolder/') -- retrieves folder different from the current one |
LIST function
Listing remote folders located on the SFTP server is like running the dir command in Windows command prompt or ls in Unix/Linux shell.
The list command is ORA_SFTP.LIST. It returns named result type 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('SSH_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_EX function
The extended command ORA_SFTP.LIST_EX returns a named result type ORA_SFTP_FILES_LIST_EX containing ORA_SFTP_FILE objects. The example below illustrates how to obtain the properties of each file
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 32 33 34 35 | DECLARE files ORA_SFTP_FILES_LIST_EX; 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('SSH_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_EX(connection_id, '.'); -- print the listed file names FOR i IN files.first .. files.last loop dbms_output.put(files(i).FileName || ' is ' || files(i).FileSize || ' bytes'); dbms_output.put_line(' last modified at ' || files(i).FileTime || ' ' || CASE files(i).FileType WHEN ORA_SFTP.FILE_TYPE_FILE THEN '(file)' WHEN ORA_SFTP.FILE_TYPE_DIRECTORY THEN '(directory)' WHEN ORA_SFTP.FILE_TYPE_SYMLINK THEN '(symbolic link)' END); END loop; ORA_SFTP.DISCONNECT_HOST(connection_id); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('General error : ' || SQLERRM ); END; / |
Sorting and Filtering
All LIST methods will return results sorted by name by default. Sometimes we may need to sort the results by date or file size or filter the results by pattern. In that case, we can use the overloaded LIST methods that accept order and file mask filter parameters.
Ordering
The following order constants are recognized by ORA_SFTP:
ORA_SFTP.ORDER_NAME_ASC — Order results by name ascending
ORA_SFTP.ORDER_NAME_DESC — Order results by name descending
ORA_SFTP.ORDER_SIZE_ASC — Order results by size ascending
ORA_SFTP.ORDER_SIZE_DESC — Order results by size descending
ORA_SFTP.ORDER_DATE_ASC — Order results by last modification time ascending
ORA_SFTP.ORDER_DATE_DESC — Order results by last modification time descending
File mask filtering
Two special characters can be used to filter the remote SFTP folder items : * and ?
* – in the file mask can be used to substitute any word. For example ‘*’ means all files. ‘Reports*‘ – all files starting with Report. ‘*.txt’ – all files ending with .txt, etc.
? – substitutes a single character. E.g. ‘data?0.txt‘ – will match ‘data10.txt‘, ‘data20.txt‘, ‘data30.txt‘, etc.
This example retrieves all files starting with ‘signed‘ and sorted by last modification time in descending order (the more recent first):
1 2 3 4 5 6 7 | DECLARE files ORA_SFTP_FILES_LIST; files_ext ORA_SFTP_FILES_LIST_EX; BEGIN ... files := ORA_SFTP.LIST(connection_id, ORA_SFTP.ORDER_DATE_DESC, 'signed*'); files_ext := ORA_SFTP.LIST_EX(connection_id, ORA_SFTP.ORDER_DATE_DESC, 'signed*'); |
List only the files
In order to list only the files, we shall use ORA_SFTP.LIST_FILES or ORA_SFTP.LIST_FILES_EX instead of ORA_SFTP.LIST and ORA_SFTP.LIST_EX respectively:
1 2 3 4 5 6 7 | DECLARE files ORA_SFTP_FILES_LIST; files_ext ORA_SFTP_FILES_LIST_EX; BEGIN ... files := ORA_SFTP.LIST_FILES(connection_id); files_ext := ORA_SFTP.LIST_FILES_EX(connection_id); |
List only the folders
In order to list only the folders, we must use ORA_SFTP.LIST_DIRS or the new ORA_SFTP.LIST_DIRS_EX
1 2 3 4 5 6 7 | DECLARE dirs ORA_SFTP_FILES_LIST; dirs_ext ORA_SFTP_FILES_LIST_EX; BEGIN ... dirs := ORA_SFTP.LIST_DIRS(connection_id); dirs_ext := ORA_SFTP.LIST_DIRS_EX(connection_id); |
Summary
This chapter illustrated how to retrieve the contents of a remote SFTP folder from within an Oracle database using the PL/SQL package ORA_SFTP.