oracle里面目录下,oracle 获取操作系统目录下文件列表

-- Oracle存储过程获取操作系统目录下文件列表

-- 1). 创建临时表

eygle@SZTYORA> create global temporary table dirlist(

filename varchar2(255),

filesize number,

filedate date)

on commit delete rows;

/

-- 创建临时表保存文件列表信息。此处使用临时表的好处是：循环取文件列表时无需对表进行清空处理。

-- 2). 创建JAVA SOURCE过程

-- 此过程用户访问操作系统目录并读取文件列表.

eygle@SZTYORA> 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[] fileList=path.list();

String fileName;

long fileSize;

long fileDate;

for (int i=0;i

{

fileName=fileList[i];

File fpath=new File(directory+'/'+fileName);

fileSize=fpath.length();

fileDate=fpath.lastModified();

#sql{insert into dirlist(filename, filesize, filedate)

values(:fileName,:fileSize,to_date('01/01/1970','mm/dd/yyyy')+:fileDate/(24*60*60*1000))};

}

}

}

/

-- 3). 创建ORACLE存储过程

-- 此过程用于调用和执行JAVA存储过程(DirList)

eygle@SZTYORA> create or replace procedure get_dir_list( p_directory in varchar2 )

as language java

name 'DirList.getList( java.lang.String )';

/

-- 4). 执行p_get_dir_list过程获取数据

eygle@SZTYORA> exec get_dir_list('/u01/app/oracle/admin/sztyora/bdump');

会出现以下错误：ERROR at line 1:

ORA-29532: Java call terminated by uncaught Java exception: java.security.AccessControlException: the Permission

(java.io.FilePermission /u01/app/oracle/admin/sztyora/bdump read) has not been granted to EYGLE. The PL/SQL to grant this is

dbms_java.grant_permission( 'EYGLE', 'SYS:java.io.FilePermission', '/u01/app/oracle/admin/sztyora/bdump', 'read' )

ORA-06512: at "EYGLE.GET_DIR_LIST", line 1

ORA-06512: at line 1

-- 执行时出错，提示无权限。这里耽误了很久，搜索了很多关于这个错误的帖子，都未提到解决办法。

-- 后来根据这个错误提示，用SYS执行：

sys@SZTYORA> exec dbms_java.grant_permission( 'EYGLE', 'SYS:java.io.FilePermission', '/u01/app/oracle/admin/sztyora/bdump', 'read' );

PL/SQL procedure successfully completed

sys> exec dbms_java.grant_permission( 'EYGLE', 'SYS:java.io.FilePermission', '/u01/app/oracle/admin/sztyora/bdump/*', 'read' );

PL/SQL procedure successfully completed

-- 此处需要用SYS用户执行dbms_java.grant_permission分别授权目录和目录文件列表(*代替即可)访问权限给指定用户。

-- 然后执行：

eygle@SZTYORA> exec get_dir_list('/u01/app/oracle/admin/sztyora/bdump');

PL/SQL procedure successfully completed5.查询文件列表信息

eygle@SZTYORA> col FILESIZE for 999999999999

eygle@SZTYORA> col FILENAME for a40

eygle@SZTYORA> col FILEDATE for a30

eygle@SZTYORA> select * from dirlist;

eygle@SZTYORA> select * from dirlist;

FILENAME                                      FILESIZE FILEDATE

---------------------------------------- ------------- ------------------------------

sztyora_lgwr_4299.trc                             1152 2010-07-16 06:01:08

sztyora_lgwr_5136.trc                              864 2010-07-20 00:24:30

sztyora_mmon_7121.trc                            48444 2010-07-14 06:11:42

sztyora_lgwr_15679.trc                             866 2010-07-13 06:28:29

sztyora_lgwr_4810.trc                             1374 2010-07-14 05:00:03

sztyora_lgwr_11407.trc                            1016 2010-07-12 08:44:36

sztyora_rvwr_3951.trc                              782 2010-07-21 00:29:22

sztyora_lgwr_3903.trc                             1014 2010-07-16 22:48:58

alert_sztyora.log                               219183 2010-07-21 00:29:33

sztyora_lgwr_4566.trc                             1180 2010-07-13 03:08:14

sztyora_lgwr_5443.trc                             1014 2010-07-19 08:17:55

sztyora_arc1_4597.trc                             2128 2010-07-13 03:06:16

sztyora_lgwr_5849.trc                             1071 2010-07-13 12:25:05

sztyora_lgwr_25369.trc                            1016 2010-07-16 23:59:23

sztyora_lgwr_5150.trc                             1014 2010-07-13 06:20:10

sztyora_arc0_7052.trc                            22373 2010-07-14 06:07:36

sztyora_arc0_4595.trc                              895 2010-07-13 03:07:15

sztyora_arc0_5184.trc                            18785 2010-07-13 06:17:26

sztyora_arc1_5186.trc                            34163 2010-07-13 06:16:26

sztyora_lgwr_4114.trc                             1152 2010-07-14 00:13:21

sztyora_lgwr_12266.trc                             866 2010-07-12 08:52:50

sztyora_lgwr_4695.trc                             1346 2010-07-15 05:40:37

sztyora_lgwr_5417.trc                             1042 2010-07-16 23:57:32

sztyora_lgwr_4617.trc                             1575 2010-07-14 15:27:18

sztyora_lgwr_3926.trc                             1002 2010-07-21 00:29:24

sztyora_arc1_7054.trc                            11264 2010-07-14 06:06:38

sztyora_lgwr_5950.trc                             1002 2010-07-15 13:39:58

27 rows selected.

注意：#sql{insert into dirlist(filename, filesize, filedate)

values(:fileName,:fileSize,to_date('01/01/1970','mm/dd/yyyy')+:fileDate/(24*60*60*1000))};

这里用了SQLJ语法。不用数据库连接，没有进行测试！