Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Yashasvi.

Asked: December 20, 2018 - 6:43 am UTC

Last updated: April 12, 2019 - 2:27 am UTC

Version: Oracle 12c

Viewed 10K+ times! This question is

You Asked

Hi Tom,

Thank you for being with us and for all your support over the years.

From last few days I am getting an error while writing file from my application i.e
Exception Info: ORA-29283: invalid file operation.
But if I run same procedure from SQL developer it works fine.

And this error is not pertaining with specific procedure. It could arise with any procedure.

Suppose my application invokes 50 procedures to write data into file one by one , there is no certainty that when this error will come, it could arise while invoking any procedure out of 50.

Earlier every thing was running fine, there is no problem of grants and OS level permissions,
I have cross checked and also procedures are running fine from Sql developer.


I have also tried to delay the process by 5 seconds between invoking of procedures but error still comes.

Please Suggest something.


and Chris said...

You get this because the database is unable to access the file for some reason. It's usually because of file permissions.

But if this is happening intermittently, it suggests that your processes are clobbering each others file access. e.g. one session deletes the file just before another tries to access it.

To give more meaningful help we need to see your code! With a description of how you're calling this to get an error.

For further reading, MOS note 746882.1 discusses various causes and solutions of ORA-29283:

Goal

This article will explain you the general scenario when ORA-29283 (INVALID FILE OPERATION) can be thrown from UTL_FILE package while accessing operating system file and how to debug the exception
Solution

The error can be raised because of the following causes.
General causes and solutions of ORA-29283 error:


1. Check whether the schema where the PLSQL code is run has READ,WRITE permission on the database directory.

If SCOTT is the schema , then login as SYS grant required permission.
GRANT READ,WRITE ON DIRECTORY <DIR_NAME> TO SCOTT



2. Check whether the path used in database directory physically exists on the OS.

Eg :
CREATE OR REPLACE DIRECTORY DIRNAME as '/home/test';


/home/test should exist on the OS. When the database directory is created , oracle doesnt check the validity of the directory path used. The validation of the path is done at the runtime. Hence it is necessary to check the file path on OS before the program is executed.

3. Check whether the owner of oracle process has enough permission on the OS directory where the file is accessed.
]$ ls -l /u01/app/oracle/product/10.2/db_1/bin/oracle
-rwsr-s--x 1 oracle oinstall 96725778 Jul 7 2008 /u01/app/oracle/product/10.2/db_1/bin/oracle

"oracle" is the owner of the oracle executable. oracle - user should have enough permission on the directory.

]$ su oracle
Password:
]$ whoami
oracle
]$touch /home/test/abc.txt


You should be able to create a file using touch command login as the owner of the oracle process.

4. Make sure you have restarted the listener once you have done any OS level changes to the directories accessed by RDBMS.
Once you have done any changes in OS level permission, you should always restart the listener so that it inherits the recently changed permission.

Else oracle will sometime raise a ora-29283 error.

5. Even though oracle-user has enough permissions on the directory, it may raise ora-29283 if the owner of the file is not oracle.

If the file is created with the rw-r-- permission and owned by another user, RDBMS wont be able to read the file. In such case you will have to change the permissions of the file to rw-rw--
chmod 750 <filename>



6. Using remote directories :

UTL_FILE package can access only server side files where the database instance is running. You cannot access client side files. If you are using UNIX system, then create a NFS mount of the client folder on the server . If on Windows platform then go through

Note 45172.1 : Running UTL_FILE on Windows NT

Note 1034188.6: INVALID_OPERATION Exception from UTL_FILE when Writing to/from Network Drive

Solution:

Start the Oracle service as a user who has the same permissions as SYSTEM, and also who has access to the shared directory.

7. Check ORA_NLS on application server :

If the ORA_NLSXX where XX is 32, 33 or 10 is set, it must be set before starting the database and on the client side too.

Note 77442.1 : ORA_NLS (ORA_NLS32, ORA_NLS33, ORA_NLS10) Environment Variables explained

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

For Oracle 18c

Joe, April 11, 2019 - 2:32 pm UTC

I got this error after upgrading From 12.1 to 18c. The directory_path used a symbolic link pointing to the physical directory on the server. After I changed the directory_path to actual physical directory, the error went away.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library