ORACLE 11G UTL_FILE + UTL_FILE_DIR - 无效目录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13607854/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORACLE 11G UTL_FILE + UTL_FILE_DIR - Invalid Directory
提问by Robert Gallow
Hi I am running Oracle 11 and I am trying to write to a directory on the server box using UTL_FILE.FOPEN.
嗨，我正在运行 Oracle 11，我正在尝试使用 UTL_FILE.FOPEN 写入服务器盒上的目录。
To test this I am using the following script (output included):
为了测试这一点，我使用了以下脚本（包括输出）：
SQL> @D:\test.sql
declare
l_file_handle UTL_FILE.FILE_TYPE;
begin
l_file_handle := UTL_FILE.FOPEN('/appl/mydir',
'/appl/mydir/filename',
'W');
UTL_FILE.FCLOSE(l_file_handle);
DBMS_OUTPUT.PUT_LINE('FINISHED');
end;
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at line 7
SQL>
I have the /appl/mydir added to the UTL_FILE parameter:
我将 /appl/mydir 添加到 UTL_FILE 参数中：
SELECT value
FROM V$PARAMETER
WHERE NAME = 'utl_file_dir';
/appl/mydir, /appl/mydir2, /appl/mydir3
The UNIX directory is writable by all on UNIX:
UNIX 目录在 UNIX 上可供所有人写入：
$ ls -l /appl/mydir
total 0
drwxrwsrwx 2 user userc 363968 Nov 27 13:46 mydir
Using oracle Directory object is not an option and I am aware of the disadvantages of using the UTL_FILE_DIR implementation.
使用 oracle Directory 对象不是一个选项，我知道使用 UTL_FILE_DIR 实现的缺点。
Any ideas why the above script is failing?
任何想法为什么上述脚本失败？
回答by DazzaL
first, in 11g the preferred way is to create a directory and not use utl_file.
首先，在 11g 中首选的方法是创建一个目录而不是使用 utl_file。
secondly, please verify what exact command you used to set the directoty list :
其次，请验证您用于设置目录列表的确切命令：
SELECT value
FROM V$PARAMETER
WHERE NAME = 'utl_file_dir';
/appl/mydir, /appl/mydir2, /appl/mydir3
was it
是吗
alter system set utl_file_dir='/appl/mydir, /appl/mydir2, /appl/mydir3' scope = spfile;
or
或者
alter system set utl_file_dir='/appl/mydir','/appl/mydir2','/appl/mydir3' scope = spfile;
if its the first way, redo it again the 2nd way as the first way is wrong (it will look the same in the v$table output, but its wrong).
如果是第一种方式，则以第二种方式再次重做，因为第一种方式是错误的（它在 v$table 输出中看起来相同，但它是错误的）。
eg:
例如：
declare
2
l_file_handle UTL_FILE.FILE_TYPE;
4
begin
l_file_handle := UTL_FILE.FOPEN('/tmp/foo/a',
'/tmp/foo/a/filename.txt',
'w');
9
UTL_FILE.FCLOSE(l_file_handle);
11
DBMS_OUTPUT.PUT_LINE('FINISHED');
13
end;
15 /
declare
*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at line 6
SQL> show parameter utl_fil
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string /tmp/foo, /tmp/foo/a
humm. now lets fix that data.
嗯。现在让我们修复这些数据。
SQL> alter system set utl_file_dir='/tmp/foo','/tmp/foo/a' scope = spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open
ORACLE instance started.
Total System Global Area 263049216 bytes
Fixed Size 2225584 bytes
Variable Size 176163408 bytes
Database Buffers 79691776 bytes
Redo Buffers 4968448 bytes
Database mounted.
Database opened.
declare
2
l_file_handle UTL_FILE.FILE_TYPE;
4
begin
l_file_handle := UTL_FILE.FOPEN('/tmp/foo/a',
'/tmp/foo/a/filename.txt',
'w');
9
UTL_FILE.FCLOSE(l_file_handle);
11
DBMS_OUTPUT.PUT_LINE('FINISHED');
13
end;
15 /
PL/SQL procedure successfully completed.
SQL> show parameter utl_file
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string /tmp/foo, /tmp/foo/a
SQL>
also verify the oracle user has r+x on /appl and rwx on /appl/mydir
还要验证 oracle 用户在 /appl 上有 r+x，在 /appl/mydir 上有 rwx
回答by Ajith Sasidharan
2 resolution steps for overcoming this error::
克服此错误的 2 个解决步骤：
1.Be sure that the OS level user has permission to write to a folder
1.确保操作系统级用户具有写入文件夹的权限
2.Try upper case letters for the directory name i.e. instead of out_dir use OUT_DIR
2.尝试使用大写字母作为目录名称，即使用 OUT_DIR 代替 out_dir
回答by Nagendra Nigade
Out of space may also lead to this issue.
空间不足也可能导致此问题。
Oracle throws same error if you do not have space on mount point.
如果挂载点上没有空间，Oracle 会抛出相同的错误。
ORA-29280: invalid directory path ORA-06512: at "SYS.UTL_FILE", line 41
ORA-29280：无效的目录路径 ORA-06512：在“SYS.UTL_FILE”，第 41 行
Please check whether space is available or not on utl_file_dir mountpoint.
请检查 utl_file_dir 挂载点上是否有可用空间。
For Linux & Solaris use :
对于 Linux 和 Solaris 使用：
df -k ${UTIL_FILE_PATH}
I faced same issue , later got to know it was space issue on mount point.
我遇到了同样的问题，后来才知道是挂载点的空间问题。
回答by user5545519
For the filename put just the name not the path. So you should put
对于文件名，只输入名称而不是路径。所以你应该把
declare
l_file_handle UTL_FILE.FILE_TYPE;
begin
l_file_handle := UTL_FILE.FOPEN('/appl/mydir',
',filename',
'W');
UTL_FILE.FCLOSE(l_file_handle);
DBMS_OUTPUT.PUT_LINE('FINISHED');
end;
