Skip to Main Content
  • Questions
  • Requirements to set up an Oracle Directory for WRITE access

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Bill.

Asked: January 13, 2021 - 3:53 pm UTC

Last updated: January 14, 2021 - 5:51 am UTC

Version: Oracle RDBMS r20 for Linux

Viewed 100+ times

You Asked

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?

and we said...

I have read various posts online indicating things like the Oracle instance must be restarted after defining a new Oracle Directory


That is false.

that every path specified by an Oracle Directory must have a separate "mount point" on the Oracle Linux server


That is also false.

The most likely cause is a permissions on the directory at the OS level, especially if you can read by not write. If you didn't create the directory yourself, then make sure your DBA has done:

grant read, write on directory RED_OUTPUT to [your_user]

to ensure the Oracle privs are in place, but after that, its going to be an OS level issue.

More to Explore

PL/SQL

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