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

Breadcrumb

May 4th

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 1000+ 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 Connor 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.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

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

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library