ITPub博客

首页 > Linux操作系统 > Linux操作系统 > UTL_FILE包的简单例子

UTL_FILE包的简单例子

原创 Linux操作系统 作者:fengzj 时间:2008-12-29 12:23:35 0 删除 编辑
开发人员要实现一个自动将表中数据导出到一个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的步骤。

SQL> CREATE DIRECTORY D_OUTPUT AS 'E:';

目录已创建。

SQL> CREATE OR REPLACE PROCEDURE P_WRITE_EMP AS
2 V_FILE UTL_FILE.FILE_TYPE;
3 V_BUFFER VARCHAR2(32767);
4 BEGIN
5 V_FILE := UTL_FILE.FOPEN('D_OUTPUT', 'EMP' || TO_CHAR(SYSDATE, 'YYYY_MM_DD') || '.csv', 'w', 32767);
6 V_BUFFER := 'EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO';
7 UTL_FILE.PUT_LINE(V_FILE, V_BUFFER);
8 FOR I IN
9 (
10 SELECT '"' || EMPNO || '","' ||
11 ENAME || '","' ||
12 JOB || '","' ||
13 MGR || '","' ||
14 HIREDATE || '","' ||
15 SAL || '","' ||
16 COMM || '","' ||
17 DEPTNO || '"' RESULT
18 FROM EMP
19 ) LOOP
20 UTL_FILE.PUT_LINE(V_FILE, I.RESULT);
21 END LOOP;
22 UTL_FILE.FCLOSE(V_FILE);
23 END;
24 /

过程已创建。

SQL> EXEC P_WRITE_EMP

PL/SQL 过程已成功完成。

例子很简单,这里就不多做解释了,最后给出文件EMP2006_09_06.csv的内容:

EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
"7369","SMITH","CLERK","7902","1980-12-17 00:00:00","960","","20"
"7499","ALLEN","SALESMAN","7698","1981-02-20 00:00:00","1920","300","30"
"7521","WARD","SALESMAN","7698","1981-02-22 00:00:00","1500","500","30"
"7566","JONES","MANAGER","7839","1981-04-02 00:00:00","3570","","20"
"7654","MARTIN","SALESMAN","7698","1981-09-28 00:00:00","1500","1400","30"
"7698","BLAKE","MANAGER","7839","1981-05-01 00:00:00","3420","","30"
"7782","CLARK","MANAGER","7839","1981-06-09 00:00:00","2940","","10"
"7788","SCOTT","ANALYST","7566","1982-12-09 00:00:00","3600","","20"
"7839","KING","PRESIDENT","","1981-11-17 00:00:00","6000","","10"
"7844","TURNER","SALESMAN","7698","1981-09-08 00:00:00","1800","0","30"
"7876","ADAMS","CLERK","7788","1983-01-12 00:00:00","1320","","20"
"7900","JAMES","CLERK","7698","1981-12-03 00:00:00","1140","","30"
"7902","FORD","ANALYST","7566","1981-12-03 00:00:00","3600","","20"
"7934","MILLER","CLERK","7782","1982-01-23 00:00:00","1560","","10"

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/219982/viewspace-524542/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-11-11

  • 博文量
    76
  • 访问量
    177175