Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 62 Insurance
- 536.1K On-Premises Infrastructure
- 138.2K Analytics Software
- 38.6K Application Development Software
- 5.7K Cloud Platform
- 109.4K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.2K Integration
- 41.6K Security Software
Extend UTL_FILE with function to return list of files in directory

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
-
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
-
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.
-
Already been discussed & resolved here: https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:439619916584
-
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.
-
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'
-
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...
-
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
-
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.
-
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.
-
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.