Forum Stats

  • 3,874,444 Users
  • 2,266,734 Discussions
  • 7,911,851 Comments

Discussions

Extend UTL_FILE with function to return list of files in directory

MortenBraten
MortenBraten Member Posts: 304 Silver Badge
edited May 7, 2020 7:11PM in Database Ideas - Ideas

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.

MortenBratenSven W.Jeffrey KempkoyliJorge RimblasWilliam RobertsonpattonjgErik van RoonHemant K ChitaleAndre SantossensoftOmar M. SawalhahChris MentenFABRICEBMarwimBPeaslandDBAJon TheriaultRainer StenzelberxMKJ10930279jnicholas330user2916724dherzhauNiels Heckerfac586ulohmanngaverillUser11273064-OracleMettemusens2Peter HraškoBEDEgassenmjAndrew MoskevitzNatarajasuthan KPaulzipMartin PreissGregVGerald Venzl-Oracletonibony7Albert Vreeswijkuser8845697user13625167user2382601Billy Verreynne21800Chase MarlerToddBarryRSa-Devandre.psantosDenis SavenkoJustin WarwickRajeshAlexMarcel Hoefsdominiquecomteuser10068379PeterGJose Carlos PavónsdstuberMohamed Faramawyuser8830676dirkvanhauteGroovyYevon
63 votes

Under Review - Voting Still Open · Last Updated

«1

Comments

  • Sven W.
    Sven W. Member Posts: 10,559 Gold Crown

    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

    Chase Marler
  • Erik van Roon
    Erik van Roon Member Posts: 60 Blue Ribbon

    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.

    berxChase Marler
  • Sven W.
    Sven W. Member Posts: 10,559 Gold Crown

    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.

    jnicholas330Peter Hraškouser10068379Mohamed Faramawy
  • William Robertson
    William Robertson Member Posts: 9,568 Bronze Crown

    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
    Joerg.Sobottka Senior Consultant and Oracle Ace Member Posts: 598 Bronze Trophy
    edited Jul 16, 2018 9:42AM

    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
    Marwim Member Posts: 3,654 Gold Trophy

    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

    Peter Hraško
  • Joerg.Sobottka
    Joerg.Sobottka Senior Consultant and Oracle Ace Member Posts: 598 Bronze Trophy
    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.

  • William Robertson
    William Robertson Member Posts: 9,568 Bronze Crown

    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.