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

User · Answer

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.

User · Answer

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

User · Answer

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      Who says SO is a Q amp 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   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

User · Answer

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

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

Example

Examples related to oracle

Examples related to plsql

Examples related to oracle11g

Examples related to ora-06512