Listing files in directory using PLSQL 用PLSQL列目录内容

Listing files in directory using PLSQL
使用PLSQL列目录内容

用3种方法列出目录中的文件列表


1. 使用DBMS_BACKUP_RESTORE程序包列出目录中的文件

Oracle 10g中, DBMS_BACKUP_RESTORE程序包提供了一个函数SEARCHFILE, 用这个函数可以列出目录中的文件

PROCEDURE SEARCHFILES
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 PATTERN                        VARCHAR2                IN/OUT
 NS                             VARCHAR2                IN/OUT
 CCF                            BOOLEAN                 IN     DEFAULT
 OMF                            BOOLEAN                 IN     DEFAULT
 FTYPE                          VARCHAR2                IN     DEFAULT

后4个参数含义不详
第1个参数为要查询的目录名:
Unix下只能是目录名, 以斜杠结束, 也可以不带, 比如

'/var/log'

或

'/var/log/'

Windows下可以带文件名通配符,比如

'C:\WINDOWS\Help\*.hlp'

举例:

conn / as sysdba
set pages 50000 line 130
set serveroutput on size unlimited
var v_pat varchar2(1000);
var v_ns varchar2(1000);
exec :v_pat := '/var/log'
exec dbms_backup_restore.searchfiles(:v_pat, :v_ns)
select fname_krbmsft as name from x$krbmsft;

NAME
----------------------------------------------------------------------------------------------------------------------------------
/var/log/rpmpkgs.1
/var/log/rpmpkgs
/var/log/sa/sa16
/var/log/sa/sar17
/var/log/sa/sa15
/var/log/sa/sa21
/var/log/sa/sa20
/var/log/sa/sa18
/var/log/sa/sa19
/var/log/sa/sa22
/var/log/sa/sar14
/var/log/sa/sar15
/var/log/sa/sar18
/var/log/sa/sa17
/var/log/sa/sa14
/var/log/sa/sar20
/var/log/sa/sar13
/var/log/sa/sar21
/var/log/sa/sar16
/var/log/sa/sar19
/var/log/rpmpkgs.4
/var/log/wtmp
/var/log/prelink.log
/var/log/Pegasus/install.log
/var/log/Xorg.0.log
/var/log/rpmpkgs.3
/var/log/wtmp.1
/var/log/mcelog
/var/log/dmesg
/var/log/scrollkeeper.log
/var/log/Xorg.0.log.old
/var/log/gdm/:0.log.2
/var/log/gdm/:0.log
/var/log/gdm/:0.log.1
/var/log/rpmpkgs.2

35 rows selected.

调用dbms_backup_restore.searchfiles后, 在内存表x$krbmsft中生成文件列表
扫描是递归的, 包括了该目录和该目录下的子目录. 因此如果目录很深文件很多, 会占用大量内存
不显示隐藏文件(Unix下是以点开头的)


参考:
29 May 2007 - Finding Files
Is there a way to read the names of a set of files with a given extension from a directory as if from a SQL cursor? (同上)
封装为程序包 The XUTL_FINDFILES package
Oracle database directory listing with ls function by Harry Dragstra



2. JAVA编程获取目录文件列表

见ASKTOM上的例子, reading files in a directory -- how to get a list of available files.

create global temporary table DIR_LIST ( filename varchar2(255) ) on commit delete rows;

create or replace
  and compile java source named "DirList"
as
import java.io.*;
import java.sql.*;

public class DirList
{
  public static void getList(String directory)
                     throws SQLException
  {
      File path = new File( directory );
      String[] list = path.list();
      String element;

      for(int i = 0; i < list.length; i++)
      {
        element = list[i];
        #sql { INSERT INTO DIR_LIST (FILENAME)
               VALUES (:element) };
      }
  }
}
/

create or replace procedure get_dir_list( p_directory in varchar2 )
  as language java
name 'DirList.getList( java.lang.String )';
/

exec get_dir_list( '/var/log' );

select * from dir_list where rownum < 10;

FILENAME
----------------------------------------------------------------------------------------------------------------------------------
dbexprm_screen3.tmp
.ICE-unix
dbexprm_screen3.tmp.bad
.X0-lock
uscreens
dbexprm_screen3.tmp.good
.X11-unix
dbexprm_screen3.tmp.delete
dbexprm_screen3.tmp.run

9 rows selected.

能显示出隐藏文件


3. 调用外部操作系统命令获得文件列表

使用DBMS_SCHEDULER调用操作系统命令生成文件列表文件, 再用UTL_FILE读取改列表文件, 比较麻烦

set serveroutput on size unlimited

begin
  dbms_scheduler.create_job(
    job_name => 'os_ls',
    job_type => 'executable',
    job_action => '/bin/sh',
    number_of_arguments => 2,
    comments => 'OS ls'
  );
  dbms_scheduler.set_job_argument_value(
    job_name => 'os_ls',
    argument_position => 1,
    argument_value => '-c'
  );
  dbms_scheduler.set_job_argument_value(
    job_name => 'os_ls',
    argument_position => 2,
    argument_value => 'ls -l /var/log/*.log >/tmp/os_list.txt'
  );
end;
/
exec dbms_scheduler.run_job('os_ls');

create or replace directory os_ls_dir as '/tmp';
declare
  l_file utl_file.file_type;
  l_text     varchar2(2000);
  l_line     number(10) := 1;
begin
  l_file := utl_file.fopen(upper('os_ls_dir'), 'os_list.txt', 'r');
  begin
    loop
      utl_file.get_line(l_file, l_text);
      dbms_output.put_line(l_text);
      l_line := l_line + 1;
    end loop;
  exception
    when no_data_found then
      null;
  end;
  utl_file.fclose(l_file);
end;
/

exec dbms_scheduler.drop_job('os_ls');
drop directory os_ls_dir;

-rw-r--r--  1 root root  40296 Jun 12 07:40 /var/log/Xorg.0.log
-rw-------  1 root root  14424 Jun  4  2008 /var/log/anaconda.log
-rw-------  1 root root      0 Jun 21 04:02 /var/log/boot.log
-rw-r--r--  1 root root 402720 Jun 22 04:02 /var/log/prelink.log
-rw-r--r--  1 root root  63438 Jun  4  2008 /var/log/scrollkeeper.log

PL/SQL procedure successfully completed.

参考:list contents of directory



-fin-