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 path
• LIST function
• LIST_EX function
• Sorting and filtering
• List only files
• List only folders
• Invoke from another schema

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 can use ORA_SFTP.LIST_DIRS or the new ORA_SFTP.LIST_DIRS_EX 

1
2
3
4
5

DECLARE 
 dirs ORA_SFTP_FILES_LIST_EX;
BEGIN
...
 dirs := ORA_SFTP.LIST_DIRS_EX(connection_id);

and then iterate over the dirs list with the same way we did with the LIST_EX function.

Invoking OraSFTP from another schema

Some customers prefer to create the ORA_SFTP package in its own dedicated schema and then invoke its methods from another user account with login capabilities. In this case the other user must be granted execute access to the ORA_SFTP package.

For example, suppose we have installed ORA_SFTP in schema MY_SFTP, and the invoking user schema will be USER1. Then we have:

1. Login as MY_SFTP and execute:
2. GRANT EXECUTE on ORA_SFTP to USER1;
3. GRANT EXECUTE on ORA_SFTP_FILE to USER1;
4. GRANT EXECUTE on ORA_SFTP_FILES_LIST to USER1;
5. GRANT EXECUTE on ORA_SFTP_FILES_LIST_EX to USER1;

Now from the USER1 account we can invoke ORA_SFT methods by prefixing it with its owner schema:

select MY_SFTP.ORA_SFTP.VERSION from dual;

The network access permissions are necessary only for the ORA_SFTP owner schema (MY_SFTP).

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.