The listing of file names in a directory through PL/SQL

There is no straightforward way to enlist file names in a file system directory from PL/SQL level. The UTL_FILE package allows for many operations on the files, yet there is no equivalent to command ls. There is, however, a way to do it from within the database engine.

The first idea is to use Java or extproc. Tom Kyte describes such solution on AskTom.
The second idea is to use the DBMS_BACKUP_RESTORE package. This way is a little tricky and may change between different versions of the Oracle RDBMS binaries, so one has to be careful, when using it. As this is quite interesting I place here a short description.

connect / as sysdba
declare
p_dir varchar2(64) := '/oracle';
p_nul varchar2(1024);
begin
sys.dbms_backup_restore.searchFiles(p_dir, p_null);
dbms_output.put_line(p_nul);
for f in (select fname_krbmsft fname from x$krbmsft) loop
dbms_output.put_line(f.fname);
end loop;
end;
/

I run it on the 11.2.0.2 version and the whole thing works as advertised, however:

• does not follow symlinks
• returns all the file names recursively, so enlists also subdirectories' files

Possibly there are other features, I did not spot in the short test.

A very good article on the subject one may find at http://www.morganslibrary.com/hci/hci002.html. The author suggests some security measures, which in this case are obligatory like wrapping the x$krbmsft in a read only view and the searchFiles call in a procedure. Of course this is not only for security - possible changes in the DBMS_BACKUP_RESTORE package may bring an end to this procedure and then it is easier to change the code in custom procedure.