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

最新推荐文章于 2021-09-08 18:42:50 发布

ck017656

最新推荐文章于 2021-09-08 18:42:50 发布

阅读量474

点赞数

文章标签：数据库 java 操作系统

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