Extend UTL_FILE with function to return list of files in directory

MortenBraten

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.

Sven W.

Yes this is way overdue.

Personally I use external tables with the preprocessor option, but this is a bit tricky to setup.

Also in 12c there is a scheduled file watcher job. This might cover a lot of use cases for which a directory would be needed.

https://docs.oracle.com/database/121/ARPLS/d_sched.htm#ARPLS72298

Erik van Roon

Agree.

I would like to add that, in my humble opinion, the t_file_list that is returned should be a collection of records that, apart from filenames, also contains file information like filepermissions and filesizes.

FatMartinR

Already been discussed & resolved here:   https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:439619916584

Sven W.

Already been discussed & resolved here:   https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:439619916584

So your are saying you prefer a workaround that

a) depends on using the JVM in the database,

b) needs additional privs (JAVAUSERPRIV etc.)

instead of implementing a proper and secure way and putting it in the UTL_FILE package where it belongs?

Well I disagree.

William Robertson

Already been discussed & resolved here:   https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:439619916584

I just tried Tom's solution and any "create or replace and compile java source" operation gives me "ORA-29538: Java not installed". The fix appears to be for a DBA to run initjvm.sql on every single Oracle instance that might need it, which could be dozens for my current system alone, plus the additional privileges Sven mentioned. The paperwork alone is frankly daunting. All so we can deploy a custom directory-reader procedure with unknown (to me) security implications, because the supplied file-reader package doesn't have one. I would hardly call that 'resolved'

Joerg.Sobottka

I just tried Tom's solution and any "create or replace and compile java source" operation gives me "ORA-29538: Java not installed". The fix appears to be for a DBA to run initjvm.sql on every single Oracle instance that might need it, which could be dozens for my current system alone, plus the additional privileges Sven mentioned. The paperwork alone is frankly daunting. All so we can deploy a custom directory-reader procedure with unknown (to me) security implications, because the supplied file-reader package doesn't have one. I would hardly call that 'resolved'

You have a (couple of) system(s) running without JVM? I haven't seen this since ages...

And it's horrible to use a RMAN related only procedure to do file listing...

Marwim

You have a (couple of) system(s) running without JVM? I haven't seen this since ages...

And it's horrible to use a RMAN related only procedure to do file listing...

running without JVM?

Everyone using XE

Joerg.Sobottka

running without JVM?

Everyone using XE

Marwim wrote:running without JVM?Everyone using XE

Which I wouldn't use in production, and I think 18c XE will have jvm on board.

Gerald Venzl-Oracle

The enhancement request is a valid one. Basic file system operations provide interfaces to deal with list of files in every language, PL/SQL shouldn't be an exception. The dependency on JVM is not necessary for that and nor should it be. Furthermore, having a collection type to deal with lists of files should also be provided out of the box rather than left to the user to implement.

William Robertson

You have a (couple of) system(s) running without JVM? I haven't seen this since ages...

And it's horrible to use a RMAN related only procedure to do file listing...

Joerg.Sobottka wrote:You have a (couple of) system(s) running without JVM? I haven't seen this since ages... 

None of the Oracle instances at that site had a JVM installed as far as I know. It's one of those features I've never seen used anywhere.