PL/SQL-->UTL_FILE包的使用介绍

    在PL/SQL中,UTL_FILE包提供了文本文件输入和输出互功能。也就是说我们可以通过该包实现从操作系统级别来实现文件读取输入或者是写入到操作系统文件。通过该包也可以将其他系统的数据加载到数据库中。如加载web服务器日志,用户登录数据库日志乃至Oracle日志文件等等。本文主要描述了UTL_FILE的功能以及通过实例演示并理解这个包下相关过程函数的用法。

1、UTL_FILE介绍    a、实现基于操作系统级别的读取与写入功能    b、该方式为基于服务器端的文本文件访问模式,不支持二进制文件    c、可以通过设置参数utl_file_dir来设置pl/sql访问操作系统文件的多个路径    d、所有用户可以读写utl_file_dir参数设定的目录,因此应考虑安全问题    e、也可以将参数utl_file_dir置空,而通过创建directory以及授予对directory权限来进行访问os文件(推荐方式)

2、UTL_FILE包中的过程和函数 a、UTL_FILE中定义的file_type为记录类型,如下所示其成员是私有的,不能够被直接引用或改变这个记录的组件。

   TYPE file_type IS RECORD (       id          BINARY_INTEGER,       datatype    BINARY_INTEGER,       byte_mode   BOOLEAN);

b、UTL_FILE中相关过程函数的功能说明    FCLOSE Procedure            Closes a file    FCLOSE_ALL Procedure        Closes all open file handles    FCOPY Procedure             Copies a contiguous portion of a file to a newly created file    FFLUSH Procedure            Physically writes all pending output to a file    FGETATTR Procedure          Reads and returns the attributes of a disk file    FGETPOS Function            Returns the current relative offset position within a file, in bytes    FOPEN Function              Opens a file for input or output    FOPEN_NCHAR Function        Opens a file in Unicode for input or output    FREMOVE Procedure           Deletes a disk file, assuming that you have sufficient privileges    FRENAME Procedure           Renames an existing file to a new name, similar to the UNIX mv function    FSEEK Procedure             Adjusts the file pointer forward or backward within the file by the number of bytes specified    GET_LINE Procedure          Reads text from an open file    GET_LINE_NCHAR Procedure    Reads text in Unicode from an open file    GET_RAW Procedure           Reads a RAW string value from a file and adjusts the file pointer ahead by the number of bytes read    IS_OPEN Function            Determines if a file handle refers to an open file    NEW_LINE Procedure          Writes one or more operating system-specific line terminators to a file    PUT Procedure               Writes a string to a file    PUT_LINE Procedure          Writes a line to a file, and so appends an operating system-specific line terminator    PUT_LINE_NCHAR Procedure    Writes a Unicode line to a file    PUT_NCHAR Procedure         Writes a Unicode string to a file    PUTF Procedure              A PUT procedure with formatting    PUTF_NCHAR Procedure        A PUT_NCHAR procedure with formatting, and writes a Unicode string to a file, with formatting    PUT_RAW Procedure           Accepts as input a RAW data value and writes the value to the output buffer

3、演示ULT_FILE用法

a、使用UTL_FILE的主要步骤(使用directory方式)
  --先创建用于存放os文件的目录
  scott@USBO> ho mkdir -p /u03/database/usbo/db_utl_dir
  
  --在数据库层面添加directory
  scott@USBO> create directory db_utl_dir as '/u03/database/usbo/db_utl_dir';
  
  --权限授予
  scott@USBO> grant read,write on directory db_utl_dir to public;

b、从SQL查询写入到数据文件
  DECLARE
     vsfile   UTL_FILE.file_type;  --->定义用于接收文件句柄的类型
     v_cnt    PLS_INTEGER := 0;
  BEGIN
     vsfile :=                      
        UTL_FILE.fopen ('DB_UTL_DIR',  --->使用fopen打开文件,定义了文件路径,文件名,读写方式以及每一行字符的最大长度,缺省为1024
                        'emp.txt',
                        'W',
                        200);
  
     FOR i IN (SELECT t.ename || ',' || t.job AS msg    --->使用了一个for循环来读取scott.emp表
                 FROM scott.emp t WHERE t.sal>2000)
     LOOP
        UTL_FILE.put_line (vsfile, i.msg);              --->将for循环查询的内容使用put_line写入到文件
        v_cnt := v_cnt + 1;                             --->计数器,用于统计写入的记录数
     END LOOP;
  
     UTL_FILE.fflush (vsfile);
     UTL_FILE.fclose (vsfile);
     DBMS_OUTPUT.put_line (v_cnt || ' rows unloaded');
  END;
  /
  
  6 rows unloaded
  
  PL/SQL procedure successfully completed.

  --查看产生的文件
  scott@USBO> ho more /u03/database/usbo/db_utl_dir/emp.txt
  JONES,MANAGER
  BLAKE,MANAGER
  CLARK,MANAGER
  SCOTT,ANALYST
  KING,PRESIDENT
  FORD,ANALYST

