oracle Pl SQL从数据库目录中读取文本文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38359075/
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
Pl SQL reading text file from directory on database
提问by qaispak
I have to read a text file that I have created on the database. I am not sure what the appropriate path would be for the UTL_FILE.FOPENdirectory parameter. I am confused because the path is not on my local pc.
我必须阅读我在数据库上创建的文本文件。我不确定UTL_FILE.FOPEN目录参数的适当路径是什么。我很困惑，因为路径不在我的本地电脑上。
The path where I found this file is something like
我找到这个文件的路径类似于
\{something here}\winixdb$\{schema here I think}\dev\data
This is my current code.
这是我目前的代码。
Declare
f UTL_FILE.FILE_TYPE;
s VARCHAR2(200);
begin
f:= UTL_FILE.FOPEN({path_here}, 'certs_file.txt', 'R');
UTL_FILE.GET_LINE(f,s);
UTL_FILE.FCLOSE(f);
dbms_output.put_line(s);
end;
Right now if I put the exact path there it will be an error:
现在，如果我将确切的路径放在那里，则会出现错误：
ORA-2928: Invalid Directory Path
ORA-2928: Invalid Directory Path
I've tried looking at other resources but they talk about a path that is found on your local environment. Also, I don't quite understand the idea of a file on your database.
我试过查看其他资源，但他们谈论的是在您的本地环境中找到的路径。另外，我不太明白数据库中文件的概念。
I want to see this file be outputted to the DBMS.
我想看到这个文件被输出到 DBMS。
采纳答案by J. Chomel
You must first declare the directory you want to access {path_here}:
您必须首先声明要访问的目录{path_here}：
CREATE OR REPLACE DIRECTORY MY_PATH_ON_DBMS as '{path_here}';
(if it already exists, you should find it with select * from dba_directories;)
（如果它已经存在，你应该用 找到它select * from dba_directories;）
; make sure
oracleuser can read to it on the DBMS;
; 确保
oracle用户可以在 DBMS 上读取它；
Then call it by its name in your code:
然后在您的代码中按其名称调用它：
Declare
f UTL_FILE.FILE_TYPE;
s VARCHAR2(200);
begin
f:= UTL_FILE.FOPEN('MY_PATH_ON_DBMS', 'certs_file.txt', 'R');
UTL_FILE.GET_LINE(f,s);
UTL_FILE.FCLOSE(f);
dbms_output.put_line(s);
end;
