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<fileList.length; 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 |
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10640957/viewspace-2121687/
，如需转载，请注明出处，否则将追究法律责任。
