Adding new directory to all_directories on oracle fails?

Question

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

1991DBA · Accepted Answer · 2019-02-05 15:21:41Z

1

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

answered Feb 5, 2019 at 15:21

1991DBA

8051 gold badge9 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kresten Over a year ago

Thanks. It's server and not the database user that needs privileges to edit the file. Just as Tom describes in the answer. Now it works.

1991DBA Over a year ago

Happy I could be of assistance, glad you got it figured out! :)

Collectives™ on Stack Overflow

Adding new directory to all_directories on oracle fails?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related