Read file names using PL/SQL

In 10g there is a new procedure hidden away in the undocumented DBMS_BACKUP_RESTORE package. It's called searchfiles, which is a bit of a giveaway and appears to have been introduced for the new backup features in 10g, as RMAN now needs to know about files in the recovery destination.

Calling this procedure populates an in memory table called x$krbmsft, which is one of those magic x$ tables, the only column which is of relevance to us is fname_krbmsft which is the fully qualified path and file name. This x$ table acts in a similar fashion to a global temporary table in that its contents can only be seen from the calling session. So two sessions can call searchfiles and each can only see the results of their call (which is extremely useful).

The code sample below will only really run as sys, due to the select from x$krbmsft, it's just intended as a demo. The first two parameters in the call to searchfiles are IN OUT so must be defined as variables, even though the second parameter is of no consequence to us and should be left as NULL.

Even though they are IN OUT, testing shows they don't appear to change. The first parameter is the string to search for, in much the same format as you would pass in a call to dir (Windows) or ls (Unix).

DECLARE
pattern VARCHAR2(1024) := 'C:\temp\*.csv'; -- /tmp/*.csv
ns VARCHAR2(1024);
BEGIN
SYS.DBMS_OUTPUT.ENABLE(1000000);
SYS.DBMS_BACKUP_RESTORE.searchFiles(pattern, ns);
FOR each_file IN (SELECT FNAME_KRBMSFT AS name FROM X$KRBMSFT) LOOP
DBMS_OUTPUT.PUT_LINE(each_file.name);
END LOOP;
END;
/

This procedure appears to raise no exceptions, passing an invalid search string, such as a non-existant path or one with no permissions, simply results in an empty x$krbmsft. However, if the database parameter db_recovery_file_dest is not set, you will get ORA-19801.

Interestingly, this procedure recursively searches sub directories found in the search string. So passing a string of 'C:\windows' (for example) populates x$krbmsft with not only the files found in that directory but all the files found in all directories beneath, such as C:\windows\system32.

As x$krbmsft is an in memory table, you have been warned! Calling this procedure on a directory with thousands of sub directories and files has the potential to consume large amounts of memory (or more likely just generate an exception).