Thursday, December 1, 2011

File list from the server

I've been asked by one of my architects whether there is a quick way of see a list of file existing in the random folder on the server. The first response was - NO, because all file system communication should go via DIRECTORY object (starting 10g).

But I recalled that I've read sometimes about a slightly nontrivial usage of undocumented package DBMS_BACKUP_RECOVERY that could serve exactly the required purpose. And in a minute I found the article and the following code sample:

DECLARE
  pattern VARCHAR2(1024) := 'C:\temp\*'; 
  ns VARCHAR2(1024); 
BEGIN
  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;


Yes, you still need some fairly advanced privileges (like logging as SYS and having 'db_recovery_file_dest' parameter set), but still - I was surprised! Thanks a lot to Christopher Poole that I don't need to reinvent at least this bicycle :-)

P.s. More posts about DBMS_BACKUP_RESTORE are coming ;-) !