ORA-29283: invalid file operation tips Oracle Error Tips by Stephanie F.

The Oracle docs note this on the ora-29283 error:

Internet sources offer information regarding ORA-29283 for PL/SQL version 7.3.4.0.0.  The symptoms of the problem are as follows

Reading a remote file using UTL_FILE.FOPEN on an OpenVMS Alpha box, were the remote file is located by using DECNET, causes the INVALID_FILE_OPERATION exception.

While if the UTL_FILE.FOPEN is used to append something to an existing remote file, a new version of the file is created.

There is no problem writing a remote file.

If this is similar to something you are experiencing, you may want to read further.  Here is the Testcase provided about ORA-29283:

1. On the OS run the following to define a system variable:
define/system MYDIR VMS"""user password"""::$1$DG:[SUPPORT.MYDIR]

2. In sqlplus create the directory object to map the logical directory:
Create or replace directory rtvms2 as 'MYDIR:';

3. Run now the following procedure to create the remote file:

SET SERVEROUT ON

declare
v_buff VARCHAR2(2000);
fhandle UTL_FILE.FILE_TYPE;
begin
dbms_output.put_line('WRITE');
fhandle:= UTL_FILE.FOPEN('MYDIR:','Report.txt','W');
UTL_FILE.put_line(fhandle,' Attempt to write');
UTL_FILE.FCLOSE(fhandle);
end;
/

SQL>
WRITE

PL/SQL procedure successfully completed.

SQL> HOST dir mydir:
Directory VMS"user password"::$1$DG:[SUPPORT.MYDIR]

REPORT.TXT;1 1/69 29-DEC-2005 10:23:58.39 (RWD,RWD,R,)

4. Now test the append to the remote file, this will create a new version of the file
declare
v_buff VARCHAR2(2000);
fhandle UTL_FILE.FILE_TYPE;
begin
dbms_output.put_line('APPEND');
fhandle:= UTL_FILE.FOPEN('MYDIR:','Report.txt','A');
UTL_FILE.put_line(fhandle,'attempt to append');

UTL_FILE.FCLOSE(fhandle);
end;
/


SQL>
APPEND

PL/SQL procedure successfully completed.

SQL> exit

dir mydir:
Directory VMS"user password"::$1$DG:[SUPPORT.MYDIR]

REPORT.TXT;2 1/69 29-DEC-2005 10:23:58.73 (RWD,RWD,R,)
REPORT.TXT;1 1/69 29-DEC-2005 10:23:58.39 (RWD,RWD,R,)

edit REPORT.TXT;1
Attempt to write
[End of file]

edit REPORT.TXT;2
attempt to append
[End of file]


5. And finally test the read operation on the remote file:

declare
v_buff VARCHAR2(2000);
fhandle UTL_FILE.FILE_TYPE;
begin
dbms_output.put_line('READ');
fhandle:= UTL_FILE.FOPEN('MYDIR:','Report.txt','R');
-- UTL_FILE.get_line(fhandle,v_buff);
UTL_FILE.FCLOSE(fhandle);
end;
/

SQL>
READ
declare
*
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 6

The reason that these errors are being thrown, including ORA-29283, is related to an OS issue, "elated to the access() C RTL in OpenVMS, which is used by the utl_file.fopen to know if the file exists or not. The access() returns success when the file is local, but for a remote file access() returns failure."  The ult_file.fopen fails when it attempts to read a remote file with ORA-29283, becaus it is using the access() routine, and "and it fails when appending to a remote file because it creates a brand new file even if the file specified exists"

The following solution ids given to resolve ORA-29283:

The cause of the problem lies in the access() OpenVMS C runtime library which does not work with remote files, so there is no fix for this from an Oracle point of view