oracle proedure 文件_Oracle存储过程获取操作系统目录下文件列表(转)

1.创建临时表create global temporary table dirlist(

filename varchar2(255),

filesize number,

filedate date)

on commit delete

rows;

/

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

2.创建JAVA SOURCE过程

此过程用户访问操作系统目录并读取文件列表.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)CREATE OR REPLACE PROCEDURE GETDIRLIST(i_directory IN VARCHAR2)

AS

LANGUAGE JAVA NAME

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

4.执行p_get_dir_list过程获取数据

用SYS执行：SQL> exec dbms_java.grant_permission( 'TEST',

'SYS:java.io.FilePermission', 'F:/xmltest', 'read' );

PL/SQL procedure

successfully completed

SQL> exec dbms_java.grant_permission( 'TEST',

'SYS:java.io.FilePermission', 'F:/xmltest\*', 'read' );

PL/SQL procedure

successfully completed

此处需要用SYS用户执行dbms_java.grant_permission分别授权目录和目录文件列表(*代替即可)访问权限给指定用户。然后执行：SQL> SQL> exec GETDIRLIST(‘F:/xmltest’);

PL/SQL procedure successfully

completed

5.查询文件列表信息SQL> col FILESIZE for a20

SQL> col FILENAME for a40

SQL> col

FILEDATE for a30

SQL>  select * from dirlist;

FILENAME                                                                           FILESIZE FILEDATE

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

people.log                                                                                0 2012/5/6 9:

people.xml                                                                              842 2012/5/6 8:

T_PRINTCLOBOUT - 副本.XML                                                              1443 2012/5/6 11

T_PRINTCLOBOUT.XML                                                                      278 2012/5/6 15

新建 Microsoft Office Word 文档.docx                                                      0 2012/5/6 14

people.log                                                                                0 2012/5/6 9:

people.xml                                                                              842 2012/5/6 8:

T_PRINTCLOBOUT - 副本.XML                                                              1443 2012/5/6 11

T_PRINTCLOBOUT.XML                                                                      278 2012/5/6 15

新建 Microsoft Office Word 文档.docx                                                      0 2012/5/6 14

people.log                                                                                0 2012/5/6 9:

people.xml                                                                              842 2012/5/6 8:

T_PRINTCLOBOUT - 副本.XML                                                              1443 2012/5/6 11

T_PRINTCLOBOUT.XML                                                                      278 2012/5/6 15

新建 Microsoft Office Word 文档.docx                                                      0 2012/5/6 14

15 rows selected