We have several existing Oracle Directories set up to allow reading CSV files that work fine, and a couple of them work OK to Write new files. I have been trying to add a new Directory definition pointing to a different path and cannot get it to work. I am in a corporate environment where I don't have access to the System accounts and cannot see the instance startup file, and don't have direct access to the Linux operating system, so I don't know what setup has been done for the previous Directories.
One of the existing Directories that works for both read and write is defined as:
CREATE OR REPLACE DIRECTORY RED AS '/red/dev';
for the above directory, the following test code works fine to create an output file:
DECLARE
v_file UTL_FILE.FILE_TYPE;
BEGIN
v_file := UTL_FILE.FOPEN(location => 'RED',
filename => 'test.csv',
open_mode => 'w',
max_linesize => 32767);
UTL_FILE.PUT_LINE(v_file, 'A,123');
UTL_FILE.FCLOSE(v_file);
END;
I want to write some files to a subdirectory under the above path, and have found that Oracle will only allow WRITE to a named-Oracle Directory for security reasons. A new Directory I want to create is defined as:
CREATE OR REPLACE DIRECTORY RED_OUTPUT AS '/red/dev/OUTPUT';
But changing the code above to use RED_OUTPUT as the "location" or directory, results in "ORA-29283: invalid file operation: cannot open file".
The '/red/dev/OUTPUT' directory location exists on the external NAS filesystem and appears to have the same permissions as the parent '/red/dev' directory (as best I can tell by using Windows Explorer to look at the directory security properties).
I have read various posts online indicating things like the Oracle instance must be restarted after defining a new Oracle Directory, or that every path specified by an Oracle Directory must have a separate "mount point" on the Oracle Linux server, but I don't have easy access to do those things. The RED_OUTPUT directory can be currently used to READ an existing file if I copy one to that location using Windows Explorer.
What is likely the issue with not being able to WRITE to this new RED_OUTPUT directory, and are any of these additional steps (restart, mounting, etc) necessary to make this work?