oracle UTL_FILE.FOPEN() 过程不接受目录路径？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2751113/
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
UTL_FILE.FOPEN() procedure not accepting path for directory?
提问by Vineet
I am trying to write in a file stored in c:\ drive named vin1.txt and getting this error .Please suggest!
我正在尝试写入存储在 c:\ 驱动器中名为 vin1.txt 的文件并收到此错误。请建议！
> ERROR at line 1: ORA-29280: invalid
> directory path ORA-06512: at
> "SYS.UTL_FILE", line 18 ORA-06512: at
> "SYS.UTL_FILE", line 424 ORA-06512: at
> "SCOTT.SAL_STATUS", line 12 ORA-06512:
> at line 1
HERE is the code
这里是代码
create or replace procedure sal_status
(
p_file_dir IN varchar2,
p_filename IN varchar2)
IS
v_filehandle utl_file.file_type;
cursor emp Is
select * from employees
order by department_id;
v_dep_no departments.department_id%TYPE;
begin
v_filehandle :=utl_file.fopen(p_file_dir,p_filename,'w');--Opening a file
utl_file.putf(v_filehandle,'SALARY REPORT :GENERATED ON %s\n',SYSDATE);
utl_file.new_line(v_filehandle);
for v_emp_rec IN emp LOOP
v_dep_no :=v_emp_rec.department_id;
utl_file.putf(v_filehandle,'employee %s earns:s\n',v_emp_rec.last_name,v_emp_rec.salary);
end loop;
utl_file.put_line(v_filehandle,'***END OF REPORT***');
UTL_FILE.fclose(v_filehandle);
end sal_status;
execute sal_status('C:\','vin1.txt');--Executing
回答by APC
Since Oracle 9i there are two ways or declaring a directory for use with UTL_FILE.
从 Oracle 9i 开始，有两种方法或声明一个用于 UTL_FILE 的目录。
The older way is to set the INIT.ORA parameter UTL_FILE_DIR. We have to restart the database for a change to take affect. The value can like any other PATH variable; it accepts wildcards. Using this approach means passing the directory path...
较旧的方法是设置 INIT.ORA 参数 UTL_FILE_DIR。我们必须重新启动数据库才能使更改生效。该值可以像任何其他 PATH 变量；它接受通配符。使用这种方法意味着传递目录路径...
UTL_FILE.FOPEN('c:\temp', 'vineet.txt', 'W');
The alternative approach is to declare a directory object.
另一种方法是声明一个目录对象。
create or replace directory temp_dir as 'C:\temp'
/
grant read, write on directory temp_dir to vineet
/
Directory objects require the exact file path, and don't accept wildcards. In this approach we pass the directory object name...
目录对象需要确切的文件路径，并且不接受通配符。在这种方法中，我们传递目录对象名称...
UTL_FILE.FOPEN('TEMP_DIR', 'vineet.txt', 'W');
The UTL_FILE_DIR is deprecated because it is inherently insecure - all users have access to all the OS directories specified in the path, whereas read and write privileges can de granted discretely to individual users. Also, with Directory objects we can be add, remove or change directories without bouncing the database.
UTL_FILE_DIR 已被弃用，因为它本质上是不安全的——所有用户都可以访问路径中指定的所有操作系统目录，而读写权限可以单独授予单个用户。此外，使用 Directory 对象，我们可以添加、删除或更改目录，而无需弹回数据库。
In either case, the oracleOS user must have read and/or write privileges on the OS directory. In case it isn't obvious, this means the directory must be visible from the database server. So we cannot use either approach to expose a directory on our local PC to a process running on a remote database server. Files must be uploaded to the database server, or a shared network drive.
在任何一种情况下，oracle操作系统用户都必须对操作系统目录具有读和/或写权限。如果不明显，这意味着该目录必须在数据库服务器上可见。因此，我们不能使用任何一种方法将本地 PC 上的目录暴露给在远程数据库服务器上运行的进程。文件必须上传到数据库服务器或共享网络驱动器。
If the oracleOS user does not have the appropriate privileges on the OS directory, or if the path specified in the database does not match to an actual path, the program will hurl this exception:
如果oracle操作系统用户对操作系统目录没有适当的权限，或者数据库中指定的路径与实际路径不匹配，程序将抛出此异常：
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 7
The OERR text for this error is pretty clear:
此错误的 OERR 文本非常清楚：
29283 - "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.
回答by Josh P
Don't forget also that the path for the file is on the actual oracle server machine and not any local development machine that might be calling your stored procedure. This is probably very obvious but something that should be remembered.
也不要忘记文件的路径在实际的 oracle 服务器机器上，而不是在任何可能调用您的存储过程的本地开发机器上。这可能非常明显，但应该记住。
回答by Jeffrey Kemp
You need to register the directorywith Oracle. fopen takes the name of a directory object, not the path. For example:
您需要向Oracle注册该目录。fopen 采用目录对象的名称，而不是路径。例如：
(you may need to login as SYS to execute these)
（您可能需要以 SYS 身份登录才能执行这些）
CREATE DIRECTORY MY_DIR AS 'C:\';
GRANT READ ON DIRECTORY MY_DIR TO SCOTT;
Then, you can refer to it in the call to fopen:
然后，您可以在对 fopen 的调用中引用它：
execute sal_status('MY_DIR','vin1.txt');
回答by sujini
For utl_file.open(location,filename,mode) , we need to give directory name for location but not path. For Example:DATA_FILE_DIR , this is the directory name and check out the directory path for that particular directory name.
对于 utl_file.open(location,filename,mode) ，我们需要为位置而不是路径提供目录名。例如：DATA_FILE_DIR，这是目录名称并检查该特定目录名称的目录路径。
回答by Shafqat Ali
The directory name seems to be case sensitive. I faced the same issue but when I provided the directory name in upper case it worked.
目录名称似乎区分大小写。我遇到了同样的问题，但是当我提供大写的目录名称时，它起作用了。
回答by Bob Jarvis - Reinstate Monica
You need to have your DBA modify the init.ora file, adding the directory you want to access to the 'utl_file_dir' parameter. Your database instance will then need to be stopped and restarted because init.ora is only read when the database is brought up.
您需要让您的 DBA 修改 init.ora 文件，将您要访问的目录添加到“utl_file_dir”参数中。然后需要停止并重新启动您的数据库实例，因为 init.ora 仅在数据库启动时读取。
You can view (but not change) this parameter by running the following query:
您可以通过运行以下查询来查看（但不能更改）此参数：
SELECT *
FROM V$PARAMETER
WHERE NAME = 'utl_file_dir'
Share and enjoy.
分享和享受。
