CODEWITHSUNDEEP
oracle-databasepermissionsdirectory
lexeme
lexeme

Reputation: 2973

Can't write to a file. Oracle

I'm trying to execute these lines:

DECLARE
    V_FILEHANDLE UTL_FILE.FILE_TYPE;
BEGIN
    V_FILEHANDLE := UTL_FILE.FOPEN('C:\samples', '1.csv', 'w');
    UTL_FILE.PUT_LINE(V_FILEHANDLE, 'sample string');
    UTL_FILE.FCLOSE_ALL;
END;

Previously I've successfully executed these statements:

create directory sample as 'C:\samples';

(though I can't find the directory on the C:\ drive?) But this gives me an output like:

ORA-29280: "invalid directory path"

*Cause: A corresponding directory object does not exist.

*Action: Correct the directory object parameter, or create a corresponding directory object with the CREATE DIRECTORY command.

Also I've tried to grant previleges to my username:

grant read, write on directory sample to brick;

But this gives me an output like

ORA-01749: you may not GRANT/REVOKE privileges to/from yourself

What am I doing wrong?

Upvotes: 0

Views: 4540

Answers (2)

Squall85
Squall85

Reputation: 1

Thank you SOOO MUCH! I found my error. I was creating the directory like

create or REPLACE DIRECTORY dat_dir as '/u01/oracle/Desktop/Migration/Data';

Then I used it in my UTL_FILE like this

file1 := utl_file.fopen('dat_dir','output.txt','w');

I've been trying this to work for 3 days now. I found the problem, since it's in quotation mark, it's a case sensitive string. It doesn't matter if initially I tiped dat_dir, oracle stores things like this in uppercase. Your simple answer helped me realize this, thank you a million times!

The right way to do it was: 

file1 := utl_file.fopen('DAT_DIR','output.txt','w');

Upvotes: 0

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

Replace 

V_FILEHANDLE := UTL_FILE.FOPEN('C:\samples', '1.csv', 'w');

with :

V_FILEHANDLE := UTL_FILE.FOPEN('SAMPLE', '1.csv', 'w');

As indicated in the doc, the first parameter is the directory object name.

In older versions of Oracle, the first parameter of UTL_FILE.open used to be the directory path, but this has been deprecated since the introduction of the DIRECTORY object (in 9i?).

Upvotes: 1

Related Questions