Oracle的UTL_FILE使用

Oracle提供的文件操作包UTL_FILE包中的UTL_FILE.FOPEN负责打开一个文件。

UTL_FILE.FOPEN(location in varchar2, filename in varchar2, open_mode in varchar2) return FILE_TYPE;
Location 是路径参数，
FILENAME 是文件名，
OPEN_MODE是打开模式，'R'是读文本，'W'是写文本，'A'是附加文本，参数不分大小写，如果指定'A'但是文件不存在，它会用'W'先创建出来，'W'有覆盖的功能，'rb'是read byte mode，'wb'是write byte mode，'ab'是append byte mode；

其中的location并不能简单的指定为'D:\temp'等路径，要建立一个DIRECTORY变量并付给权限（必须以DBA身份登录）：

create or replace directory D_OUTPUT as 'D:\TEMP';
grant read,write on directory D_OUTPUT to testdb;
GRANT EXECUTE ON utl_file TO testdb;

之后就可以用UTL_FILE包建立文件了

V_FILE UTL_FILE.FILE_TYPE;
V_FILE := UTL_FILE.FOPEN('D_OUTPUT', 'Data.txt', 'w');

就可以在数据库服务器的D:\TEMP建立Data.txt

##读的例子

create table test (
fld1 VARCHAR2(20),
fld2 VARCHAR2(20));

CREATE OR REPLACE PROCEDURE read_demo(file_name VARCHAR2) IS
 vSFile   utl_file.file_type;
 vNewLine VARCHAR2(200);
BEGIN
  vSFile := utl_file.fopen('ORALOAD', file_name,'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 test
        (fld1, fld2)
        VALUES
        (vNewLine, file_name);
      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          EXIT;
      END;
    END LOOP;
    COMMIT;
  END IF;
  utl_file.fclose(vSFile);
  utl_file.frename('ORALOAD', 'test.txt', 'ORALOAD', '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;
/

 

##Read-Write Demo

--This demo writes out a Korn Shell script to run SQL*Loader   

CREATE OR REPLACE PROCEDURE create_cmd_file AS
 CURSOR sll_cur IS
 SELECT loadname, loadfilename, loadfiledate
 FROM sqlldrlog
 WHERE run_status = 'B'
 ORDER BY sequenceno;

 sll_rec   sll_cur%ROWTYPE;

 DirLoc    VARCHAR2(30) := 'ORALOAD';
 LFileName sqlldrlog.loadfilename%TYPE;
 LFileDate sqlldrlog.loadfiledate%TYPE;
 ctl_file  VARCHAR2(500);
 dat_file  VARCHAR2(500);
 log_file  VARCHAR2(500);
 bad_file  VARCHAR2(500);
 Emsg      VARCHAR2(90) := 'Load CREATE_CMD_FILE Failed with ERROR ';
 vSubject := 'SQL Loader Failure Notification';
 DayFile  utl_file.file_type;
 LogFile  utl_file.file_type;
BEGIN
  DayFile := utl_file.fopen(DirLoc, 'execsqlldr.ksh','W');
  LogFile := utl_file.fopen(DirLoc, 'log_list.dat','W');

  OPEN sll_cur;
  LOOP
    FETCH sll_cur INTO sll_rec;
    EXIT WHEN sll_cur%NOTFOUND;

    ctl_file := '/data/cload/ctl/'|| LOWER(sll_rec.loadname) || '.ctl \';

    dat_file := '/data/cload/data/' || sll_rec.loadfilename || ' \';

    log_file := '/data/cload/log/' || LOWER(sll_rec.loadname) || '_' || TO_CHAR(sll_rec.loadfiledate, 'YYYYMMDD') || '.log \';

        bad_file := '/data/cload/bad/' || LOWER(sll_rec.loadname) || '_' || TO_CHAR(sll_rec.loadfiledate, 'YYYYMMDD') || '.bad';

    utl_file.putf(dayfile, 'sqlldr userid=%s control=%s data=%s log=%s bad=%s ', '/ \',
ctl_file, dat_file, log_file, bad_file);

    log_file := '/data/cload/log/' || LOWER(sll_rec.loadname) || '_' || TO_CHAR(sll_rec.loadfiledate, 'YYYYMMDD') || '.log';

    utl_file.putf(logfile,'%s ',log_file);
  END LOOP;
  utl_file.fclose(DayFile);
  utl_file.fclose(LogFile);
EXCEPTION
  WHEN utl_file.invalid_mode THEN
    vErrMsg := SQLERRM;
    vMessage := Emsg || '-20051, Invalid Option';
    sp_SendEmail (0, 'dba@psoug.org', vSubject, vMessage);
  WHEN utl_file.invalid_path THEN
    vErrMsg := SQLERRM;
    vMessage := Emsg || '-20052, Invalid Path';
    sp_SendEmail (0, 'dba@psoug.org', vSubject, vMessage);
  WHEN utl_file.invalid_filehandle THEN
    vErrMsg := SQLERRM;
    vMessage := Emsg || '-20053, Invalid Filehandle';
    sp_SendEmail (0, 'dba@psoug.org', vSubject, vMessage);
  WHEN utl_file.invalid_operation THEN
    vErrMsg := SQLERRM;
    vMessage := Emsg || '-20054, Invalid Operation';
    sp_SendEmail (0, 'dba@psoug.org', vSubject, vMessage);
  WHEN utl_file.read_error THEN
    vErrMsg := SQLERRM;
    vMessage := Emsg || '-20055, Read Error';
    sp_SendEmail (0, 'dba@psoug.org', vSubject, vMessage);
  WHEN utl_file.write_error THEN
    vErrMsg := SQLERRM;
    vMessage := Emsg || '-20056, Write Error';
    sp_SendEmail (0, 'dba@psoug.org', vSubject, vMessage);
  WHEN utl_file.internal_error THEN
    vErrMsg := SQLERRM;
    vMessage := Emsg || '-20057, Internal Error';
  WHEN OTHERS THEN
    vErrMsg := SQLERRM;
    vMessage := Emsg || vErrMsg;
    sp_SendEmail (0, 'dba@psoug.org', vSubject, vMessage);
END create_cmd_file;
/ 

 

 

原文出处：http://psoug.org/reference/utl_file.html