浅说两种输出Oracle字符文件的方法

归纳几条简单方便的经验之谈吧。前几天,一个同事为做项目和笔者讨论将数据库检索处理结果输出的方法。为了简单明了,笔者按照不同的需求场景准备了两种策略供同事进行选择,记录下来,权作不时之需的留存。

文本文件输出,特别是大数据量文本文件输出,是我们在实际需求领域中经常遇到的场景。文本文件是我们最早接触的文件格式,格式单一,内容简单。但是,也正是因为结构简单,是很多“中间结构文件”通常选择的载体。从最早简单的txt、csv,到现在越来越多出现xml,本质上都是以文本格式文件进行保存。

所谓“中间结构文件”,也就是我们通常所说的接口文件。如果系统之间需要进行大规模数据传输、接入或者交互,双方共同认可的“协议”也就是问题的关键。通过接口文件格式外加自动化上传、定位和检索机制,是可以实现解耦方式的系统间数据交互。

Oracle环境中通常使用的文本生成方式传统上有两种,一种是借助原生的sqlplus命令行工具,将数据转出到客户端目录上。注意:sqlplus命令行系列默认输出是屏幕。另一种是借助utl_file工具包将数据输出到数据库服务端(Server Side)。两种方法各有利弊优缺点,各有适应的场景。下面分别进行讨论。

1、实验环境介绍

笔者使用Oracle 11gR2版本进行测试,具体版本为11.2.0.4。

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

PL/SQL Release 11.2.0.4.0 - Production

CORE    11.2.0.4.0      Production

TNS for Linux: Version 11.2.0.4.0 - Production

NLSRTL Version 11.2.0.4.0 – Production

2、Sqlplus的Spool方法

Spool方法是脚本工程师比较常用的策略。思路其实也比较简单:在传统的编程结构中,数据处理结果的输出是有一个导向定位机制的。默认情况下,Sqlplus系工具的输出是屏幕窗口。Spool方法就是重新设置输出方式,将结果文本内容输出到磁盘文件中。

使用spool文件有两个方面需要关注:首先是文件位置。生成的文件,无论是Linux/AIX文件还是Windows系列,都是在客户端所在的计算机(执行程序的机器)上生成。另一个就是文本量限制,无论是使用sqlplus、还是PL/SQL Developer的Command Windows窗口,都会遇到潜在的缓冲区buffer溢出风险。这也就限制了生成文件的大小。

下面我们通过一个简单实验来进行证明,实验数据表结构如下:

SQL> desc test_user;

Name            Type        Nullable Default Comments                                                                                                         

---------------- ------------ -------- ------- ---------------------------------------------------------------------------------------------------------------- 

OBJECT_ID        CHAR(32)                      String - Object Id                                                                                               

USER_ID          CHAR(32)    Y                String - Unique User ID                                                                                         

FIRSTNAME        CHAR(50)    Y                String - Users first name                                                                                       

LASTNAME        CHAR(50)    Y                String - Users last name                                                                                         

SHORTNAME        CHAR(5)      Y                String - Users short name                                                                                       

IS_ACTIVE        CHAR(1)      Y                Character - Boolean                                                                                             

在sqlplus命令行中依次执行:

SQL> spool d:\spool_test.txt

Started spooling to d:\spool_test.txt

SQL> 

SQL> set echo off;

SQL> set feedback off;

SQL> set newpage none;

SQL> set linesize 1000;

SQL> set pagesize 0;

SQL> set term off;

SQL> set timing off;

SQL> set verify off;

SQL> select trim(USER_ID)||','||trim(FIRSTNAME)||','||trim(LASTNAME)||','||trim(IS_ACTIVE)

  2  from test_user;

SQL> spool off;

Stopped spooling to d:\spool_test.txt

注意几个细节问题:

首先在SQL>命令提示符,使用spool命令,就可以启动/关闭文件写入磁盘动作。Spool on就是默认开启命令,而spool后加入路径就指定了文件目录名称。关闭写入spool off后,文件写入动作自动停止。注意:从文件大小角度看,只有在spool off的时候,通常系统才将结果从缓冲区写入到文件中。

其次是sqlplus参数配置。Sqlplus易用难精,有很多控制参数用于输出输入数据方式。对于一般使用者而言,可以直接保留一份固定的控制参数模板,对相同需求的命令可以直接使用。

生成文件之后,我们就可以在客户端机器的目录上找到对应文件。其中内容恰好是满足逗号分隔数据要求的。

111,222,222,Y

111,2222,33,Y

11,222,33,Y

(篇幅原因,有省略…..)

Spool方法的优点是很明显的,就是简单易用,对使用用户权限要求低,只要能够使用sqlplus工具,就可以生成文件。同时,生成文件在客户端,也不需要DBA和系统管理员设置目录权限管理空间消耗。

