list files on a directory from oracle database into A temporary table

需求:
列出一个目录里的的文件名，插入一个临时表中
分析：
因ORACLE 自带JVM，所以用JAVA PROCEDURE也可以实现，今天网上查了一下，发现了一个包也可以做这件事情，记录一下
实验:

[oracle@orazhang testfiles]$ ls
a.txt  b.txt  c.txt  d.txt  e.txt  f.txt  g.txt
[oracle@orazhang testfiles]$ pwd
/home/oracle/testfiles
[oracle@orazhang testfiles]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 9月 27 15:33:46 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
sys@ORCL> create  directory testfiles as '/home/oracle/testfiles';
Directory created.
create or replace procedure list_builder(p_dirname varchar2 ,p_tmptabname varchar2  default 'fileslist')
is
v_path VARCHAR2(1024);
v_ns varchar2(1024);
v_create varchar2(1000);
begin
	SELECT directory_path INTO v_path
  FROM dba_directories
  WHERE directory_name = p_dirname;
  dbms_backup_restore.searchfiles(v_path,v_ns);
  v_create:='create table '||p_tmptabname||' as select fname_krbmsft  filename from X$KRBMSFT';
 -- DBMS_OUTPUT.PUT_LINE(v_create);
  execute immediate v_create;
end;
sys@ORCL> col DIRECTORY_PATH for a30
sys@ORCL> select directory_path from dba_directories where directory_name='TESTFILES'
  2  ;
DIRECTORY_PATH
------------------------------
/home/oracle/testfiles
sys@ORCL> !ls /home/oracle/testfiles
a.txt  b.txt  c.txt  d.txt  e.txt  f.txt  g.txt
sys@ORCL> exec list_builder('TESTFILES','allfile');
PL/SQL procedure successfully completed.
sys@ORCL> select * from allfile;
FILENAME
------------------------------------------------------------------
/home/oracle/testfiles/e.txt
/home/oracle/testfiles/d.txt
/home/oracle/testfiles/g.txt
/home/oracle/testfiles/b.txt
/home/oracle/testfiles/a.txt
/home/oracle/testfiles/c.txt
7 rows selected.
sys@ORCL> exec list_builder('TESTFILES','anbob.allfile');
PL/SQL procedure successfully completed.
sys@ORCL> select * from anbob.allfile;
FILENAME
-----------------------------------------------------------------
/home/oracle/testfiles/f.txt
/home/oracle/testfiles/e.txt
/home/oracle/testfiles/d.txt
/home/oracle/testfiles/g.txt
/home/oracle/testfiles/b.txt
/home/oracle/testfiles/a.txt
/home/oracle/testfiles/c.txt
7 rows selected.
复制

note:

可以跟自己的需要，在procedure 中加参数，实现文件名的过滤，可以生成在批定的schema下，缺陷还是中文路径不支持，上次java试也是中文路径，真响应了我那句话,计算机就是人家英语的世界，如果有能发现解决中文问题可以回贴

java的实现，不在实验了上次做过，这次偷次懒，把tom大叔的copy一下,嘿嘿，偷懒的感觉还是很爽的...

ops$tkyte@8i> GRANT JAVAUSERPRIV to ops$tkyte
  2  /
Grant succeeded.
That grant must be given to the owner of the procedure..  Allows them to read
directories.
ops$tkyte@8i> create global temporary table DIR_LIST
  2  ( filename varchar2(255) )
  3  on commit delete rows
  4  /
Table created.
ops$tkyte@8i> create or replace
  2     and compile java source named "DirList"
  3  as
  4  import java.io.*;
  5  import java.sql.*;
  6
  7  public class DirList
  8  {
  9  public static void getList(String directory)
 10                     throws SQLException
 11  {
 12      File path = new File( directory );
 13      String[] list = path.list();
 14      String element;
 15
 16      for(int i = 0; i < list.length; i++)  
17      {  
18          element = list[i]; 
 19          #sql { INSERT INTO DIR_LIST (FILENAME) 
 20                 VALUES (:element) };  
21      }  
22  }  
23    
24  }  
25  /
 Java created. 
ops$tkyte@8i>
ops$tkyte@8i> create or replace
  2  procedure get_dir_list( p_directory in varchar2 )
  3  as language java
  4  name 'DirList.getList( java.lang.String )';
  5  /
Procedure created.
ops$tkyte@8i>
ops$tkyte@8i> exec get_dir_list( '/tmp' );
PL/SQL procedure successfully completed.
ops$tkyte@8i> select * from dir_list where rownum < 5;
FILENAME
------------------------------------------------------
data.dat
.rpc_door
.pcmcia
ps_data
复制