ORACLE UTL_FILE文件包的应用，文件I/O操作

利用 ORACLE UTL_FILE 包可以使文本文件转入进数据表，反之亦然：

1 ：建立文件目录：

首先在数据库服务器上建立相应的文件目录。

1 ． 1 方法：

在初始化文件配置文件 init.ora 的配置中将

UTL_FILE_DIR = ‘ E ： /temp ’指定路径；

1.2 方法：

创建路径对象：

Create directory test_dir as e:temp

建议用第二种方法；

2 ：打开和关闭文件：

所有的文件句柄都拥有 UTL_FILE.FILE_TYPE 。 FILE_TYPE 在 UTL_FILE 规范中进行了定义。

2.1 ： FOPEN （ location in varchar2, filename in varchar2, open_mode in varchar2 ） return FILE_TYPE;

Location 是路径参数， FILENAME 是文件名， OPEN_MODE 是打开模式；

有效值：‘ R ’是读文本，‘ W ’是写文本，‘ A ’是附加文本，参数不分大小写，如果指定‘ A ’但是文件不存在，它会用‘ W ’先创建出来，‘ W ’有覆盖的功能；

2.2 : FCLOSE(file_handle in out file_type);

唯一的参数是文件句柄，就是关闭文件；

FCLOSE_ALL 关闭所有文件句柄；

2.3: IS_OPEN(file_handle in file_type) return Boolean;

判断文件是否打开；

3 ：文件输出：

所有的函数如下：

3.1 ： PUT(file_handle in file_type, buffer in varchar2);

文件输出，但是不会在文件中附加新行字符（ newline ），必须用 put_line 或者 new_line 向文件中输入终结符；

3.2 ： NEW_LINE （ file_handle in file_type, lines in natural := 1 ） ;

向文件中写入一个或者多个行终结符；

3.3 ： PUT_LINE(file_handle in file_type, buffer in varchar2);

等价 PUT 后接着用 NEW_LINE ；

3.4 ： PUTF(file_handle in file_type, format in varchar2,

Arg1 in varchar2 default null,

Arg2 in varchar2 default null,

Arg3 in varchar2 default null,

Arg4 in varchar2 default null,

Arg5 in varchar2 default null)

和 PUT 类似，但是它允许输出字符串是带格式的，格式字符中 n 是换行， %s 被可选参数取代；

例如：

Declare

v_outputfile utl_file.file_type;

v_name varchar2(20) := ‘scott’;

begin

v_outputfile := utl_file.fopen(..);

utl_file.putf(v_outputfile,

‘hi there! n my name is %s,and I am a %s major.n’,

V_name,

‘Computer science’ );

Fclose(v_outputfile);

end;

 

输出文件格式：

hi there!

my name is scott ,and I am a Computer science major.

 

3.5: 输出文件应用实例：

create or replace procedure p_mmr_new
(p_start_time out date,
p_end_time out date)
is
v_file UTL_FILE.FILE_TYPE;
v_string varchar2(100);
v_error exception;
v_i number;
cursor cur_ms_no is

select distinct substr(trim(a.ms_no),1,11)
from msno.t_upload_msno a,
msno.t_msno_black b
where substr(a.ms_no,1,11) = b.ms_no(+)
and b.ms_no is null;

v_count number;
v_ms_no varchar2(20);
begin
p_start_time:=sysdate;
v_count:=0;
-- insert
v_file := UTL_FILE.FOPEN('OUTER_DIR','ivr170.txt', 'w',32767);
open cur_ms_no ;
loop
fetch cur_ms_no into v_ms_no;
exit when cur_ms_no%notfound;
UTL_FILE.PUT_line(v_file, v_ms_no);
v_count:=v_count+1;
if v_count>=5000 then
UTL_FILE.fflush(v_file);
--UTL_FILE.FCLOSE(v_file);
--v_file := UTL_FILE.FOPEN('OUTER_DIR','v_ms_no.txt', 'a',32767);
v_count:=0;
end if;
end loop;
UTL_FILE.FCLOSE(v_file);
close cur_ms_no;
--close
p_end_time:=sysdate;
EXCEPTION
WHEN v_error Then
UTL_FILE.FCLOSE(v_file);
RETURN;
end;

 

