解决“以非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

阅读更多
文章标签: FP SQL Oracle C C++
上一篇仅仅换了种说法---周末读故事
下一篇源码下载站列表
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