oracle 在目录中查找文件列表的问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4181128/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
problem in finding list of files in directory
提问by Amir
I want to retrieve list of all file in a specific folder that included oracle form and menu and report and some txt file...
我想检索包含 oracle 表单、菜单和报告以及一些 txt 文件的特定文件夹中所有文件的列表...
Do you have any idea how I can retrieve these data in ORACLE form, and insert them into my data block, automatically?
您知道如何以 ORACLE 形式检索这些数据，并将它们自动插入到我的数据块中吗？
I use oracle form 6.0.
我使用oracle form 6.0。
回答by 0xdb
There is another interesting approach with external tables that makes it even easier to retrieve such lists without using a Java stored procedure:
还有另一种有趣的外部表方法，可以更轻松地检索此类列表，而无需使用 Java 存储过程：
$ mkdir /tmp/incoming
$ cat >/tmp/incoming/readdir.sh<<eof
#/bin/bash
cd /tmp/incoming/
/bin/ls -1
eof
# test files
$ for i in {1..5}; do touch /tmp/incoming/invoice_no_$RANDOM.pdf; done
In SQL*Plus:
在 SQL*Plus 中：
create or replace directory incoming as '/tmp/incoming';
Directory INCOMMING created.
create table files (filename varchar2(255))
organization external (
type oracle_loader
default directory incoming
access parameters (
records delimited by newline
preprocessor incoming:'readdir.sh'
fields terminated by "|" ldrtrim
)
location ('readdir.sh')
);
/
Table FILES created.
select * from files;
FILENAME
--------------------------------------------------------------------------------
FILES_27463.log
invoice_no_20891.pdf
invoice_no_2255.pdf
invoice_no_24086.pdf
invoice_no_30372.pdf
invoice_no_8340.pdf
readdir.sh
7 rows selected
This approach was added in the same Ask Tom threadas mentioned in the @DCookie's answere.
这种方法是在@DCookie 的回答中提到的同一个Ask Tom 线程中添加的。
回答by DCookie
I did something along these lines:
我做了这些事情：
Create an Oracle directory for the directory you want to list:
为要列出的目录创建一个 Oracle 目录：
create or replace directory YOURDIR
as '\path\to\your\directory';
Build a temporary table:
建立临时表：
create global temporary table DIR_LIST
(
FILENAME VARCHAR2(255),
)
on commit preserve rows;
grant select, insert, update, delete on DIR_LIST to PUBLIC;
You'll need a java stored procedure:
你需要一个java存储过程：
create or replace and compile java source named dirlist as
import java.io.*;
import java.sql.*;
import java.text.*;
public class DirList
{
public static void getList(String directory)
throws SQLException
{
File dir = new File( directory );
File[] files = dir.listFiles();
File theFile;
for(int i = 0; i < files.length; i++)
{
theFile = files[i];
#sql { INSERT INTO DIR_LIST (FILENAME)
VALUES (:theName };
}
}
}
And a PL/SQL callable procedure to invoke the java:
还有一个 PL/SQL 可调用过程来调用 java：
CREATE OR REPLACE PROCEDURE get_dir_list(pi_directory IN VARCHAR2)
AS LANGUAGE JAVA
name 'DirList.getList(java.lang.String)';
Finally, calling the procedure get_dir_list inside your form will populate the table with the files in your directory, which you can then read into your form block.
最后，在表单中调用过程 get_dir_list 将使用目录中的文件填充表，然后您可以将这些文件读入表单块。
The java code came straight out of a Tom Kyte book (don't recall which one).
Java 代码直接来自 Tom Kyte 的书（不记得是哪一本了）。
EDIT:
编辑：
Actually, all the code is pretty much lifted from this AskTom thread.
实际上，所有代码几乎都是从这个AskTom 线程中提取的。