4 ：文件输入：

4.1 get_line(file_handle in file_type, buffer out varchar2);

从文件中读出数据；

示例：

Declare

V_newline varchar2(200);

begin

v_filehandle := utl_file.fopen(p_filedir, p_filename, ‘r’);

utl_file.get_line(vfilehandle, v_newline);

 

insert into t1 (tip) values(v_newline);

end;

///

第一步：以管理 员用户登陆  
   如：conn   sys/password@sid     as    sysdba  
   第二步：设置可操作目录  
   需要指定utl_file包可以操作的目录。在oracle    10g以前，可以用以下方法：  
   1、alter    system    set    utl_file_dir='e:/utl'    scope=spfile;  
   2、在init.ora文件中，配置如下：  
   UTL_FILE=E:/utl或者UTL_FILE_DIR=E:/utl  
   在oracle    10g中建议用以下方法配置：CREATE    DIRECTORY    utl    AS    'E:/utl';  
   参见oracle    online:  
   In    the    past,    accessible    directories    for    the    UTL_FILE    functions    were    specified    in    the    initialization    file    using    the    UTL_FILE_DIR    parameter.    However,    UTL_FILE_DIR    access    is    not    recommended.    It    is    recommended    that    you    use    the    CREATE    DIRECTORY    feature,    which    replaces    UTL_FILE_DIR.    Directory    objects    offer    more    flexibility    and    granular    control    to    the    UTL_FILE    application    administrator,    can    be    maintained    dynamically    (that    is,    without    shutting    down    the    database),    and    are    consistent    with    other    Oracle    tools.    CREATE    DIRECTORY    privilege    is    granted    only    to    SYS    and    SYSTEM    by    default.  
   
   第三步：授权给指定用户，以便执行utl_file  
   GRANT    EXECUTE    ON    utl_file    TO    scott;  
   
   第四步：conn    scott/tiger  
   就可以正常使用utl_file了。    
   
   
   
   
   摘要：本文主要讨论如何利用Oracle的UTL_FILE包来实现对磁盘文件的I/O操作。  
   
   　　文件I/O对于数据库 的开发来说显得很重要，比如如果数据库中的一部分数据来自于磁盘文件，  
           那么就需要使用I/O接口把数据导入到数据库中来。在    PL/SQL中没有直接的I/O接口，  
           一般在调试程序时可以使用Oracle自带的DBMS_OUTPUT包的put_line函数（即向屏幕进行I/O    操作）即可，  
         但是对于磁盘文件的I/O操作它就无能为力了。其实Oracle同样也提供了可以进行文件I/O的实用包-----UTL_FILE包，  
         利用这个实用包提供的函数来实现对磁盘的I/O操作。  
   
   　　1．    准备工作  
   
   　　由于Oracle数据库对包创建的目录有一个安全管理的问题，所以并不是所有的文件目录能够被UTL_FILE包所访问，  
           要更新这种目录设置，就得到init.ora里将UTL_FILE_DIR域设置为*，这样UTL_FILE包就可以对所有的目录文件进行访问了。  
   
   　　2．    文件I/O的实施  
   
   　　UTL_FILE包提供了很多实用的函数来进行I/O操作，主要有以下几个函数：  
   
   　　fopen  
   
   　　打开指定的目录路径的文件。  
   
   　　get_line  
   
   　　获取指定文件的一行的文本。  
   
   　　put_line  
   
   　　向指定的文件写入一行文本。  
   
   　　fclose  
   
   　　关闭指定的文件。  
   
   　　下面利用这些函数，实现从文件取数据，然后将数据写入到相应的数据库中。  
   
   create    or    replace    procedure    loadfiledata(p_path    varchar2,p_filename    varchar2)    as  
   v_filehandle    utl_file.file_type;    --定义一个文件句柄  
   v_text    varchar2(100);    --存放文本  
   v_name    test_loadfile.name%type;  
   v_addr_jd    test_loadfile.addr_jd%type;  
   v_region    test_loadfile.region%type;  
   v_firstlocation    number;  
   v_secondlocation    number;  
   v_totalinserted    number;  
   begin  
   if    (p_path    is    null    or    p_filename    is    null)    then  
   goto    to_end;  
   end    if;  
   v_totalinserted:=0;  
   /*open    specified    file*/  
   v_filehandle:=utl_file.fopen(p_path,p_filename,'r');  
   loop  
   begin  
   utl_file.get_line(v_filehandle,v_text);  
   exception  
   when    no_data_found    then  
   exit;  
   end    ;  
   v_firstlocation:=instr(v_text,',',1,1);  
   v_secondlocation:=instr(v_text,',',1,2);  
   v_name:=substr(v_text,1,v_firstlocation-1);  
   v_addr_jd:=substr(v_text,v_firstlocation+1,v_secondlocation-v_firstlocation-1);  
   v_region:=substr(v_text,v_secondlocation+1);  
   /*插入数据库操作*/  
   insert    into    test_loadfile  
   values    (v_name,v_addr_jd,v_region);  
   commit;  
   end    loop;  
   <<to_end>>  
   null;  
   end    loadfiledata;  
   
   
   
   可以不用在init.ora中改的  
   只要用管理员的权限登陆，执行：  
   create    directory    UTL_FILE_TEST    as    '/*'    
   应该就可以了,不用新启动oracle的  
   不过其他用户要使用此目录要授权的  
   /  
   
   
   grant    create    any    directory    to    scott;  
     grant    create    any    library    to    scott;  
     create    or    replace    directory    utllobdir    as    'C:/ep';  
   
   
   在initsid.ora文件中，加入或修改  
       设置utl_file_dir的要点：  
     1。    utl_file_dir=*      这表示你能操作任何目录，尽量不要用  
     2。    utl_file_dir=d:/    这表示你能操作d:/目录下的文件，但你不能操作d:/目录下的子目录  
     3。注意在设置  
       utl_file_dir=路径时，如果路径是长路径名，例如c:/my    temp目录，则你必须加上''，例如：  
       utl_file_dir='c:/my    temp'  
     4。utl_file_dir可以是多个路径  
           utl_file_dir=c:/,d:/,d:/temp,'c:/my    temp'  
     5。设置完必须重新启动数据库

