Where can I access a file from my oracle db with UTL_FILE?

Question

I'm trying to read from a file with oracle db using UTL_FILE. I can't find a file location I have access to. Whenever I use this code:

DECLARE 
  F1 UTL_FILE.FILE_TYPE; 
BEGIN 
  F1 :=UTL_FILE.FOPEN('C:\TEMP','test_file.txt','R'); 
END;

I get: ORA-29280: invalid directory path

Why would that be?

Can I somehow make oracle show the location I have access to?

BR Kresten

Derviş Kayımbaşıoğlu · Accepted Answer · 2019-02-05 11:05:26Z

2

UTL_FILE.FOPEN uses DBA_DIRECTORIES.

SELECT * from ALL_DIRECTORIES

gives you defined and accessible DBA_DIRECTORIES.

You can create directory for your File operations

CREATE DIRECTORY File_Op_Dir AS '/u01/fileDir';
GRANT READ ON DIRECTORY File_Op_Dir TO <<user>>;
--IF you need write permission 
GRANT WRITE ON DIRECTORY File_Op_Dir TO <<user>>;

Then

 F1 := UTL_FILE.FOPEN('File_Op_Dir','u12345.tmp','R');

answered Feb 5, 2019 at 11:05

Derviş Kayımbaşıoğlu

30.9k4 gold badges55 silver badges79 bronze badges

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

Collectives™ on Stack Overflow

Where can I access a file from my oracle db with UTL_FILE?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related