Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Extend UTL_FILE with function to return list of files in directory

MortenBratenJun 15 2016 — edited May 7 2020

UTL_FILE is lacking a function that returns a list of files in a given directory. This is very unconvenient, as it is not possible to write PL/SQL code to process one or more files in a directory whose names are unknown. For example, I want to write a PL/SQL procedure that reads and processes a bunch of XML files that another process/system dumps in a folder which is mapped to a database directory object. With UTL_FILE I can only read files when I know the file name. If I could read out a list of file names, then I could loop through each of the files found and process them one by one.

The proposal is to add a new function called GET_FILE_LIST (or something like that) to UTL_FILE which would return a list of files.

The function signature could look like this:

function get_file_list (p_directory_name in varchar2,

                        p_file_pattern in varchar2 := null,

                                              p_max_files in number := null) return t_file_list

Note: Currently there is an undocumented procedure called dbms_backup_restore.searchfiles which can be used to implement such functionality, as shown here:

https://github.com/mortenbra/alexandria-plsql-utils/blob/master/extras/utl_file_nonstandard.pkb#L18

However, this proposal is made to get an official, documented and supported function into UTL_FILE, where it belongs.

Comments
Post Details
Added on Jun 15 2016
17 comments
22,949 views