Oracle - 使用存储过程创建文本文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9565316/
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
Oracle - Create text file using stored procedure
提问by user1249603
Need help on creating text file using stored procedure in oracle 10. I'm very new to this so please guide me from the start.
在使用 oracle 10 中的存储过程创建文本文件方面需要帮助。我对此很陌生，所以请从一开始就指导我。
I found code and created it as stored procedure. I run it and it is VALID. But I executed it, I got error of "INVALID FILE NAME... AT SYS.UTL_FILE..."
我找到了代码并将其创建为存储过程。我运行它，它是有效的。但我执行了它，我得到了错误"INVALID FILE NAME... AT SYS.UTL_FILE..."
I first created a directory that grants read, write on directory.
我首先创建了一个目录，该目录授予对目录的读取、写入权限。
CREATE OR REPLACE PROCEDURE refcursorkim IS
l_file utl_file.file_type;
l_file_name VARCHAR2(60);
BEGIN
l_file := utl_file.fopen('UTL_DIR', l_file_name, 'w');
SELECT 'KY' || TO_CHAR(SYSDATE, 'yyyymmdd') || '1.txt' INTO l_file_name FROM dual;
utl_file.putf(l_file,l_file_name);
utl_file.fclose(l_file);
END refcursorkim;
I don't know what I'm missing, since I'm not familiar to this at all. Another thing, is the file automatically being created in the directory specified?
我不知道我错过了什么，因为我根本不熟悉这个。另一件事，是在指定的目录中自动创建文件吗？
Hope you could help me. Thank you and God bless!
希望你能帮助我。谢谢你，上帝保佑！
回答by Guru
You should open the file with proper file name.
您应该使用正确的文件名打开文件。
Your code (will error out):
您的代码（会出错）：
CREATE OR REPLACE PROCEDURE refcursorkim
IS
l_file UTL_FILE.file_type;
l_file_name VARCHAR2 (60);
BEGIN
l_file := UTL_FILE.fopen ('UTL_DIR', l_file_name, 'w');
SELECT 'KY' || TO_CHAR (SYSDATE, 'yyyymmdd') || '1.txt'
INTO l_file_name
FROM DUAL;
UTL_FILE.putf (l_file, l_file_name);
UTL_FILE.fclose (l_file);
END refcursorkim;
Should be (Corrected):
应该是（更正）：
CREATE OR REPLACE PROCEDURE refcursorkim
IS
l_file UTL_FILE.file_type;
l_file_name VARCHAR2 (60);
BEGIN
SELECT 'KY' || TO_CHAR (SYSDATE, 'yyyymmdd') || '1.txt'
INTO l_file_name
FROM DUAL;
l_file := UTL_FILE.fopen ('UTL_DIR', l_file_name, 'w');
UTL_FILE.putf (l_file, l_file_name);
UTL_FILE.fclose (l_file);
END refcursorkim;
More here: http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_file.htm#i1004619
更多信息：http: //docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_file.htm#i1004619
Edit 1:
编辑1：
- A new file will be created if the file did not exist. If exists, it would replace with a new file. If you need preserve the file, open with
'A'append mode. - There is no option within UTL_FILE to browse the directory content. However, there are other options in Oracle 11g. Which version of Oracle are you in? (I will also give you an inside secret that will help you to learn Oracle standard library functions quick. Open the UTL_FILE package specification. They provide nice documentation with enough comments on what is done in the procedure call)
- 如果文件不存在，将创建一个新文件。如果存在，它将替换为一个新文件。如果您需要保留文件，请使用
'A'附加模式打开。 - UTL_FILE 中没有用于浏览目录内容的选项。但是，Oracle 11g 中还有其他选项。您使用的是哪个版本的 Oracle？（我还会给你一个内部秘密，帮助你快速学习 Oracle 标准库函数。打开 UTL_FILE 包规范。他们提供了很好的文档，对过程调用中所做的事情有足够的评论）
What is the error you get? Can you paste the code you are using and what you get? A full run detail?
你得到的错误是什么？你能粘贴你正在使用的代码以及你得到了什么吗？完整的运行细节？
