oracle生成文件包,【UTL_FILE】使用UTL_FILE包生成文件并写入数据

使用UTL_FILE包可以实现数据库向文件写入数据的功能。本文给出写出文件的基本方法。1.创建文件存放的目录ora10g@secdb /home/oracle$ mkdir secoolerora10g@secdb /home/oracle$ cd secooler/ora10g@secdb /home/oracle/secooler$ ls -ltrtotal 02.在数据库中创建DIRECTORYora10g@secdb /home/oracle/secooler$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on Wed Jun 15 21:06:09 2011Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionssys@ora10g> create or replace directory dir_secooler as '/home/oracle/secooler';Directory created.sys@ora10g> grant read, write on directory dir_secooler to public;Grant succeeded.此时dir_secooler创建成功，指向目录“/home/oracle/secooler”，并且任何用户都可以使用这个DIRECTORY。3.使用UTL_FILE包向文件中写入信息1)连接到sec用户sys@ora10g> conn sec/secConnected.2)准备PL/SQLdeclarev_f1 utl_file.file_type;beginv_f1 := utl_file.fopen('DIR_SECOOLER','secooler.dat','w');UTL_FILE.PUT_LINE(v_f1,'Secooler writes this message.');utl_file.fclose(v_f1);end;/3)执行PL/SQLsec@ora10g> declare2    v_f1 utl_file.file_type;3  begin4    v_f1 := utl_file.fopen('DIR_SECOOLER','secooler.dat','w');5    UTL_FILE.PUT_LINE(v_f1,'Secooler writes this message.');6    utl_file.fclose(v_f1);7  end;8  /PL/SQL procedure successfully completed.4)检查执行结果ora10g@secdb /home/oracle/secooler$ ls -ltrtotal 4.0K-rw-r--r-- 1 oracle oinstall 30 Jun 15 21:58 secooler.datora10g@secdb /home/oracle/secooler$ cat secooler.datSecooler writes this message.可见，secooler.dat文件已经创建成功，同时数据已经写入。4.使用UTL_FILE包创建问题注意在使用UTL_FILE包用到DIRECTORY数据库对象时，名字一定要大写，否则会遭遇“ORA-29280: invalid directory path”错误。sec@ora10g> declare2    v_f1 utl_file.file_type;3  begin4    v_f1 := utl_file.fopen('dir_secooler','secooler.dat','w');5    UTL_FILE.PUT_LINE(v_f1,'Secooler writes this message.');6    utl_file.fclose(v_f1);7  end;8  /declare*ERROR at line 1:ORA-29280: invalid directory pathORA-06512: at "SYS.UTL_FILE", line 33ORA-06512: at "SYS.UTL_FILE", line 436ORA-06512: at line 45.关于UTL_FILE包参考信息官方文档参考链接：http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_file.htm#BABDEJDH6.小结本文以实现使用UTL_FILE包生成文件并写入信息的简单功能为例展示了UTL_FILE包的魅力。以此为基础可以实现更多有价值的需求。Good luck.secooler11.06.15-- The End --