LIST remote folder

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