c、从数据文件读入并写入到表
  scott@USBO> create table tb_emp(val varchar2(30), file_name varchar2(10));
  
  scott@USBO> exec read_demo('emp.txt','db_utl_dir');  -->调用过程来实现,代码见文章尾部
  
  PL/SQL procedure successfully completed.
  
  scott@USBO> select * from tb_emp;
  
  VAL                           FILE_NAME
  ----------------------------- ---------------------
  JONES,MANAGER                 emp.txt
  BLAKE,MANAGER                 emp.txt
  CLARK,MANAGER                 emp.txt
  SCOTT,ANALYST                 emp.txt
  KING,PRESIDENT                emp.txt
  FORD,ANALYST                  emp.txt
  
  6 rows selected.

d、读写混合模式示例
  scott@USBO> set serveroutput on;
  scott@USBO> exec rw_demo;     -->调用过程来实现,代码见文章尾部
  14
  14
  28
  42
  56
  71
  84
  
  PL/SQL procedure successfully completed.
  
  scott@USBO> ho ls 
  out.txt  x.txt
  
  scott@USBO> ho more out.txt
  JONES,MANAGER
  JONES,MANAGER
  BLAKE,MANAGER
  CLARK,MANAGER
  SCOTT,ANALYST
  KING,PRESIDENT
  FORD,ANALYST

