read a list of operating system files into a PL/SQL array
 
Home

Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups


General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement
As Solution Using V$
For many many years, long after it was no longer true, I believed that there was on way using SQL or PL/SQL to collect a listing of files in the operating system and read it into the database without using C or Java. The method demonstrated here uses X$KRBMSFT and thus requires that you expose an X$ memory structure. First lets look at the object at the heart of this "How Can I?"
conn / as sysdba

desc X$KRBMSFT

SELECT COUNT(*)
FROM x$krbmsft;
So we have an empty structure with reasonably cryptic column names. A few we can figure out ... INST_ID obviously gives us insight into how this might be used in a RAC cluster to differentiate files on node 1 from files on node 3. And FNAME_KRBMSFT, as a VARCHAR2(513) seems likely to be the file name as Oracle has used VARCHAR2(513) in several other places many of which point to a file name.
SELECT table_name, column_name
FROM dba_tab_cols
WHERE data_type = 'VARCHAR2'
AND data_length = 513
ORDER BY 2,1;
So the question becomes one of figuring out how to populate the array and see if we are correct. A search of DBA_ARGUMENTS for a package that might perform the trick takes us straight to DBMS_BACKUP_RESTORE which much, we realize, be able to read lists of files be they archived redo logs or backup files. This does the trick assuming you are on a Windows machine with the directory C:\TMP.
set serveroutput on

DECLARE
 TYPE file_rec IS RECORD (FileName dbms_sql.varchar2_table);
 frec file_rec;

 CURSOR fcur IS
 SELECT fname_krbmsft AS FileName
 FROM x$krbmsft
 WHERE fname_krbmsft LIKE '%.%';

 dirname VARCHAR2(1024) := 'c:\tmp';
 ns      VARCHAR2(1024);
BEGIN
  dbms_backup_restore.searchfiles(dirname, ns);

  OPEN fcur;
  FETCH fcur BULK COLLECT INTO frec;
  CLOSE fcur;

  FOR i IN 1 .. frec.FileName.COUNT LOOP
    dbms_output.put_line(frec.FileName(i));
  END LOOP;
END;
/
Well this is definitely impressive but making an X$ object visible to an application requires just a small amount of additional work to create a secure interface.
CREATE OR REPLACE VIEW expose_files AS
SELECT fname_krbmsft FILE_NAME
FROM x$krbmsft
READONLY;

CREATE OR REPLACE PROCEDURE expfiles (pattern IN OUT NOCOPY VARCHAR2, ns IN OUT NOCOPY VARCHAR2)
AUTHID DEFINER IS
BEGIN
  dbms_backup_restore.searchfiles(pattern, ns);
END expfiles;
/

GRANT select ON expose_files TO uwclass;
GRANT execute ON expfiles TO uwclass;

conn uwclass/uwclass

set serveroutput on

DECLARE
 TYPE file_rec IS RECORD (file_name dbms_sql.varchar2_table);
 frec file_rec;

 CURSOR fcur IS
 SELECT file_name
 FROM sys.expose_files
 WHERE file_name LIKE '%.%';

 dirname VARCHAR2(1024) := 'c:\tmp';
 ns      VARCHAR2(1024);
BEGIN
  sys.expfiles(dirname, ns);

  OPEN fcur;
  FETCH fcur BULK COLLECT INTO frec;
  CLOSE fcur;

  FOR i IN 1 .. frec.file_name.COUNT LOOP
    dbms_output.put_line(frec.file_name(i));
  END LOOP;
END;
/
With only a few minutes of work it is possible to create a secure interface exposed to the application only through execute on a non-dynamic procedure and SELECT on a read-only view. My recommendation, when converting the final anonymous block into a usable package procedure is to define AUTHID CURRENT_USER.

The only negative I have found is that this procedure doesn't just look at the single directory named but also all of its subdirectories. That means that if you point it to too large a file source it may take a lot of resources and create a very large array. Keep that in mind as you look to implementation.
 
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved