UTL_FILE.FOPEN()过程不接受目录路径? [英] UTL_FILE.FOPEN() procedure not accepting path for directory?

问题描述

我正在尝试写入名为vin1.txt的c:\驱动器中存储的文件 并收到此错误.请提出建议！

I am trying to write in a file stored in c:\ drive named vin1.txt and getting this error .Please suggest!

> 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

这里是代码

  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

推荐答案

自Oracle 9i起，有两种方法或声明用于UTL_FILE的目录.

Since Oracle 9i there are two ways or declaring a directory for use with UTL_FILE.

较旧的方法是设置INIT.ORA参数UTL_FILE_DIR.我们必须重新启动数据库才能使更改生效.该值可以像其他任何PATH变量一样；它接受通配符.使用这种方法意味着传递目录路径...

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...

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');

不赞成使用UTL_FILE_DIR，因为它本质上是不安全的-所有用户都可以访问路径中指定的所有OS目录，而读写权限可以离散地授予单个用户.另外，借助Directory对象，我们可以添加，删除或更改目录，而无需启动数据库.

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.

在任何一种情况下，oracle OS用户必须在OS目录上具有读取和/或写入特权.如果不太明显，这意味着该目录必须在数据库服务器上可见.因此，我们无法使用这两种方法将本地PC上的目录公开给在远程数据库服务器上运行的进程.文件必须上传到数据库服务器或共享的网络驱动器.

In either case, the oracle OS 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 OS用户在OS目录上没有适当的特权，或者数据库中指定的路径与实际路径不匹配，则程序将抛出此异常:

If the oracle OS 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:

ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 7

此错误的OERR文本非常清楚:

The OERR text for this error is pretty clear:

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.

这篇关于UTL_FILE.FOPEN()过程不接受目录路径?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！