e、演示中用到的过程
  --下面是读模式的过程代码
  CREATE OR REPLACE PROCEDURE read_demo (file_name_in VARCHAR2, utl_dir_in VARCHAR2)
  --两个传入参数,一个用于指定文件名,一个用于指定utl_file_dir目录
  --Author : Leshami
  --Blog   : http://blog.csdn.net/leshami
  IS
     vsfile      UTL_FILE.file_type;
     vnewline    VARCHAR2 (200);
     v_utl_dir   VARCHAR2 (30);
  BEGIN
     v_utl_dir := UPPER (utl_dir_in);
     vsfile := UTL_FILE.fopen (v_utl_dir, file_name_in, 'r');   --->打开文件
  
     IF UTL_FILE.is_open (vsfile)
     THEN
        LOOP
           BEGIN
              UTL_FILE.get_line (vsfile, vnewline);   -->从文件读入行
  
              IF vnewline IS NULL
              THEN
                 EXIT;
              END IF;
  
              INSERT INTO tb_emp (val, file_name)     --->将读入的行插入到表
                   VALUES (vnewline, file_name_in);
           EXCEPTION
              WHEN NO_DATA_FOUND
              THEN
                 EXIT;
           END;
        END LOOP;
  
        COMMIT;
     END IF;
  
     UTL_FILE.fclose (vsfile);                       --->关闭打开的文件
     UTL_FILE.frename (v_utl_dir,                    --->此处进行了重命名
                       file_name_in,
                       v_utl_dir,
                       'x.txt',
                       TRUE);
  EXCEPTION                                          --->定义了相关的异常信息
     WHEN UTL_FILE.invalid_mode
     THEN
        raise_application_error (-20051, 'Invalid Mode Parameter');
     WHEN UTL_FILE.invalid_path
     THEN
        raise_application_error (-20052, 'Invalid File Location');
     WHEN UTL_FILE.invalid_filehandle
     THEN
        raise_application_error (-20053, 'Invalid Filehandle');
     WHEN UTL_FILE.invalid_operation
     THEN
        raise_application_error (-20054, 'Invalid Operation');
     WHEN UTL_FILE.read_error
     THEN
        raise_application_error (-20055, 'Read Error');
     WHEN UTL_FILE.internal_error
     THEN
        raise_application_error (-20057, 'Internal Error');
     WHEN UTL_FILE.charsetmismatch
     THEN
        raise_application_error (-20058, 'Opened With FOPEN_NCHAR
      But Later I/O Inconsistent');
     WHEN UTL_FILE.file_open
     THEN
        raise_application_error (-20059, 'File Already Opened');
     WHEN UTL_FILE.invalid_maxlinesize
     THEN
        raise_application_error (-20060, 'Line Size Exceeds 32K');
     WHEN UTL_FILE.invalid_filename
     THEN
        raise_application_error (-20061, 'Invalid File Name');
     WHEN UTL_FILE.access_denied
     THEN
        raise_application_error (-20062, 'File Access Denied By');
     WHEN UTL_FILE.invalid_offset
     THEN
        raise_application_error (-20063, 'FSEEK Param Less Than 0');
     WHEN OTHERS
     THEN
        raise_application_error (-20099, 'Unknown UTL_FILE Error');
  END read_demo;
  /
  
  --下面是读写模式过程的代码,这个过程实现了从一个数据文件读出并写入到另外一个数据文件
  CREATE OR REPLACE PROCEDURE rw_demo
  IS
     infile     UTL_FILE.file_type;
     outfile    UTL_FILE.file_type;
     vnewline   VARCHAR2 (4000);
     i          PLS_INTEGER;
     j          PLS_INTEGER := 0;
     seekflag   BOOLEAN := TRUE;
  BEGIN
     -- open a file to read
     infile := UTL_FILE.fopen ('DB_UTL_DIR', 'x.txt', 'r');       -->打开源文件用于读取数据
     -- open a file to write
     outfile := UTL_FILE.fopen ('DB_UTL_DIR', 'out.txt', 'w');   -->创建目标文件用于存放数据
  
     -- if the file to read was successfully opened
     IF UTL_FILE.is_open (infile)
     THEN
        -- loop through each line in the file
        LOOP
           BEGIN
              UTL_FILE.get_line (infile, vnewline);                   -->从源文件读取行
  
              i := UTL_FILE.fgetpos (infile);                      -->将行的位置赋值并输出
              DBMS_OUTPUT.put_line (TO_CHAR (i));
  
              UTL_FILE.put_line (outfile, vnewline, FALSE);  -->将得到的数据行写出到文件句柄缓冲
              UTL_FILE.fflush (outfile);                        -->将数据行从缓冲区写入到文件
  
              IF seekflag = TRUE
              THEN
                 UTL_FILE.fseek (infile, NULL, -30);            -->用于调整文件指针,即偏移量
                 seekflag := FALSE;
              END IF;
           EXCEPTION
              WHEN NO_DATA_FOUND
              THEN
                 EXIT;
           END;
        END LOOP;
  
        COMMIT;
     END IF;
  
     UTL_FILE.fclose (infile);                                          -->关闭源文件
     UTL_FILE.fclose (outfile);                                        -->关闭目标文件
  EXCEPTION
     WHEN OTHERS
     THEN
        raise_application_error (-20099, 'Unknown UTL_FILE Error');
  END rw_demo;
  /
  
注意在使用UTL_FILE包用到DIRECTORY数据库对象时,名字一定要大写,否则会遭遇“ORA-29280: invalid directory path”错误
主要参考: 
http://psoug.org/reference/utl_file.html http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/u_file.htm#BABGGEDF

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

使用 DBMS_REPAIR 修复坏块

       对于Oracle数据块物理损坏的情形,在我们有备份的情况下可以直接使用备份来恢复。对于通过备份恢复,Oracel为我们提供了很多种方式,冷备,基于...

9820
来自专栏杨建荣的学习笔记

ORA-01427问题的分析和解决(r6笔记第51天)

前几天开发的同事反馈一个问题,说前台系统报出了ORA错误,希望我们能看看是什么原因。 java.sql.SQLException: ORA-01427: sin...

28540
来自专栏乐沙弥的世界

Oracle 角色、配置文件

增加或删除角色中的某一权限,被授予该角色的所有用户或角色自动地获得新增权限或删除旧的权限

10820
来自专栏乐沙弥的世界

使用带dblink方式的datapump迁移Oracle 10g到11g

      对于从Oracle 10g下迁移数据库到Oracle 11g,除了使用RMAN方式之外,我们可以使用带dblink的datapump方式来实现基于逻...

7210
来自专栏菩提树下的杨过

mybatis 使用经验小结

一、多数据源问题 主要思路是把dataSource、sqlSesstionFactory、MapperScannerConfigurer在配置中区分开,各Map...

29660
来自专栏杨建荣的学习笔记

关于dual表的破坏性测试(r3笔记第60天)

关于dual表的破坏性测试,既然是破坏性测试,就需要确定这个测试仅限于测试或者个人学习所用,可能有些sql看似极为简单,但是一旦运行就会导致整个业务系统崩溃。 ...

407130
来自专栏杨建荣的学习笔记

记一次数据库重启后归档急剧增加的问题(98天)

在本地的环境中测试外部表的性能,由于空间有限,不一会儿归档的空间就爆了。然后文件貌似出现了系统级的问题,刚刚rm掉的归档日志文件。隔了几秒钟再ls,就出现了。怎...

36640
来自专栏杨建荣的学习笔记

merge语句导致的CPU使用率过高的优化(r7笔记第4天)

今天有一个数据库有点反常,早上的时候报出了CPU使用率的警告。 警告内容如下: ZABBIX-监控系统: -----------------------...

34050
来自专栏杨建荣的学习笔记

简单实用的sql小技巧(第一篇) (r3笔记第36天)

今天和大家简单分享几个实用的sql小技巧。还有一些还在整理中,会不断的分享出来。 有些其实也不算是sql的技巧,可能大家在写sql语句的时候没有意识到我们可以通...

31230
来自专栏杨建荣的学习笔记

不经意发现的dba_objects和dba_tables中的细节(r7笔记第56天)

今天有一个同学问我一个问题,因为白天比较忙也没有在意,在下班后坐地铁的时候抽空看了这个问题,感觉还是蛮有意思的。但是当时也没有任何答案,就准备自己回去好好实验一...

39030

扫码关注云+社区

领取腾讯云代金券