sql - Oracle 数据库创建目录

Question

我正在尝试创建目录是 SQL Developer 并使用以下代码在那里创建一个简单的文本文件:

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;

但它以那个错误结束

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.

在 SQL 开发人员中，目录变量已创建并可见

所以，我的问题是那段代码是应该自己创建目录还是我必须手动创建它？ (我无权访问服务器文件系统)

Answer 1

看起来 GRANTS 丢失了。

创建目录:

CREATE OR REPLACE DIRECTORY alias AS 'pathname';

地点:

alias is the name of the directory alias.

pathname is the physical directory path.

授予:

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

地点:

权限是以下之一:

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. 

编辑:

目录检查:

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

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

更改目录权限。默认情况下，它对其他人只有读取和执行权限。

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.

然后我看到创建的文件:

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