/*
-- 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 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 it's 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.
--
-- Updated 29/05/2007
-- 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). However, on some platforms and versions
-- passing in a wildcard or anything other than a directory name will result in an empty X$KRBMSFT.
-- The trick here is to simply pass a valid OS directory name - the entire listing of that directories
-- contents will now appear in X$KRBMSFT, which can then be filtered using LIKE.
--
-- 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 blow out memory
--
-- 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!
--
-- This code has been tested and works as intended on Oracle 10104 EE.
--
-- Get this tip and others, along with useful PL/SQL utilities at
-- www.chrispoole.co.uk
--