ORA-29283: Invalid File Operation
APPLIES TO: PL/SQL
PL/SQL - Version 126.96.36.199 and later
Information in this document applies to any platform.
***Checked for relevance on 16th May 2016***
Although the following Note illustrates one error it will discuss a few possible causes.
To run the sample, a directory alias needs to be created
SQL> CREATE DIRECTORY NOTE_TEST AS 'C:/FileStore'; Directory created.
The following anonymous block is used to generate the error in one of many ways which means that although the code may not match other code, the error generated can be the same.
SQL> DECLARE input_file utl_file.file_type; input_buffer varchar2(10000); BEGIN input_file := utl_file.fopen ('NOTE_TEST', 'TestFile.txt', 'r'); utl_file.get_line (input_file,input_buffer); utl_file.fclose(input_file); END; / DECLARE * ERROR at line 1: ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 475 ORA-29283: invalid file operation
CAUSE: UTL_FILE.FOPEN(), Oracle9i Supplied PL/SQL Packages and Types Reference Release 2 (9.2)
The error can be caused by one of the following four items:
Issue 1 - The directory referenced is incorrect.
Issue 2 - Privileges were not granted to the username to access the directory.
Issue 3 - Attempting to read from a file with no data.
Issue 4 - The input file has a linesize over 1000 bytes and that was not indicated in:
UTL_FILE.FOPEN(). The UTL_FILE.FOPEN is improprly set up. There is no max_linesize parameter in the UTL_FILE.FOPEN method in the provided code. If there is no max_linesize parameter specified for this optional parameter, a default value between 1 and 1000 is assumed. The max_linesize parameter is needed if the file line size size exceeds 1000 as indicated by the documentation of the UTL_FILE package is in:
Oracle9i Supplied PL/SQL Packages and Types Reference
Release 2 (9.2)
Part Number A96612-01
This function opens a file. You can specify the maximum line size and have a maximum of 50 files open simultaneously. See also "FOPEN_NCHAR Function".
location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2,
max_linesize IN BINARY_INTEGER)
FOPEN Function Parameters
location - Directory location of file
filename - File name, including extension (file type), without directory
path In Unix, the filename cannot end with /.
open_mode - Specifies how the file is opened. Modes include:
r--read text , w--write text, a--append text
If you try to open a file that does not exist using a value for open_mode, then the file is created in write mode.
Maximum number of characters per line, including the newline character, for this file. (minimum value 1, maximum value 32767). The default is approximately 1000 bytes.
SOLUTION: UTL_FILE Package, ORA-29283
Issue 1 Solution - Ensure the directory being reference does actually exist and ensure privilege are provided for access. Also, a Directory Alias should be used instead of the database parameter UTL_FILE_DIR which has been deprecated.
Issue 2 Solution - Grant the READ or WRITE privileges to the username accessing the DIRECTORY.
For the DIRECTORY setup and permissions reference the following Notes:
Note:206272.1 New Method of Using UTL_FILE in Oracle9i 9.2
Note:292961.1 ORA-29280: Invalid Directory Path With UTL_FILE.FOPEN
Issue 3 Solution - ensure that the file being read does exist and actually contains information to be read.
Issue 4 Solution - Supply a value for the max_linesize parameter to the UTL_FILE.FOPEN () method. An example follows and be sure the myfile.html file exists and contains data.
SQL> DECLARE input_file utl_file.file_type; input_buffer varchar2(32767); BEGIN input_file := UTL_FILE.FOPEN('NOTE_TEST', 'myfile.html','r',32767); utl_file.get_line (input_file,input_buffer); utl_file.fclose(input_file); END; / PL/SQL procedure successfully completed.
For detailed analysis of what is actually occurring at the OS level, the following Note discusses how to obtain a trace.
Note:1313404.1 Finding the Underlying Cause For "ORA-29283: invalid file operation" Error When Using the UTL_FILE Package.