Archive for May, 2009

Say, you are in need of a directory listing from within the Oracle database similar to the one generated with Unix command ls. Your database doesn’t contain a JVM, so the use of some simple JAVA is out of the question. Well, starting from DB Release 10g there is the possibility of using the searchfiles procedure in package dbms_backup_restore. Though the spec isn’t wrapped, I’m not quite sure the use of this package is supported by Oracle, because it’s internally used by Oracle Recovery Manager ( RMAN ). But if this isn’t putting you off, and you don’t mind creating some objects within the sys schema, here is the code.

-- install with sys
-- create types
CREATE OR REPLACE TYPE v2_row AS OBJECT ( text varchar2(1024));
/
CREATE OR REPLACE TYPE v2_table AS TABLE OF v2_row;
/

-- create function
CREATE OR REPLACE FUNCTION list_files
( p_directory VARCHAR2
, p_recursive PLS_INTEGER := 0)
RETURN v2_table
IS
ns VARCHAR2(1024);
v_dir VARCHAR2(1024);
v_list v2_table := v2_table();
v_slash VARCHAR2(1) := '/' ;
v_dir_depth PLS_INTEGER;
BEGIN
IF instr(v_dir,'\') > 0 THEN
v_slash := '\';
END IF;
v_dir := p_directory||v_slash;
v_dir_depth := (LENGTH(v_dir) - LENGTH(REPLACE(v_dir,v_slash,'')))/LENGTH(v_slash);
  DBMS_BACKUP_RESTORE.SEARCHFILES(v_dir, ns);
  FOR i IN ( SELECT fname_krbmsft as name
, (LENGTH(fname_krbmsft) - LENGTH(REPLACE(fname_krbmsft,v_slash,'')))/LENGTH(v_slash) as dir_depth
FROM x$krbmsft
WHERE INSTR(fname_krbmsft,v_dir) > 0 )
LOOP
IF p_recursive = 0 THEN
IF v_dir_depth = i.dir_depth THEN
v_list.extend;
v_list(v_list.count) := v2_row(i.name);
END IF;
ELSIF p_recursive = 1 THEN
v_list.extend;
v_list(v_list.count) := v2_row(i.name);
END IF;
END LOOP;

RETURN v_list;
END list_files;
/
-- grants and private synonyms
GRANT EXECUTE ON V2_ROW TO FOO;
GRANT EXECUTE ON V2_TABLE TO FOO;
GRANT EXECUTE ON LIST_FILES TO FOO;
CREATE SYNONYM FOO.V2_ROW FOR V2_ROW;
CREATE SYNONYM FOO.V2_TABLE FOR V2_TABLE;
CREATE SYNONYM FOO.LS FOR LIST_FILES;

Connect with user FOO, and the following select statements should work:

-- no recursive listing of files in subdirectories:
SELECT * FROM TABLE ( ls('/var/log') );
-- recursive listing of files in subdirectories:
SELECT * FROM TABLE ( ls('/var/log',1) );

Some comments:

1. The ls function generates fully qualified path and file names within a given directory, without listing any subdirectories.
2. Default the ls function doesn’t recursively list files. The underlying searchfiles procedure though, lists files on subdirectories, independent of the value you provide for the "p_recursive" parameter. So, you better be careful in your choice of directory, or you exhaust all memory on the server.
3. You might get the idea of granting execute on dbms_backup_restore, select on the x$krbmsft table, and create the ls function in some other schema. I’m not in favor of granting execute privileges on the rman package to regular users. But if you want to follow this path, you should know that Oracle doesn’t allow you to grant select privileges on a fixed table. Instead, with sys, create a view on table x$krbmsft, and grant select privileges on this view.
4. When using the ls function in PL/SQL you may need to explicitly cast the nested table, like this:
   SELECT * FROM TABLE ( cast ( ls(‘/home/oracle’) as v2_table ) );
5. Don’t worry about concurrent sessions. The fixed table seems to act as a temporary table, each session only has access to its own result set.
6. Within the same session, the result set of a previous call of dbms_backup_restore.searchfiles doesn’t interfere with the current one. Apparently the first transaction the procedure performs is a delete of all existing rows.
7. If a given directory doesn’t exist, or oracle lacks the necessary permissions, no error is generated. So, the directories "/home/oracle-sun" or "/root" will generate a "no rows returned" message, but that’s all. You must, however, have parameter db_recovery_file_dest set, otherwise ORA-19801 is raised.

I managed to find some info on the internet in regard to the dbms_backup_restore.searchfiles procedure. I liked the information provided by Chris Poole ( he has a nice XUTL_FINDFILES package for free download ) and Birijan Maharjan., but couldn’t find any useful documentation of this procedure on Metalink though.