开发人员要实现一个自动将表中数据导出到一个csv格式文件的功能。建议他们使用UTL_FILE包加DBMS_JOB实现。最后给他们写了一个简单的UTL_FILE包使用的例子。
以前需要使用UTL_FILE包,必须设置utl_file_dir初始化参数,必须重启数据库才能使这个初始化参数修改生效。这成为使用UTL_FILE包最不方便的地方。
在Oracle9i中,增强了UTL_FILE包的功能,使得UTL_FILE包可以使用DIRECTORY,这就解决了重启数据库的问题。
注意,由于对目录的读写具有比较大的安全隐患,因此CREATE DIRECTORY权限只授权给DBA用户,而且创建了DIRECTORY后,对DIRECTORY的读写操作的权限也要DBA来授权。
本例中,当前用户拥有DBA角色,因此省略了GRANT READ, WRITE ON DIRECTORY的步骤。
CREATE
DIRECTORY PIC_DIR
AS
'
F:PIC_DIR
'
;
create or replace procedure P_WRITE_EMP is
v_file utl_file.file_type;
v_buffer varchar2 ( 32767 );
begin
v_file : = utl_file.fopen( ' PIC_DIR ' ,
' EMP ' || to_char(sysdate, ' yyyy-mm-dd ' ) ||
' .csv ' ,
' w ' ,
32767 );
v_buffer : = ' empno,ename,job,mgr,hiredate,sale,comm,deptno ' ;
utl_file.put_line(v_file, v_buffer);
for i in ( select empno || ' "," ' || ename || ' "," ' || job || ' "," ' || mgr ||
' "," ' || hiredate || ' "," ' || sal || ' "," ' || comm ||
' "," ' || deptno || ' "," ' result
from scott.emp) loop
utl_file.put_line(v_file, i.result);
end loop;
utl_file.fclose(v_file);
end P_WRITE_EMP;
create or replace procedure P_WRITE_EMP is
v_file utl_file.file_type;
v_buffer varchar2 ( 32767 );
begin
v_file : = utl_file.fopen( ' PIC_DIR ' ,
' EMP ' || to_char(sysdate, ' yyyy-mm-dd ' ) ||
' .csv ' ,
' w ' ,
32767 );
v_buffer : = ' empno,ename,job,mgr,hiredate,sale,comm,deptno ' ;
utl_file.put_line(v_file, v_buffer);
for i in ( select empno || ' "," ' || ename || ' "," ' || job || ' "," ' || mgr ||
' "," ' || hiredate || ' "," ' || sal || ' "," ' || comm ||
' "," ' || deptno || ' "," ' result
from scott.emp) loop
utl_file.put_line(v_file, i.result);
end loop;
utl_file.fclose(v_file);
end P_WRITE_EMP;
转自http://yangtingkun.itpub.net/post/468/204487
Oracle内建包UTL_FILE使用说明 参见http://www.blogjava.net/liwei/archive/2007/01/10/92902.aspx