同时,spool方法的缺点也是比较明显的,就是生成文件大小限制。进入11g之后,sqlplus在缓冲区上有所扩大,但是依然还是很大的工作隐患。站在系统自动化的角度,生成的接口文件存放在客户端也不是一种规范的做法,不利于后续自动化传输处理。

总而言之,spool方法比较适用于小规模、简单数据文件的生成。

3、utl_file包使用

UTL_FILE是Oracle官方推荐的一种经典文件生成方法,主要原则是通过utl_file包来进行文本文件读写动作。与spool方法最大的区别,在于utl_file包主要是生成在数据库服务器端(也就是Oracle Instance运行的服务器上),同时utl_file包对于文件读写的控制更加细粒度化,以一种类似于C语言的方式进行文件读写。

从目前看,utl_file生成文件依然是从数据库端生成文件比较成熟的方案,特别是大接口文件。在一些高性能需求的场景下,还是有竞争力的。

对于utl_file包,不能不说到参数utl_file_dir。在Oracle 9.2之前,这个参数是生成读写utl_file执行的最重要参数。

SQL> show parameter utl_file

NAME                                TYPE        VALUE

------------------------------------ ----------- ------------------------------

utl_file_dir                        string     

在现在我们在网络查询资料时,还是能够看到对utl_file_dir参数的设置要求。在9.2之前的版本中,如果进行文件读写,都需要这个这个参数,将读写文件的所在目录添加到其上。否则Oracle就不能承认这个目录下的文件操作权限。略麻烦的是,这个参数修改要在spfile中进行,生效就需要重启服务器。

这种场景在9.2版本之后有了变化,directory对象的出现,提供了更好的读写目录管理和权限管理。最大的一个好处,就是在代码中,可以不用硬编码方式写目录结构。所以,当前utl_file_dir目录基本不会再使用,只是出于系统兼容性目的。

使用utl_file包方法使用如下步骤:

步骤1:创建directory目录

[oracle@sicslife /]$ su - root

Password: 

--确保操作系统层面的权限!

[root@sicslife ~]# cd /

[root@sicslife /]# chown -R oracle:oinstall /upload/

[root@sicslife /]# ls -l | grep upload

drwxr-xr-x.  4 oracle oinstall  4096 Aug  6 21:21 upload

SQL> create directory utl_path as '/upload';

SQL>

SQL> select directory_name, directory_path from dba_directories;

DIRECTORY_NAME                DIRECTORY_PATH

------------------------------ ------------------------------------------------

UTL_PATH                      /upload

使用directory要解��两个层面权限,一个是操作系统层面,要让Oracle操作系统用户可以使用目录。另一个是directory对象使用权,要进行显示的授权。

SQL> grant write on directory utl_path to scott;

Grant succeeded

SQL> grant execute on utl_file to scott;

Grant succeeded

最后,可以在代码中进行调用。

SQL> set serveroutput on size 1000;

SQL> declare

  2    out_file utl_file.file_type; --文件类型,也就是句柄对象

  3    vc_file_name varchar2(100);

  4    vc_line varchar2(100);

  5    i number;

  6  begin

  7    vc_file_name := 'utl_file_test.txt';

  8 

  9    out_file := utl_file.fopen('UTL_PATH',vc_file_name,'w'); --写方式打开文件

 10 

 11    if (utl_file.is_open(out_file)) then

 12      for i in 1..100 loop

 13          vc_line := to_char(i)||','||i||'Lines~';

 14          utl_file.put_line(out_file,vc_line);

 15      end loop;

 16    else

 17      dbms_output.put_line('Open Failure~');

 18    end if;

 19 

 20    utl_file.fclose(out_file); --和C语言一样,需要显示进行关闭

 21  end;

 22  /

PL/SQL procedure successfully completed

最后,就可以在操作系统层面,找到对应文件。

[root@sicslife /]# cd /upload/

[root@sicslife upload]# ls -l

total 12

drwxr-xr-x. 7 oracle oinstall 4096 Aug 27  2013 database

drwx------. 3 oracle oinstall 4096 Aug  5 17:26 igb-5.3.2

-rw-r--r--. 1 oracle oinstall 1184 Aug  7 02:19 utl_file_test.txt

[root@sicslife upload]# cat utl_file_test.txt 

1,1Lines~

2,2Lines~

3,3Lines~

4,4Lines~

5,5Lines~

6,6Lines~

(篇幅原因,有省略…….)

 [root@sicslife upload]#

Utl_file包是一种比较成熟的文件读写方案,除了实例中操作的步骤方法之外,还定义了很多有用的读写方法、异常类型,这对于我们进行完善编程是很有意义的。同时,在实际应用中,utl_file有着更多的细节因素和限制特性,本篇不予累述。

3、结论

利用数据库生成文件,是非常常见的需求。在不借助第三方工具的情况下,spool和utl_file是不错的工具选择。

相关推荐