oracle ORA-29283：无效的文件操作 ORA-06512：在“SYS.UTL_FILE”，第 536 行

ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 536

提问by Vivek

Below is the code i use to extract data from a table to a flat file.

下面是我用来从表中提取数据到平面文件的代码。

BEGIN
    DECLARE
        file_name VARCHAR2(50);
        file_handle utl_file.file_type;
BEGIN
    file_name := 'table.txt';
    file_handle := utl_file.fopen('SEND',file_name,'W');
FOR rec in(
    SELECT            column 1
                ||'~'||column 2
                ||'~'||column 3 out_line
    FROM table1)LOOP
UTL_FILE.PUT_LINE(file_handle,rec.out_line);
UTL_FILE.FFLUSH(file_handle);
END LOOP;
UTL_FILE.FCLOSE(file_handle);
END;
end;

This code is working fine in our development database but its throwing the below error if i execute in a new DB.

这段代码在我们的开发数据库中运行良好，但如果我在新数据库中执行，它会抛出以下错误。

Error starting at line 1 in command:
    BEGIN
    DECLARE
        file_name VARCHAR2(50);
        file_handle utl_file.file_type;
BEGIN
    file_name := 'table.txt';
    file_handle := utl_file.fopen('SEND',file_name,'W');
FOR rec in(
    SELECT            column 1
                ||'~'||column 2
                ||'~'||column 3 out_line
    FROM table1)LOOP
UTL_FILE.PUT_LINE(file_handle,rec.out_line);
UTL_FILE.FFLUSH(file_handle);
END LOOP;
UTL_FILE.FCLOSE(file_handle);
END;
end;

Error report:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 7
29283. 00000 -  "invalid file operation"
*Cause:    An attempt was made to read from a file or directory that does
           not exist, or file or directory access was denied by the
           operating system.
*Action:   Verify file and directory access privileges on the file system,
           and if reading, verify that the file exists.

---

---

Oracle directory 'SEND' points to some UNIX directory which has rights as 
       'rwxrwsr-x' (Octal 2775)
Oracle Version:11g

Please help me to solve this issue.

请帮我解决这个问题。

Guys please do let me know if you require more data from me to solve this question.

伙计们，如果您需要我提供更多数据来解决这个问题，请告诉我。

采纳答案by APC

So, @Vivek has got the solution to the problem through a dialogue in the Comments rather than through an actual answer.

因此，@Vivek 通过评论中的对话而不是通过实际答案获得了问题的解决方案。

"The file is being created by user oraclejust noticed this in our development database. i'm getting this error because, the directory where i try to create the file doesn't have write access for othersand user oraclecomes under otherscategory. "

“该文件是由用户创建的，oracle只是在我们的开发数据库中注意到了这一点。我收到此错误，因为我尝试创建文件的目录没有写入权限，others而用户oracle属于others类别。”

Who says SO is a Q&A site not a forum? Er, me, amongst others. Anyway, in the absence of an accepted answer to this question I proffer a link to an answer of mine on the topic of UTL_FILE.FOPEN(). Find it here.

谁说 SO 是问答网站而不是论坛？呃，我，等等。无论如何，在没有对这个问题接受的答案的情况下，我提供了一个链接，指向我关于UTL_FILE.FOPEN(). 在这里找到它。

P.S. I'm marking this answer Community Wiki, because it's not a proper answer to this question, just a redirect to somewhere else.

PS我将此答案标记为社区维基，因为它不是这个问题的正确答案，只是重定向到其他地方。

回答by kuldeep

Assume file is already created in the predefined directory with name "table.txt"

假设文件已经在名为“ table.txt”的预定义目录中创建

• 1) change the ownership for file :

  sudo chown username:username table.txt
  

• 2) change the mode of the file

  sudo chmod 777 table.txt
  

• 1）更改文件的所有权：

  sudo chown username:username table.txt
  

• 2）改变文件的模式

  sudo chmod 777 table.txt
  

Now, try it should work!

现在，尝试它应该有效！

回答by Ayush Pratap Singh

I had been facing this problem for two days and I found that the directory you create in Oracle also needs to created first on your physical disk.

我已经面临这个问题两天了，我发现您在 Oracle 中创建的目录也需要先在您的物理磁盘上创建。

I didn't find this point mentioned anywhere i tried to look up the solution to this.

我在尝试查找此问题的解决方案的任何地方都没有发现这一点。

Example

例子

If you created a directory, let's say, 'DB_DIR'.

如果您创建了一个目录，比如说“ DB_DIR”。

CREATE OR REPLACE DIRECTORY DB_DIR AS 'E:\DB_WORKS';

Then you need to ensure that DB_WORKSexists in your E:\drive and also file system level Read/Write permissions are available to the Oracle process.

然后您需要确保DB_WORKS您的E:\驱动器中存在，并且文件系统级别的读/写权限可用于 Oracle 进程。

My understanding of UTL_FILE from my experiences is given below for this kind of operation.

对于这种操作，我根据我的经验对 UTL_FILE 的理解如下。

UTL_FILE is an object under SYS user. GRANT EXECUTE ON SYS.UTL_FILE TO PUBLIC; needs to given while logged in as SYS. Otherwise, it will give declaration error in procedure. Anyone can create a directory as shown:- CREATE OR REPLACE DIRECTORY DB_DIR AS 'E:\DBWORKS'; But CREATE DIRECTORY permission should be in place. This can be granted as shown:- GRANT CREATE ALL DIRECTORY TO user; while logged in as SYS user. However, if this needs to be used by another user, grants need to be given to that user otherwise it will throw error. GRANT READ, WRITE, EXECUTE ON DB_DIR TO user; while loggedin as the user who created the directory. Then, compile your package. Before executing the procedure, ensure that the Directory exists physically on your Disk. Otherwise it will throw 'Invalid File Operation' error. (V. IMPORTANT) Ensure that Filesystem level Read/Write permissions are in place for the Oracle process. This is separate from the DB level permissions granted.(V. IMPORTANT) Execute procedure. File should get populated with the result set of your query.

UTL_FILE 是 SYS 用户下的对象。将 SYS.UTL_FILE 上的 EXECUTE 授权给 PUBLIC；需要在以 SYS 身份登录时给出。否则会在程序中出现声明错误。任何人都可以创建如下所示的目录：- CREATE OR REPLACE DIRECTORY DB_DIR AS 'E:\DBWORKS'; 但是 CREATE DIRECTORY 权限应该到位。这可以授予如下所示：- GRANT CREATE ALL DIRECTORY TO user; 以 SYS 用户身份登录时。但是，如果这需要由另一个用户使用，则需要向该用户授予授权，否则会引发错误。授予用户读取、写入、执行 DB_DIR 权限；以创建目录的用户身份登录时。然后，编译你的包。在执行该过程之前，请确保该目录实际存在于您的磁盘上。否则会抛出“无效文件操作”错误。(V. 重要）确保文件系统级别的读/写权限适用于 Oracle 进程。这与授予的 DB 级别权限是分开的。（V. 重要）执行过程。文件应填充查询的结果集。

回答by Khamyl

On Windows also check whether the file is not encrypted using EFS. I had the same problem untill I decrypted the file manualy.

在 Windows 上，还要检查文件是否未使用 EFS 加密。在我手动解密文件之前，我遇到了同样的问题。