ORACLE 借助utl_file使用存储过程将oracle表中的数据导出成文本文件
ORACLE 借助utl_file使用存储过程将oracle表中的数据导出成文本文件
摘要：在使用数据库的过程中，我们经常会遇到一个问题：如何将数据按照想要的格式导出到一个文本文件中。
当然，我们可以通过编写一些应用程序或通过第三方的软件工具来解决这个问题，但是其效率和灵活性并不一定是最优的解决方案。
本文就探讨使用数据库管理系统自身的功能来实现以上的需求。
本文研究的实验环境为Oracle 9i数据库系统，操作系统为Sun Solaris 10。
关键词：Oracle数据库；数据导出；数据文件；导出方法
1.引言
数据库已经应用比较普遍，目前各类系统的开发几乎都离不开数据库管理系统的支撑，当前占数据库管理系统市场较大份额的有Oracle、DB2、SQL Server等。
在使用数据库的过程中，难免会有将数据表中的数据导出这样的需求，特别是一些企业应用需要经常性的提取一些业务数据作为分析决策的参考，而这些提取数据的需求又具有格式经常变化等特点，如果单纯依靠编写应用程序就会显得很麻烦，成本也较大。
第三方工具因为需要另外安装等不方便的原因，所以也很难满足要求。
因此需要利用数据库自身的工具或功能来实现数据表数据的提取。
2.本文约定的实验环境
Oracle 9i是由甲骨文（Oracle）公司出品的一款企业级数据库管理系统，目前最新的版本是11g，但9i 版本应用比较普遍，主要应用于电信、公安等行业的数据库管理。
Solaris 10 是由太阳（SUN）公司出品的一种UNIX操作系统，因为其稳定性及安全性较好，在一些大型的企业级应用中也比较广泛。
文中约定实验环境中的Oracle9i的服务名（SID）为example，其管理员用户（sys）的口令为passwd，普通用户的用户名为user，其口令为userpwd，在user用户方案下存在一个数据库表，其表结构如下：
create table test (
id number(1),
name varchar2(20),
address varchar2(20)
);
向表中插入一些初始化数据，内容如下：
insert into test values(1,'wanglp','sy');
insert into test values(2,'gaoliang','dl');
insert into test values(3,'fuyaxian','as');
insert into test values(4,'wangtong','bx');
insert into test values(5,'gaoqi','dd');
最终实验输出的文本文件的内容及格式如下：1|wanglp|sy
2|gaoliang|dl
3|fuyaxian|as
4|wangtong|bx
5|gaoqi|dd
3.实验过程
接下来将采用两种方法来实现数据输出到文本的功能。
方法一：使用Oracle SQL/Plus 的 Spool工具
第一步：建立脚本文件，在磁盘上建立一个以sql为扩展名的文本文件（script.sql），这里约定存放在“/export/home2/”路径下编辑内容如下：
set heading off
set echo off
set term off
set line 0
set pages 0
set feed off
spool /export/home2/expdata.txt
select id||'|'||name||'|'||address as newcloumn from test; spool off
set heading on
set echo on
set term on
set feed on
第二步：执行脚本，得到输出数据文件。
登录Oracle 的SQL/PLUS工具，并执行脚本文件。
sqlplus user/userpwd@example
SQL>@/export/home2/script
运行成功后，即可在/export/home2/目录下找到expdata.txt
文件。
* 说明：方法一比较简单，只要安装了Oracle 数据库的SQL/PLUS 客户端工具，就可以在本地客户端或者远程数据库服务器端生成数据文件。
方法二：使用Oracle 内置的函数包UTL_FILE
第一步：在SQL/PLUS 中使用sys 用户登录到数据库
sqlplus user/userpwd@example
SQL>conn sys/passwd@example as sysdba
第二步：设置输出目录
SQL> create or replace directory TMP as '/export/home2/';
第三步：授权user 用户对该目录的访问权限
SQL> grant read,write on directory TMP to user;
第四步：使用user 用户登录到数据库
SQL> conn user/userpwd;
第五步：建立Oracle 数据库程序包及程序包体（或存储过程），这里把程序包及包体的内容存储到/export/home2/pro.sql 文件中。
--建立程序包
create or replace package wlptest AS
procedure START_OUT;
图1 操作示意图
end wlptest;
/
--建立程序包体
create or replace package body wlptest
AS
OutputFile UTL_FILE.FILE_TYPE; --输出文件对象
type struct_records is record(
id number(1),
name varchar2(20),
address varchar2(20)
);
logtemp struct_records;
CURSOR log_cursor IS
select id,name,address from test;
PROCEDURE START_OUT
AS
BEGIN
OutputFile := UTL_FILE.FOPEN('TMP','expdata.txt','a');
DBMS_OUTPUT.PUT_LINE('***BEGIN TO EXPORT DATA!***');
OPEN log_cursor;
loop
fetch log_cursor into logtemp;
exit when log_cursor%notfound;
UTL_FILE.PUTF(OutputFile,'%s|%s|%sn',logtemp.id,
,logtemp.address); END loop;
CLOSE log_cursor;
DBMS_OUTPUT.PUT_LINE('***FINISHED EXPORT DATA!***');
UTL_FILE.FFLUSH(OutputFile);
UTL_FILE.fclose(OutputFile);
END START_OUT;
END wlptest;
/
show errors;
执行创建及编译包及包体
SQL> @/export/home2/pro.sql
第六步：执行程序包，生成数据文件。
SQL> set serverout on
SQL> exec wlptest.START_OUT;
4.结论
本文所提出的实验方法，使导出数据表文件的工作变得非常简单，不
需要编写应用程序，也不需要使用第三方的软件工具，保证了以最高的效率得到想要的数据导出文件，具有较强的实际意义。
文中提供的两种导出方法，各有优点：方法一可以在数据库服务器端或客户端生成结果文件，方法二虽然只能在数据库服务器端生成结果文件，但可以经过进一步的改造完成一个数据导出小模块，供第三方进行很方便的调用。
我们需要根据具体的环境和情况来选择合理使用哪种方法。
参考文献
[1]Thomas Kyte.Expert Oracle Database Architecture 9i and 10g Programming Techniques and Solutions ,2006.。
oracle表数据导出为文本形式
oracle表数据导出为⽂本形式oracle表数据导出⽂本数据（xls或txt）今天试验了两种⽅法，记录如下1.第⼀种⽅法：采⽤utl_file包如下过程即可实现某表数据的导出CREATE OR REPLACE PROCEDURE p_tabletoxls ISv_file utl_file.file_type;CURSOR cur_emp ISSELECT ename, deptno FROM emp;BEGINIF utl_file.is_open(v_file) THENutl_file.fclose(v_file);END IF;v_file := utl_file.fopen('UTL_FILE_DIR', 'emp.xls', 'w');FOR i IN cur_emp LOOPutl_file.put_line(v_file, i.ename || chr(9) || i.deptno); --chr(9)即字段换列END LOOP;utl_file.fclose(v_file);EXCEPTIONWHEN OTHERS THENdbms_output.put_line(SQLERRM); --写⼊数据IF utl_file.is_open(v_file) THENutl_file.fclose(v_file);END IF;END p_tabletoxls;注：ULT_file包的使⽤要先创建⼀个⽬录存放数据create or replace directory UTL_FILE_DIR as 'D:\dir';grant read,write on directory UTL_FILE_DIR to ltwebgis;2.第⼆种⽅法：采⽤ociuldr⼯具先下载该⼯具，如下所⽰：D:\ociuldr\ociuldr>ociuldr user=username/username@orcl query="SELECT ename, deptno FROM emp"field=0x20 record=0x0a file=emp.xls命令说明：user = username/password@tnsnamesql = SQL file name, one sql per file, do not include ";"query = select statementfield = seperator string between fieldsrecord= seperator string between recordsfile = output file name(default: uldrdata.txt)field=0x20 表⽰字段间⽤空格表⽰，也可以写成field=' '。
Oracle使用UTL_FILE文件包大批量数据导出到CSV(Excel)文件
Oracle使用UTL_FILE文件包大批量数据导出到
CSV(Excel)文件
1.创建测试表
创建多张表，模拟多张表操作。
2.创建初始化表
这张表保存了存储过程执行过程中需要执行的sql语句，格式为select * from table_name（注意：一定不能加分号）。
3.初始化数据
根据需要调整需要导出数据的表（通过调整sql语句实现）。
4.创建目录及授权
如果目录已存在，只需要授权即可
5.创建存储过程
6.执行存储过程
打开数据库系统输出后执行存储过程。
由于文件是以追加的模式打开，所以每次存储过程执行完毕后请重新初始化表EXP_TMP和最终生成的文件，否则下次打开时回忆追加的模式将数据进行合并。
oracle 导出导入操作
oracle 导出导入操作基础知识：一、数据导出（exp.exe）1、将数据库orcl完全导出，用户名system，密码accp，导出到d:\daochu.dmp文件中exp system/accp@orcl file=d:\daochu.dmp full=y2、将数据库orcl中scott用户的对象导出exp scott/accp@orcl file=d:\daochu.dmp owner=(scott)3、将数据库orcl中的scott用户的表emp、dept导出exp scott/accp@orcl file= d:\daochu.dmp tables=(emp,dept)4、将数据库orcl中的表空间testSpace导出exp system/accp@orcl file=d:\daochu.dmp tablespaces=(testSpace)二、数据导入（imp.exe）1、将d:\daochu.dmp 中的数据导入orcl数据库中。
imp system/accp@orcl file=d:\daochu.dmp full=y2、如果导入时，数据表已经存在，将报错，对该表不会进行导入；加上ignore=y即可，表示忽略现有表，在现有表上追加记录。
imp scott/accp@orcl file=d:\daochu.dmp full=y ignore=y3、将d:\daochu.dmp中的表emp导入imp scott/accp@orcl file=d:\daochu.dmp tables=(emp)ok，现在尝试导出数据首先查看字符集select userenv(‘language’) from dual;分别查看字符集是否相同，不相同的话更改一致，否则不能正常导入我在执行过程中出现错误error: ora-12712(解决办法)shutdown immediate;startup mount;ALTER SESSION SET SQL_TRACE=TRUE;ALTER SYSTEM ENABLE RESTRICTED SESSION;ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;ALTER SYSTEM SET AQ_TM_PROCESSES=0;ALTER DATABASE OPEN;set linesize 120;alter database character set zhs16gbk;RROR at line 1:ORA-12712: new character set must be a superset of old character setALTER DATABASE character set INTERNAL_USE zhs16gbk;ALTER SESSION SET SQL_TRACE=FALSE;shutdown immediate;STARTUP执行导出命令exp scott/cat@orcl wner=scott direct=y file=scott.dmp执行成功后将scott.dmp 拷贝至目标数据库执行导入命令imp scott/cat@orcl fromuser=scott touser=scott ignore=y file=d:\jzz.DMP。
Oracle中的导入导出表及数据
Oracle中的导入导出表及数据Oracle数据导入导出imp/exp就相当于oracle数据还原与备份。
exp命令可以把数据从远程数据库服务器导出到本地的dmp文件，imp命令可以把dmp文件从本地导入到远处的数据库服务器中。
利用这个功能可以构建两个相同的数据库。
1.用plsql实现1.1使用plsql连接oracle，点击工具——导出表1.2选择要导出的表1.3可执行文件在C:\oracle\product\10.2.0\db_1\bin 目录下导出是exp 导入是imp导出的为dmp文件1.4导入文件：点击工具——导入表在导入文件中选择要导入的表确认后点击导入2.用dos命令实现2.1Windows——R——cmd2.2输入dos命令：exp youngtop_us/ail@192.168.0.46/orcl10g file=F:/fileSys.dmp log=F:/fileSys.logstatistics=none tables=file_attach,file_tree,file_permissionps：exp user/password@主机地址file=存储位置log=存储位置statistics=none tables=tablename3.将数据导出到excel表中及将excel表数据导入数据库3.1选中要导出数据的表右键——查询数据3.2选中表中的数据邮件——复制到excel3.3在excel中保存3.4可以不按照数据库中的字段存放顺序，编辑形成Excel表中的数据3.5选中要导入的数据后另存一份txt文档3.6在plsql中点击工具——文本导入器进入到文本导入器的页面后，先点击“来自文本文件的数据”选项卡，然后点击打开按钮，选择数据录入.txt文件3.7在配置中进行配置如果不将标题名勾选上，则会导致字段名也当做记录被导入到数据库中，影响正确录入3.8点击导入按钮将数据导入oracle数据库中。
Oracle导入与导出(即备份与恢复)
在没有警告的情况下成功终止导出。
IMP jwd/jwd@ps D:\DD\PHARMACY.DMP FULL=Y ****************
********************************
3. 导出工具exp非交互式命令行方式的例子
$exp scott/tiger tables=emp,dept file=/directory/scott.dmp grants=y
说明:把scott用户里两个表emp,dept导出到文件/directory/scott.dmp
$exp scott/tiger tables=emp query=\"where job=\'salesman\' and sal\<1600\" file=/directory/scott2.dmp
参数文件username.par内容
userid=username/userpassword
buffer=8192000
compress=n
grants=y
说明:username.par为导出工具exp用的参数文件,里面具体参数可以根据需要去修改
filesize指定生成的二进制备份文件的最大字节数
(可用来解决某些OS下2G物理文件的限制及加快压缩速度和方便刻历史数据光盘等)
4. 命令参数说明
关键字 说明（默认）
---------------------------------------------------
USERID 用户名/口令
FULL 导出整个文件 (N)
Oracle支持三种类型的输出：
oracle数据库导入导出
JServer Release 8.1.7.0.0 - Production
经由常规路径导出由EXPORT:V08.01.07创建的文件
已经完成ZHS16GBK字符集和ZHS16GBK NCHAR 字符集中的导入
导出服务器使用UTF8 NCHAR 字符集(可能的ncharset转换)
impdp piner/piner directory=dump_test dumpfile=table.dmp 导出表数据
imp 目标库usr/目标库pwd@目标库连接符 file=test.dmp log=test_imp.log ignore=Y
imp lisquery/lisquery@ora100 file=E:\yangfan\lis120824\lis120824\lis120824.dmp fromuser=lisquery touser=lis120824 tables=(lcpol);
也可以在上面命令后面 加上compress=y 来实现。
数据的导入：
1 将D:\daochu.dmp 中的数据导入TEST数据库中。
imp system/manager@TEST file=d:\daochu.dmp
imp aichannel/aichannel@HUST full=y file= d:\data\newsmgnt.dmp ignore=y
imp lisquery / lisquery@ora100 file = E:\lis120824.dmp fromuser = lisquery touser = lis120824 tables = (lp_gl_interface, newinterface);
4 将数据库中的表table1中的字段filed1以"00"打头的数据导出
oracleg数据库导入导出方法教程
oracleg数据库导入导出方法教程Oracle 11g 是一种关系型数据库管理系统，它具有很多强大的功能，包括数据导入和导出。
在本教程中，我们将介绍 Oracle 11g 数据库的导入和导出方法。
导出数据的方法有两种，一种是使用 exp 工具，另一种是使用expdp 工具。
exp 工具是在 Oracle 11g 之前版本中使用的，而 expdp工具是在 Oracle 11g 之后版本中引入的。
在这个教程中，我们将使用expdp 工具来导出数据。
导出数据的步骤如下：1. 打开终端或命令提示符，并登录到您的 Oracle 数据库。
2.使用以下命令导出整个数据库：```sql```其中，username 是数据库用户名，password 是密码，connect_string 是连接字符串，directory_name 是要导出数据的目录名称，dumpfile_name 是要导出数据的文件名称。
例如，如果要导出一个用户的数据，可以使用以下命令：```sql```这将导出 hr 用户的数据到 datapump 目录，并生成一个 hr.dmp 文件。
3.数据导出完成后，您可以在指定目录下找到生成的导出文件。
导入数据的方法也有两种，一种是使用 imp 工具，另一种是使用impdp 工具。
在这个教程中，我们将使用 impdp 工具来导入数据。
导入数据的步骤如下：1. 打开终端或命令提示符，并登录到您的 Oracle 数据库。
2.使用以下命令导入数据：```sql```其中，username 是数据库用户名，password 是密码，connect_string 是连接字符串，directory_name 是导入数据的目录名称，dumpfile_name 是要导入的数据文件的名称。
例如，如果要导入一个用户的数据，可以使用以下命令：```sql```这将导入 hr 用户的数据，该数据文件位于 datapump 目录下的hr.dmp 文件。
Oracle数据库导入导出方法汇总
Oracle数据库导入导出方法:1.使用命令行：数据导出：1.将数据库TEST完全导出,用户名system密码manager导出到D:\daochu.dmp中exp system/manager@TEST file=d:\daochu.dmp full=y2.将数据库中system用户与sys用户的表导出exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)3.将数据库中的表inner_notify、notify_staff_relat导出exp aichannel/aichannel@TESTDB2 file= d:\data\newsmgnt.dmp tables=(inner_notify,notify_staff_relat)4.将数据库中的表table1中的字段filed1以"00"打头的数据导出exp system/manager@TEST file=d:\daochu.dmp tables=(table1) query=\" where filed1 like '00%'\"上面是常用的导出，对于压缩，既用winzip把dmp文件可以很好的压缩。
也可以在上面命令后面加上compress=y来实现。
数据的导入：1.将D:\daochu.dmp 中的数据导入TEST数据库中。
imp system/manager@TEST file=d:\daochu.dmpimp aichannel/aichannel@HUST full=y file= d:\data\newsmgnt.dmp ignore=y上面可能有点问题，因为有的表已经存在，然后它就报错，对该表就不进行导入。
在后面加上ignore=y 就可以了。
2.将d:\daochu.dmp中的表table1导入imp system/manager@TEST file=d:\daochu.dmp tables=(table1)3.不同名用户之间的数据导入：imp system/test@xe fromuser=hkb touser=hkb_new file=c:\orabackup\hkbfull.dmplog=c:\orabackup\hkbimp.log;2.plsql:数据导出:TOOLS-Export user objects(用户对象)TOOLS-Export tables(表)数据的导入：TOOLS-Import tablesOracle Import(表) SQL Inserts(用户对象)也可以将用户对象的语句拷贝出来，粘贴到Command Window这样的好处是可以看到执行的过程。
[原创]Oracle数据导入导出
本文包含exp/imp,expdp/impdp的使用说明和常用参数详解另外包括一个有趣的测试一、Oracle数据库EXP\IMP\EXPDP\IMPDP使用说明1．Exp数据导出1.1．exp关键字说明关键字说明 (默认值)------------------------------USERID 用户名/口令BUFFER 数据缓冲区大小FILE 输出文件 (EXPDAT.DMP)COMPRESS 导入到一个区 (Y)GRANTS 导出权限 (Y)INDEXES 导出索引 (Y)DIRECT 直接路径 (N) --直接导出速度较快LOG 屏幕输出的日志文件ROWS 导出数据行 (Y)CONSISTENT 交叉表的一致性 (N)FULL 导出整个文件 (N)OWNER 所有者用户名列表TABLES 表名列表RECORDLENGTH IO记录的长度INCTYPE 增量导出类型RECORD 跟踪增量导出 (Y)TRIGGERS 导出触发器 (Y)STATISTICS 分析对象 (ESTIMATE)PARFILE 参数文件名CONSTRAINTS 导出的约束条件 (Y)OBJECT_CONSISTENT 只在对象导出期间设置为只读的事务处理 (N)FEEDBACK 每 x 行显示进度 (0)FILESIZE 每个转储文件的最大大小FLASHBACK_SCN 用于将会话快照设置回以前状态的 SCNFLASHBACK_TIME 用于获取最接近指定时间的 SCN 的时间QUERY 用于导出表的子集的 select 子句RESUMABLE 遇到与空格相关的错误时挂起 (N)RESUMABLE_NAME 用于标识可恢复语句的文本字符串RESUMABLE_TIMEOUT RESUMABLE 的等待时间TTS_FULL_CHECK 对 TTS 执行完整或部分相关性检查TABLESPACES 要导出的表空间列表TRANSPORT_TABLESPACE 导出可传输的表空间元数据 (N)TEMPLATE 调用 iAS 模式导出的模板名1.2．常用的exp关键字举例1、full用于导出整个数据库，在rows=n一起使用，导出整个数据库的结构。
oracle的数据库的导入导出
oracle的数据库的导入导出从一个用户expdp导出再impdp导入到另一个用户（示例：讲scott用户里面的表全部迁移到新建的test用户里面）如果想导入的用户已经存在:1.导出之前需要做的一些操作，进入数据库，默认为sys用户SQL> create directory dumpdir as '/home/oracle/test_bk ';（该备份路径是需要手动创建的）SQL> grant read,write on directory dumpdir to scott(scott为源用户)；导出用户expdp user1/pass1 directory=dumpdir dumpfile=user1.dmp 示例：expdp scott/tiger directory=dumpdir dumpfile=scott.dmp2.导入之前需要做一些操作，进入数据库，默认为sys用户SQL> create directory dumpdir as '/home/oracle/test_bk ';（该备份路径是需要手动创建的）SQL> grant read,write on directory dumpdir to test(test为目标用户)；导入用户impdp test/test directory=dumpdir dumpfile=scott.dmp REMAP_SCHEMA=scott:test full=y;如果想导入的用户不存在:1. 导出用户expdp user1/pass1 directory=dumpdirdumpfile=user1.dmp2. 导入用户impdp system/passsystem directory=dumpdirdumpfile=user1.dmp REMAP_SCHEMA=user1:user2 full=y;3. user2会自动建立，其权限和使用的表空间与user1相同，但此时用user2无法登录，必须修改user2的密码impdp遇到的错误C:\Documents and Settings\Administrator>impdp aaa/ccc directory=data_dump dumpfile=fromaaa.dmp logfile=IMP_DATA_20100618.LOG连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsORA-39001: 参数值无效ORA-39000: 转储文件说明错误ORA-39143: 转储文件"F:\ora10G_expdp\ic_price_fromlufang.dmp" 可能是原始的导出转储文件可恶的提示让我一直以为是版本的的问题，因为是同事给的dmp文件，用的又都是10.2.0版本，自然以为用的是expdp，所以一直用impdp导入，其他的权限都没问题，所以最后怀疑同事用的是exp，所以试了下imp导入，成功执行了。