Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

Processing

Post Details

Added on Jun 15 2016
17 comments
25,218 views