今天集成调试中发现当以非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;