第一步:以管理员用户登陆
如:conn sys/password@sid as sysdba
第二步:设置可操作目录
需要指定utl_file包可以操作的目录。在oracle 10g以前,可以用以下方法:
1、alter system set utl_file_dir='e:/utl' scope=spfile;
2、在init.ora文件中,配置如下:
UTL_FILE=E:/utl或者UTL_FILE_DIR=E:/utl
在oracle 10g中建议用以下方法配置:CREATE DIRECTORY utl AS 'E:/utl';
参见oracle online:
GRANT EXECUTE ON utl_file TO scott;
第四步:conn scott/tiger
就可以正常使用utl_file了。
具体使用实例:
create or replace procedure test_data_txt
IS
LOG_FILE UTL_FILE.FILE_TYPE;
CURR_TIME varchar2(25);
BEGIN
--打开日志文件
LOG_FILE := UTL_FILE.FOPEN('e:/utl','test.log','A');
--获取当前时间
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') into CURR_TIME from dual;
--输入信息
UTL_FILE.PUTF(LOG_FILE, '-- START_TIME: %s --/nAuthor=%s/n', CURR_TIME, 'Tom');
--输出缓存
UTL_FILE.FFLUSH(LOG_FILE);
--关闭
UTL_FILE.FCLOSE(LOG_FILE);
--UTL_FILE.FCLOSE_ALL;
end test_data_txt;
如:conn sys/password@sid as sysdba
第二步:设置可操作目录
需要指定utl_file包可以操作的目录。在oracle 10g以前,可以用以下方法:
1、alter system set utl_file_dir='e:/utl' scope=spfile;
2、在init.ora文件中,配置如下:
UTL_FILE=E:/utl或者UTL_FILE_DIR=E:/utl
在oracle 10g中建议用以下方法配置:CREATE DIRECTORY utl AS 'E:/utl';
参见oracle online:
In the past, accessible directories for the UTL_FILE
functions were specified in the initialization file using the UTL_FILE_DIR
parameter. However, UTL_FILE_DIR
access is not recommended. It is recommended that you use the CREATE DIRECTORY
feature, which replaces UTL_FILE_DIR
. Directory objects offer more flexibility and granular control to the UTL_FILE
application administrator, can be maintained dynamically (that is, without shutting down the database), and are consistent with other Oracle tools. CREATE DIRECTORY
privilege is granted only to SYS
and SYSTEM
by default.
GRANT EXECUTE ON utl_file TO scott;
第四步:conn scott/tiger
就可以正常使用utl_file了。
具体使用实例:
create or replace procedure test_data_txt
IS
LOG_FILE UTL_FILE.FILE_TYPE;
CURR_TIME varchar2(25);
BEGIN
--打开日志文件
LOG_FILE := UTL_FILE.FOPEN('e:/utl','test.log','A');
--获取当前时间
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') into CURR_TIME from dual;
--输入信息
UTL_FILE.PUTF(LOG_FILE, '-- START_TIME: %s --/nAuthor=%s/n', CURR_TIME, 'Tom');
--输出缓存
UTL_FILE.FFLUSH(LOG_FILE);
--关闭
UTL_FILE.FCLOSE(LOG_FILE);
--UTL_FILE.FCLOSE_ALL;
end test_data_txt;