解决“以非sys用户使用CREATE OR REPLACE DIRECTORY”问题

今天集成调试中发现当以非sys用户使用CREATE OR REPLACE DIRECTORY一般会提示权限不足！需要GRANT ANY CREATE DIRECTORY TO MYUSER;

(
  P_QUERY_SQL IN VARCHAR2,
  P_FILE_DIR IN VARCHAR2,
  P_FILE_NAME IN VARCHAR2,
  P_COLUMN_LIST IN VARCHAR2 DEFAULT NULL
)
AS
  V_FILE UTL_FILE.FILE_TYPE;
  V_RESULT VARCHAR2(32767);
  C_RESULT SYS_REFCURSOR;
BEGIN
  /*Oracle 10g 版本*/
  V_RESULT:='CREATE OR REPLACE DIRECTORY D_OUTPUT AS '''||P_FILE_DIR||'''';
  execute immediate V_RESULT;
  V_FILE := UTL_FILE.FOPEN('D_OUTPUT', P_FILE_NAME,'W', 32767);

  /*Oracle 9i版本
  **前提是必须将utl_file_dir参数指定为*：
  **修改/pfile/init.ora文件在最后加上[utl_file_dir=*]
  **重启数据库
  V_FILE := UTL_FILE.FOPEN(P_FILE_DIR, P_FILE_NAME,'W',32767);
  */
  IF P_COLUMN_LIST IS NOT NULL THEN
    UTL_FILE.PUT_LINE(V_FILE, P_COLUMN_LIST);
  END IF;
  OPEN C_RESULT FOR P_QUERY_SQL;
  LOOP
    FETCH C_RESULT INTO V_RESULT;
    EXIT WHEN C_RESULT%NOTFOUND;
    UTL_FILE.PUT_LINE(V_FILE, V_RESULT);
  END LOOP;
  UTL_FILE.FCLOSE(V_FILE);
END;

另附：

CREATE DIRECTORY基本使用如下

目录创建

create or replace directory exp_dir as '/tmp';

读写权限授予特定用户

grant read, write on directory dir to myuser;

让我们看一个简单的测试:

SQL> create or replace directory UTL_FILE_DIR as 'C:/xxx'; Directory created. SQL> declare 2 fhandle utl_file.file_type; 3 begin 4 fhandle := utl_file.fopen('UTL_FILE_DIR', 'example.txt', 'w'); 5 utl_file.put_line(fhandle , 'write one'); 6 utl_file.put_line(fhandle , 'write two'); 7 utl_file.fclose(fhandle); 8 end; 9 / PL/SQL procedure successfully completed.

读取文件:

SQL> declare 2 fhandle utl_file.file_type; 3 fp_buffer varchar2(4000); 4 begin 5 fhandle := utl_file.fopen ('UTL_FILE_DIR','example.txt', 'R'); 6 7 utl_file.get_line (fhandle , fp_buffer ); 8 dbms_output.put_line(fp_buffer ); 9 utl_file.get_line (fhandle , fp_buffer ); 10 dbms_output.put_line(fp_buffer ); 11 utl_file.fclose(fhandle); 12 end; 13 /

查看directory.

SQL> select * from dba_directories;

删除路径.

SQL> drop directory exp_dir; Directory dropped