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

Question

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.

Rigorous

was the directory SEND in the new DB created? Does the DB-user in the new DB have READ and WRITE rights on the DB-DIRECTORY object? – Snowslide

Have you tried an explicit path for 'SEND'? Who owns the folder? If you change the permissions to: rwxrwsrwx, does it work? – Housemother

@schurik:Yes, directory is there in the new DB and it has READ and WRITE rights on the oracle directory. @forsvairr: Can we use explicit path instead of SEND? If we can then how? I thought oracle directory is the only way to specify the path. Cant change the permission of the directory, no access. This folder is owned by 'build' and user 'batch' will create file in this folder. Both users, 'build' and 'batch' are in same group – Rigorous

and what OS-user is the new DB-Instance running by, (the owner of oracle-processes)? Does he has access to the SEND-UNIX directory. – Snowslide

The file is being created by user 'oracle' just 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 'others' and user 'oracle' comes under 'others' category. Thank you guys for assisting me :) – Rigorous

possible duplicate of UTL_FILE.FOPEN() procedure not accepting path for directory ? – Pr

@scurik: Please post your comment as an answer. So that i can accept it which makes the question 'solved' – Rigorous

Answer 1

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

"The file is being created by user oracle just 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 others and user oracle comes under others category. "

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.

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.

Pr

+1, I don't quite understand the trend towards answering questions via comments. – Em

@Em - funnily enough I find myself doing it a lot these days, because there are so many questions of low quality and/or duplicates which should not get an actual answer. I provide some kind of solution in a comment as well as voting to close (and maybe downvoting too), so the Seeker get what they want but there's a vague attempt to follow the site bylaws. – Pr

Answer 2

Assume file is already created in the predefined directory with name "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
  

Now, try it should work!

Tecu

Whatever you are hoping to accomplish, chmod 777 is wrong and dangerous. You will want to revert to sane permissions ASAP (for your use case, probably chmod 755) and if you have had world writable system files on a public-facing system, at the very least investigate whether it could have been breached and used as a pivot point for breaking into your organization’s network. – Woehick

Answer 3

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.

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

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

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

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

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.

Stochastic

Answer 4

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

Lanthanum

Answer 5

The ORA-29283: invalid file operation is also raised on utl_file.put if there is an attempt to write line longer than max_linesize in text mode. max_linesize is optional 4th parameter of utl_file.fopen function defaulting to 1024.

(My case was dumping CSV from within Oracle in Docker into file in host directory mapped as Docker volume and I was misleaded by this error for pretty significat time - I seeked cause in filesystem rights or volume mapping between Docker and host, actually it was so stupid cause.)

UPDATE: another occurence of same exception also happened on utl_file.fopen. The database rejected to create file even if the file did not exist before. The directory in which the attempt of file creation happened was mapped on Docker volume. It started to work if the zero-sized file was created on host machine in advance. Attempt to create file from within container (touch /dir/file) failed though. Perhaps some docker issue - it disappeared after restarting Docker Desktop.

Amandy

Answer 6

1. You need give permission by creating folder.

2. create or replace directory DINESH as '/home/oracle/DINESH/';

3. grant read, write on directory DINESH to public;

4. Simple PLSQL to open a file, -- write two lines into the file, -- and close the file declare

   fhandle utl_file.file_type;

begin

fhandle := utl_file.fopen(

              'DINESH'     -- File location
            , 'test_file.txt' -- File name
            , 'w' -- Open mode: w = write.
                );

utl_file.put(fhandle, 'Hello world!'|| CHR(10));

utl_file.put(fhandle, 'Hello again!');

 utl_file.fclose(fhandle);

exception

when others then

  dbms_output.put_line('ERROR: ' || SQLCODE     || ' - ' || SQLERRM);

  raise;

end;

5. test_file.txt file created in /home/oracle/DINESH.

Penthouse