plsql CSV文件读写

Question

OWNER   SYS
DIRECTORY_NAME  ME
DIRECTORY_PATH  \\172.16.20.11\Mad\


begin

vSFile := utl_file.fopen('ME','20170405.csv','R');
IF utl_file.is_open(vSFile) THEN
LOOP

我收到错误消息：

ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 536 ORA-29283: invalid file operation ORA-06512: at "MADHUR.MSP_UPD_DAILYSALESFRMSAP", line 28 ORA-06512: at line 1
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.

Answer 1

出现此问题的原因是您没有对directory的read或write权限。

运行以下查询，查看您是否具有read和write权限：

 SELECT * 
      FROM all_tab_privs 
     WHERE table_name = 'your_directory name';      

如果您发现没有任何访问权限，则授予读取和写入权限。

SQL>CREATE OR REPLACE DIRECTORY dir1 as '/opt/oracle/';

SQL>GRANT READ,WRITE on dir1 to <Required user>; (if you want to give access to particular user)
OR
SQL>GRANT READ,WRITE on dir1 to PUBLIC; (if you want to give access to all users then give access to public)

Answer 2

您的错误准确地告诉您问题是什么：

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

因此，您可以指定：

DIRECTORY_PATH \\172.16.20.11\Mad\

您是否能够通过您的oracle用户实际访问\\172.16.20.11\Mad\？

如果没有，那么您需要grant read, write on directory to user并检查您的用户对该路径的OS权限。

但也考虑做一个网络共享驱动器号，而不是UNC路径。