Oracle-使用存储过程创建文本文件 [英] Oracle - Create text file using stored procedure

问题描述

需要有关在oracle 10中使用存储过程创建文本文件的帮助.我对此非常陌生，因此请从头开始指导我.

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.

我找到了代码并将其创建为存储过程.我运行它，它是有效的.但是我执行了它，出现了错误"INVALID FILE NAME... AT SYS.UTL_FILE..."

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..."

我首先创建了一个目录，该目录允许对目录进行读写.

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!

推荐答案

您应使用正确的文件名打开文件.

You should open the file with proper file name.

您的代码(将出错):

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;

应(更正):

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;

此处更多: http://docs.oracle .com/cd/B19306_01/appdev.102/b14258/u_file.htm#i1004619

1. 如果该文件不存在，将创建一个新文件.如果存在，它将替换为新文件.如果需要保留文件，请使用'A'附加模式打开.
2. UTL_FILE中没有选项来浏览目录内容.但是，Oracle 11g中还有其他选项.您使用的是哪个版本的Oracle? (我还将向您提供一个内部秘密，它可以帮助您快速学习Oracle标准库功能.打开UTL_FILE软件包规范.它们提供了不错的文档，并对过程调用中的操作提供了足够的注释)

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.
2. 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)

您得到什么错误?您可以粘贴正在使用的代码以及所获得的内容吗?完整的细节?

What is the error you get? Can you paste the code you are using and what you get? A full run detail?

这篇关于Oracle-使用存储过程创建文本文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！