ORACLE UTL_FILE文件包的应用，文件I/O操作
ORACLE UTL_FILE文件包的应用，文件I/O操作

利用ORACLE UTL_FILE包可以使文本文件转入进数据表，反之亦然：

1：建立文件目录：

首先在数据库服务器上建立相应的文件目录。

1．1方法：

在初始化文件配置文件init.ora的配置中将

UTL_FILE_DIR = ‘E：/temp’指定路径；

1.2 方法：

创建路径对象：

Create directory test_dir as e:temp

建议用第二种方法；

2：打开和关闭文件：

所有的文件句柄都拥有UTL_FILE.FILE_TYPE。FILE_TYPE在UTL_FILE规范中进行了定义。

2.1：FOPEN（location in varchar2, filename in varchar2, open_mode in varchar2）return FILE_TYPE;

Location 是路径参数，FILENAME 是文件名，OPEN_MODE是打开模式；

有效值：‘R’是读文本，‘W’是写文本，‘A’是附加文本，参数不分大小写，如果指定‘A’但是文件不存在，它会用‘W’先创建出来，‘W’有覆盖的功能；

2.2 : FCLOSE(file_handle in out file_type);

唯一的参数是文件句柄，就是关闭文件；

FCLOSE_ALL关闭所有文件句柄；

2.3: IS_OPEN(file_handle in file_type) return Boolean;

判断文件是否打开；

3：文件输出：

所有的函数如下：

3.1： PUT(file_handle in file_type, buffer in varchar2);

文件输出，但是不会在文件中附加新行字符（newline），必须用put_line或者new_line向文件中输入终结符；

3.2： NEW_LINE（file_handle in file_type, lines in natural := 1）;

向文件中写入一个或者多个行终结符；

3.3： PUT_LINE(file_handle in file_type, buffer in varchar2);

等价PUT后接着用NEW_LINE；

