[SOLVED] Adding new directory to all_directories on oracle fails?

Adding new directory to all_directories on oracle fails?

I'm trying to add a new directory to all_directories. This first part went well I think, but when I'm trying to create a file in that dir I get:

ORA-29283: invalid file operation

This is my code:

DECLARE
  fHandle  UTL_FILE.FILE_TYPE;
BEGIN
  fHandle := UTL_FILE.FOPEN('TEMPKBU', 'test.txt', 'w');
  UTL_FILE.FCLOSE(fHandle);
END;

The DIRECTORY is created here:

SELECT * FROM all_directories WHERE  directory_name = 'TEMPKBU'

OWNER   DIRECTORY_NAME  DIRECTORY_PATH                          ORIGIN_CON_ID
SYS     TEMPKBU         \\kcdkfile03\UserData\Bruger\KBU\temp   0

And the user has write privileges:

SELECT * FROM all_tab_privs WHERE  table_name  = 'TEMPKBU'

GRANTOR GRANTEE TABLE_SCHEMA TABLE_NAME PRIVILEGE GRANTABLE HIERARCHY   COMMON  TYPE
SYS     KC_PROD SYS          TEMPKBU    EXECUTE   YES       NO          NO      DIRECTORY
SYS     KC_PROD SYS          TEMPKBU    READ      YES       NO          NO      DIRECTORY
SYS     KC_PROD SYS          TEMPKBU    WRITE     YES       NO          NO      DIRECTORY
KC_PROD PUBLIC  SYS          TEMPKBU    READ      NO        NO          NO      DIRECTORY
KC_PROD PUBLIC  SYS          TEMPKBU    WRITE     NO        NO          NO      DIRECTORY

So why is this not working? And how can I get a more detailed errormessage?

BR Kresten

Solution

That error usually happens if you are trying to: 1. Write to a directory that does not exist, or 2. Write to a directory where sufficient privileges have not been granted.

While you have granted write privileges through the database, what about the OS Oracle user? The OS user will also need to have r/w privileges on the directory at the operating system level, otherwise it will not be able to create files and will result in this error. To check, connect to the database server as the Oracle user (or whichever user was used to create the database,) navigate to that \\kcdkfile03\UserData\Bruger\KBU\temp directory, and try to create a file.

Here is a relevant AskTom article that might be helpful: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9538408200346950265