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