3.4： PUTF(file_handle in file_type, format in varchar2,

Arg1 in varchar2 default null,

Arg2 in varchar2 default null,

Arg3 in varchar2 default null,

Arg4 in varchar2 default null,

Arg5 in varchar2 default null)

和PUT类似，但是它允许输出字符串是带格式的，格式字符中n是换行，%s被可选参数取代；

例如：

Declare

v_outputfile utl_file.file_type;

v_name varchar2(20) := ‘scott’;

begin

v_outputfile := utl_file.fopen(..);

utl_file.putf(v_outputfile,

‘hi there! n my name is %s,and I am a %s major.n’,

V_name,

‘Computer science’ );

Fclose(v_outputfile);

end;

输出文件格式：

hi there!

my name is scott ,and I am a Computer science major.

3.5: 输出文件应用实例：

create or replace procedure p_mmr_new
(p_start_time out date,
p_end_time out date)
is
v_file UTL_FILE.FILE_TYPE;
v_string varchar2(100);
v_error exception;
v_i number;
cursor cur_ms_no is

select distinct substr(trim(a.ms_no),1,11)
from msno.t_upload_msno a,
msno.t_msno_black b
where substr(a.ms_no,1,11) = b.ms_no(+)
and b.ms_no is null;

v_count number;
v_ms_no varchar2(20);
begin
p_start_time:=sysdate;
v_count:=0;
-- insert
v_file := UTL_FILE.FOPEN('OUTER_DIR','ivr170.txt', 'w',32767);
open cur_ms_no ;
loop
fetch cur_ms_no into v_ms_no;
exit when cur_ms_no%notfound;
UTL_FILE.PUT_line(v_file, v_ms_no);
v_count:=v_count+1;
if v_count>=5000 then
UTL_FILE.fflush(v_file);
--UTL_FILE.FCLOSE(v_file);
--v_file := UTL_FILE.FOPEN('OUTER_DIR','v_ms_no.txt', 'a',32767);
v_count:=0;
end if;
end loop;
UTL_FILE.FCLOSE(v_file);
close cur_ms_no;
--close
p_end_time:=sysdate;
EXCEPTION
WHEN v_error Then
UTL_FILE.FCLOSE(v_file);
RETURN;
end;

4：文件输入：

4.1 get_line(file_handle in file_type, buffer out varchar2);

从文件中读出数据；

示例：

Declare

V_newline varchar2(200);

begin

v_filehandle := utl_file.fopen(p_filedir, p_filename, ‘r’);

utl_file.get_line(vfilehandle, v_newline);

insert into t1 (tip) values(v_newline);

end;

 

oracle文件操作
oracle数据库的sys用户下的utl_file包,可以操作系统文件,步骤如下:(1)创建访问文件的路径如果要在pl/sql里面访问某个操作系 统文件,那就得先在数据库里面创建一个存放该文件的路径,然后把你想访问的文件放到这个路径下,就可以用utl_file包访了.路径的创建最好在sys 用户下面,然后把这个路径的read和write权限赋给想使用这个路径的用户语句:create directory xxx as '/oracle/oradata/utlfile';其中'/oracle/oradata/utlfile'是你想存放文件的路径,是个文件夹,这里 是linux 系 统的一个路径.(2)赋权限创建路径后,就应该把操作这个路径的权限赋给使用用户,当然如果不是sys用户创建的路径，而是创建路径的用户本身想要使用这 个路径访问文件,就可以直接使用了.语句:grant read,write on directory xxx to someuser ;(3)这样可以在pl/sql里面使用这个路径访问文件了,下面是一个例子:declare
      fhandle   sys.utl_file.file_type;
      fp_buffer varchar2(4000);
    begin
      fhandle := sys.utl_file.fopen ('XXX',wenjian.txt', 'R');--路径'xxx'一定要大写,'R'是读取,'W'是写入
      dbms_output.enable(200000);
      loop
      sys.utl_file.get_line (fhandle , fp_buffer );
      exit when fp_buffer is null or length(fp_buffer)<=0;
      dbms_output.put_line(trim(fp_buffer));
      end loop;
      sys.utl_file.fclose(fhandle);
      exception
      when no_data_found then
       exit;