CODEWITHSUNDEEP
sqloracle-databasefiledirectory
user3102664
user3102664

Reputation: 119

Oracle DB create directory

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)

Upvotes: 5

Views: 37618

Answers (1)

XING
XING

Reputation: 9886

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

Upvotes: 9

Related Questions