CODEWITHSUNDEEP
oracle-database
Julio Orrego
Julio Orrego

Reputation: 33

Oracle Schema cannot access Directory

I've been trying for a while now to access an oracle DB directory I have created. I've granted all permissions to the schema I want to access the directory from, but still Im getting the same error: "ORA-29280:invalid directory path".

I've checked the directory multiple time on my server, and it's everything as it should be, carefully checked, and still getting the same error.

I try to execute this example code from the schema I need to create the file from, to create a file in my directory:

  DECLARE
  l_file utl_file.file_type;
BEGIN
  l_file := utl_file.fopen( 'DIRECTORIO_DATO_EXT', 'example_file.txt', 'W' );
  utl_file.put_line( l_file, 'Example file.' );
  utl_file.fclose( l_file );
END;

And the same error appears.

But when I execute the very same code from the SYS user as SYSDBA, the procedure completes without error, and the file is created in the server.

Please note that I've added ALL privileges to the user I want to access the directory from:

GRANT ALL ON DIRECTORY DIRECTORY_DATO_EXT TO MY_SCHEMA;
GRANT READ ON DIRECTORY DIRECTORY_DATO_EXT TO MY_SCHEMA;
GRANT WRITE ON DIRECTORY DIRECTORY_DATO_EXT TO MY_SCHEMA;
GRANT CONNECT ON DIRECTORY DIRECTORY_DATO_EXT TO MY_SCHEMA;

Also, from the server OS I've granted all permissions to the folder where the directory is pointing, and even tried to add the oracle user to the root user group to see if that worked, but still nothing.

I'll appreciate any help.

Regards.

Upvotes: 1

Views: 888

Answers (1)

Julio Orrego
Julio Orrego

Reputation: 33

Ok, so I just found out what was my problem:

I created a DBLink a few days ago, and updated ALL the synonyms from the main database so I could access my DBLink. When I did that , I did it for ALL synonyms, included UTL_FILE synonym, resulting in something like this:

CREATE OR REPLACE SYNONYM MY_SCHEMA.UTL_FILE FOR SYS.UTL_FILE@DBLink;

And as it turned out, the "@DBlink" was the problem, cause it was pointing at a specific schema (not sys), making my procedure unable to find the UTL_FILE utility.

Hope this helps someone in the future!

Upvotes: 0

Related Questions