The Oracle (tm) Users' Co-Operative FAQ

Is there a way to read the names of a set of files with a given extension from a directory as if from a SQL cursor?


Author's name: Chris Poole

Author's Email: dba@chrispoole.co.uk

Date written: 14/05/2006

Oracle version(s): 10.1.0.4.0 EE

 How to get a listing of files in a directory using PL/SQL has got to be one of the most  popular questions asked by Oracle developers. Prior to 10g, DIY solutions included the  sophisticated, using either Java (8i onwards) or External Procedures (8 onwards),  or the clunky (pipe servers and shell scripts from 7 onwards).  However in 10g, there is a little piece of undocumented functionality that, with care, can be used  to do exactly this, and what’s better, is a purely native implementation

Back to index of questions


 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).
 

Further reading:       http://www.ixora.com.au/scripts/prereq.htm  (shows how to create views on x$ fixed tables)

Further work:          http://www.chrispoole.co.uk/

The way forward is to wrap this functionality in a package, perhaps using  directory objects instead and checking access priviliges, creating a view based on 
x$krbmsft, maybe even allowing/disallowing subdirectory traversal and limiting memory usage search size. I see another project coming, stay tuned for XUTL_FINDFILES!

Back to top

Back to index of questions