Oracle DB create directory

Question

I am trying to create directory is SQL Developer and create a simple text file there using this code :

CREATE DIRECTORY ABC AS '/abc';


    DECLARE
      fileHandler UTL_FILE.FILE_TYPE;
    BEGIN
      fileHandler := UTL_FILE.FOPEN('ABC', 'test_file.txt', 'W');
      UTL_FILE.PUTF(fileHandler, 'Writing to a file\n');
      UTL_FILE.FCLOSE(fileHandler);
    END;

But it ends up with that Error

29283. 00000 -  "invalid file operation"
*Cause:    An attempt was made to read from a file or directory that does
           not exist, or file or directory access was denied by the
           operating system.
*Action:   Verify file and directory access privileges on the file system,
           and if reading, verify that the file exists.

In SQL developer the directory variable is created and visible

So, my question is Does that piece of code suppose to create directory by itself or do i have to create it manually? (I don't have access to the servers file system)

According to the documentation, FOPEN will create the file if it doesn't exist. However, it can only create files in directories where it has write access. Have you tried providing the complete file path? — waka
– waka, Commented Jan 23, 2018 at 9:56
but the directory /abc doesn't exits so i was wondering if CREATE DIRECTORY ABC AS '/abc'; will create one or is it only making a variable pointing to directory that should be created manually — user3102664
– user3102664, Commented Jan 23, 2018 at 9:59
@user3102664 It would create one at the server where database is mounted. You can check that in data dictionary dba_directories — XING
– XING, Commented Jan 23, 2018 at 10:05

XING · Accepted Answer · 2018-01-23 10:36:31Z

9

It looks like GRANTS are missing.

To create a directory:

CREATE OR REPLACE DIRECTORY alias AS 'pathname';

Where:

alias is the name of the directory alias.

pathname is the physical directory path.

To Grant:

GRANT permission ON DIRECTORY alias TO {user | role | PUBLIC};

Where:

permission is one of the following:

READ for read-only access

WRITE for write-only access

ALL for read and write access

alias is the name of the directory alias.

user is a database user name.

Edit:

Directory Check:

SQL> SELECT DIRECTORY_NAME , DIRECTORY_PATH FROM DBA_DIRECTORIES WHERE DIRECTORY_NAME = 'BDUMP';

DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ---------------
BDUMP                          /home/fil_test/

Change directory permission. By default it has only Read and execute permission for others.

terminal$ chmod 777 fil_test

Block:

DECLARE
   fHandle   UTL_FILE.FILE_TYPE;
BEGIN
   fHandle := UTL_FILE.FOPEN ('BDUMP', 'test_file', 'w');

   UTL_FILE.PUT (fHandle, 'This is the first line');
   UTL_FILE.PUT (fHandle, 'This is the second line');
   UTL_FILE.PUT_LINE (fHandle, 'This is the third line');

   UTL_FILE.FCLOSE (fHandle);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE (
         'Exception: SQLCODE=' || SQLCODE || '  SQLERRM=' || SQLERRM);
      RAISE;
END;
/
Execution:

SQL> @tt.sql

PL/SQL procedure successfully completed.

And i See the file created:

terminal$ ls -lrt test_file*
-rw-r-----   1 oracle   dba           68 Oct 24 14:49 test_file

edited Jan 23, 2018 at 10:36

answered Jan 23, 2018 at 10:00

XING

9,9264 gold badges25 silver badges40 bronze badges

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

4 Comments

user3102664 Over a year ago

Ok so i Did this -GRANT WRITE ON DIRECTORY ABC TO SOME_USER; -Grant succeeded. But the effect of earlier query is same

XING Over a year ago

can you see your directory in DBA_DIRECTORIES table.

user3102664 Over a year ago

Yes sir, Owner SYS

user3102664 Over a year ago

Thanks that was that! Love it

Collectives™ on Stack Overflow

Oracle DB create directory

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related