ORA-29283 Using UTL_FILE On Windows

SYMPTOMS

Using UTL_FILE and attempting to perform a read or write on a file stored on a network resource on Windows using UNC notation like


\\myserver\myshare

fails with the following error:


ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 449
ORA-29283: invalid file operation
ORA-06512: at line 4
CAUSE


The reason for this error may be for various reasons such as permissions or the network resource is unavailable.


SOLUTION

1. Verify the user that the Oracle Database is started as and verify the permissions for this network resource to ensure that the correct permissions have been set on the share.

2. Login as the same user that the database is started as and test the resource availability and access to files on the share for both reading and writing.

3. Verify the UTL_FILE_DIR database parameter or the database directory object are setup correctly and referencing the correct network resource.


TIP: Using Mapped drives is not recommended as this resource is only available when the user that created it is logged into the server. For most Windows Servers hosting Databases, this is not the normal use case scenario.



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