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 · Accepted 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

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

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

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

Examples related to oracle

Examples related to plsql

Examples related to oracle11g

Examples related to ora-06512