Using the procedure SEARCHFILES in DBMS_BACKUP_RESTORE

I found the following great article about how to list files in directory from within sqlplus here, written by David Marco. As pointed out by several oracle bloggers, there is very little documentation around for this package.

It just so happened that a user was in need of such functionality, and his code could be used straight-off.

However, my customer had already ammended a very useful functionality, by adding support for sending the directory name in as a parameter.

The code as it looks now:

CREATE OR REPLACE FUNCTION LIST_FILES( p_directory IN VARCHAR2, p_file_pattern IN VARCHAR2 default null)
RETURN file_array pipelined AS

l_path VARCHAR2(1024);
l_dummy VARCHAR2(1024);

BEGIN

   SELECT directory_path
   INTO l_path
   FROM all_directories
   WHERE directory_name = p_directory;

   sys.DBMS_BACKUP_RESTORE.SEARCHFILES(l_path, l_dummy);

   FOR file_list IN (SELECT FNAME_KRBMSFT AS file_name
                     FROM X$KRBMSFT
                     WHERE FNAME_KRBMSFT LIKE '%'|| NVL(p_file_pattern, FNAME_KRBMSFT)||'%' ) LOOP
      PIPE ROW(file_list.file_name);
   END LOOP;

END;
/

The call will thus be:

SELECT * FROM TABLE(LIST_FILES('MY_DIR','%*.dmp%'));