解决“以非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)ASV_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文件在最后加上**重启数据库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> declare2 fhandle utl_file.file_type;3begin4 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);8end;9/PL/SQL procedure successfully completed.
读取文件:
SQL> declare2 fhandle utl_file.file_type;3 fp_buffer varchar2(4000);4begin5 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); 12end; 13/
查看directory.
SQL> select * from dba_directories;
删除路径.
SQL> drop directory exp_dir;Directory dropped
页:
[1]