首页 » ORACLE » list files on a directory from oracle database into A temporary table

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
打赏

目前这篇文章有1条评论(Rss)评论关闭。

  1. Geoffrey Hofer | #1
    2011-12-21 at 05:36

    Hello there, just changed into aware of your blog via Google, and found that it is truly informative. I’m going to watch out for brussels. I’ll appreciate for those who continue this in future. Lots of other folks might be benefited out of your writing. Cheers!