|
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 |
|
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!