Rocho.J

人脑是不可靠的, 随时记录感悟并且经常重复!

 

关于Oracle中读写文件按的操作 --- 转自360doc.com

关于在Oracle中读写文件的操作

在存储过程中，常常需要对操作系统文件进行读写操作，以前的处理方法是直接将数据库spfle文件中的utl_file_dir参数的值修改为相应的物理路径（默认为空）。
 
但此方法有几个局限性：
1、当物理路径因某种原因需要更改的时候，需要修改程序。
2、当有不同类型的应用都有文件要操作时，不易区分出来（放在一个文件夹中，不利于管理）。
3、经常修改spfile参数文件，显然也不是个好事情。
 
Oracle 提供的 Create directory 功能使得我们可以在Oracle数据库中灵活的对文件进行读写操作，极大的提高了Oracle的易用性和可扩展性。

其语法为:
 
1.创建目录：
CREATE [OR REPLACE] DIRECTORY directory AS 'pathname';
 
2.目录创建以后，就可以把读写权限授予特定用户，具体语法如下:
GRANT READ[,WRITE] ON DIRECTORY directory TO username;
 

看一个简单的测试:
SQL> create or replace directory PIC_FILE_DIR as '/home1/oracle/pic_file';
SQL> grant read, write on directory PIC_FILE_DIR to piccmsuser;
Directory created.
SQL> declare
  2    fhandle utl_file.file_type;
  3  begin
  4    fhandle := utl_file.fopen('PIC_FILE_DIR', 'example.txt', 'w');
  5    utl_file.put_line(fhandle , 'piccmsuser test write one');
  6    utl_file.put_line(fhandle , 'piccmsuser test write two');
  7    utl_file.fclose(fhandle);
  8  end;
  9  /
PL/SQL procedure successfully completed.
SQL> !
[oracle@localhost]$ more /home1/oracle/pic_file/example.txt
piccmsuser test write one
piccmsuser test write two
[oracle@localhost]$
 
 
类似的我们可以通过utl_file来读取文件:
SQL> declare
  2    fhandle   utl_file.file_type;
  3    fp_buffer varchar2(4000);
  4  begin
  5    fhandle := utl_file.fopen ('PIC_FILE_DIR','example.txt', 'R');
  6 
  7    utl_file.get_line (fhandle , fp_buffer );
  8    dbms_output.put_line(fp_buffer );
  9    utl_file.get_line (fhandle , fp_buffer );
 10    dbms_output.put_line(fp_buffer );
 11    utl_file.fclose(fhandle);
 12  end;
 13  /
 
piccmsuser test write one
piccmsuser test write two
PL/SQL procedure successfully completed.
 
 
3.可以查询dba_directories查看所有directory.
SQL> select * from dba_directories;
OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ ------------------------------
SYS                            PIC_FILE_DIR                   /home1/oracle/pic_file
SYS                            BDUMP_DIR                      /opt/oracle/admin/conner/bdump
SYS                            EXP_DIR                        /opt/oracle/utl_file
 
 
4.可以使用drop directory删除这些路径.
SQL> drop directory PIC_FILE_DIR;
Directory dropped
SQL> select * from dba_directories;
OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ ------------------------------
SYS                            BDUMP_DIR                      /opt/oracle/admin/conner/bdump
SYS                            EXP_DIR                        /opt/oracle/utl_file
 
 
例子：
       declare
           -- Create or replace directory UCE_DIR  AS 'D:\UCEDATA\FILEDIR\';
           -- GRANT READ/WRITE/ALL ON DIRECTORY UCE_DIR to TESTUSER;
           Fileid   UTL_FILE.file_type;
           l_line   VARCHAR2 (32767);
           L_EOF    BOOLEAN;
        BEGIN
           ---1)Test writing file
           -- w means Rewrite the file,A means append the file
           fileid := UTL_FILE.fopen ('UCE_DIR', 'TUSER.TXT', 'W');
           FOR emprec IN (SELECT rownum,RPAD(userid,12,' ') USERID,name UNAME FROM TUSER)
           LOOP
              l_line:=RPAD(to_char(emprec.rownum),6,' ')||' '||emprec.userid||'    '||emprec.UNAME;
              UTL_FILE.putf(fileid,'%s',l_line);  --like C language printf ,here f means five stirng parameters
              utl_file.new_line(fileid);     
              --This following row does the same as the two rows upon.
              --Utl_File.put_line(fileid,l_line);
           END LOOP;
           UTL_FILE.fclose (fileid);
           --2)Test Reading file
           fileid :=utl_file.fopen('UCE_DIR', 'TUSER.TXT', 'R');
           begin
             LOOP
               UTL_FILE.get_line (fileid, l_line);
               DBMS_OUTPUT.put_line(l_line);
             END LOOP;
           exception
             WHEN NO_DATA_FOUND THEN
              UTL_FILE.fclose (fileid);
           end;
           --3)Test with clob
        end;
       －－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
       有一点需要注意的,GRANT 中那样写是为了方便。正确的格式请参考其它文档。
       最终输出的结果如下（只摘取部分）：
       －－－－－－－－－－－－－－－－－－－－－－－
        7      000100000005    005 Crazy Stone
        8      000100000006    006 Crazy Stone
        9      000100000007    007 Crazy Stone
        10     000100000001    001 Crazy Stone
        11     000100000008    008 Crazy Stone
        12     000100000009    009 Crazy Stone
      －－－－－－－－－－－－－－－－－－－－－－－－
    注意事项：
         1)在windows操作系统下，目录最后都应该带上反斜杆 “\"，至少在10g r2及其之前的，都应该改如此，否则会出现如下错误提示：
         ORA-29283: 文件操作无效
         ORA-06512: 在"SYS.UTL_FILE", line 449
         ORA-29283: 文件操作无效
         至于oracle会不会在11g或者之后的版本上智能一些（其实很简单的问题），得看oracle心情。
        2)充分注意你的用户在操作系统上对特定目录具有需要的访问权限，否则也会提示错误。
        3)读写二进制文件请用put_raw 或者get_raw ,:)  因该是想当然的事情。
 
例子：
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
--打开ORALOAD目录下的file_name文件
  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;
 
 
例子：
首先我们得先建立一个 ORACLE的目录对象 指向 C:\

create or replace directory DIR as 'C:\';

--然后我们对这个目录对象进行授权 其实这步可以忽略

grant read, write on directory DIR to 用户;

--以上前奏完成了！ 我们可以写PLSQL 进行操作文件了

declare
isto_file utl_file.file_type; --定义变量的类型为utl_file.file_type
begin
isto_file := utl_file.fopen('DIR', 'test.txt', 'W'); --指定为DIR 目录下面的test.txt文件写操作
utl_file.put_line(file, 'hello,world!'); --写入字符串
utl_file.fflush(file); --刷缓冲
utl_file.fclose(file); --关闭文件指针
end;

--下面我们可以使用读操作把写入的文件内容读出来

declare
isto_file utl_file.file_type; --如上
fp_buffer varchar2(4000); --没必要说了吧？
begin
isto_file := utl_file.fopen('DIR', 'test.txt', 'R'); -- 指定为读操作
utl_file.get_line (file , fp_buffer ); --读取一行放到 fp_buffer 变量里面
dbms_output.put_line(fp_buffer);--在终端输出结果看看
utl_file.fclose(file); --关闭文件指针
end;

执行这段的话
出先文件操作无效的提示...
 
 
 
这里描述的是9i及其以上版本的情况，环境为WINDOWS2003上的ORACLE 10G R2.
       不考虑使用初始化参数UTL_FILE_DIR来设置并解决文件的读写，这是比较老套的方式，不灵活。
       新的是使用ORACLE对象DIRECTORY来读写，并可以控制权限.
       下面就是例子：
       －－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
        declare
           -- Create or replace directory UCE_DIR  AS 'D:\UCEDATA\FILEDIR\';
           -- GRANT READ/WRITE/ALL ON DIRECTORY UCE_DIR to TESTUSER;
           Fileid   UTL_FILE.file_type;
           l_line   VARCHAR2 (32767);
           L_EOF    BOOLEAN;
        BEGIN
           ---1)Test writing file
           -- w means Rewrite the file,A means append the file
           fileid := UTL_FILE.fopen ('UCE_DIR', 'TUSER.TXT', 'W');
           FOR emprec IN (SELECT rownum,RPAD(userid,12,' ') USERID,name UNAME FROM TUSER)
           LOOP
              l_line:=RPAD(to_char(emprec.rownum),6,' ')||' '||emprec.userid||'    '||emprec.UNAME;
              UTL_FILE.putf(fileid,'%s',l_line);  --like C language printf ,here f means five stirng parameters
              utl_file.new_line(fileid);     
              --This following row does the same as the two rows upon.
              --Utl_File.put_line(fileid,l_line);
           END LOOP;
           UTL_FILE.fclose (fileid);
           --2)Test Reading file
           fileid :=utl_file.fopen('UCE_DIR', 'TUSER.TXT', 'R');
           begin
             LOOP
               UTL_FILE.get_line (fileid, l_line);
               DBMS_OUTPUT.put_line(l_line);
             END LOOP;
           exception
             WHEN NO_DATA_FOUND THEN
              UTL_FILE.fclose (fileid);
           end;
           --3)Test with clob
        end;
       －－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
       有一点需要注意的,GRANT 中那样写是为了方便。正确的格式请参考其它文档。
       最终输出的结果如下（只摘取部分）：
       －－－－－－－－－－－－－－－－－－－－－－－
        7      000100000005    005 Crazy Stone
        8      000100000006    006 Crazy Stone
        9      000100000007    007 Crazy Stone
        10     000100000001    001 Crazy Stone
        11     000100000008    008 Crazy Stone
        12     000100000009    009 Crazy Stone
      －－－－－－－－－－－－－－－－－－－－－－－－
    注意事项：
         1)在windows操作系统下，目录最后都应该带上反斜杆 “\"，至少在10g r2及其之前的，都应该改如此，否则会出现如下错误提示：
         ORA-29283: 文件操作无效
         ORA-06512: 在"SYS.UTL_FILE", line 449
         ORA-29283: 文件操作无效
         至于oracle会不会在11g或者之后的版本上智能一些（其实很简单的问题），得看oracle心情。
        2)充分注意你的用户在操作系统上对特定目录具有需要的访问权限，否则也会提示错误。
        3)读写二进制文件请用put_raw 或者get_raw ,:)  因该是想当然的事情。
另外一种文章中提到的方法：
来实现两者的交互，这里可以利用UTL_FILE包实现对文件的I/O操作．下面就分别介绍文件写表以及表数据写文件．
[1]表信息导出到文件
在SUSE上建议一个文件夹/home/zxin10/file，然后对其chmod g+w file进行授权(否则无法导出到文件)，再对您指定的路径(/home/zxin10/file)向Oracle的系统表sys.dir$进行注册(否则也是无法成功将信息导出到文件),操作完后可以查询sys.dir$可以看到表中的OS_PATH中有您指定的路径位置．
注册方式：执行SQL语句create or replace directory BBB as '/home/zxin10/file'; 　即可
存储过程如下：(写文件时,文件名可以不用先创建,程序中会自动创建指定文件)
CREATE OR REPLACE PROCEDURE V3_SUB_FETCH_TEST_2
(
V_TEMP VARCHAR2,
--1为成功，0为失败
v_retvalue OUT NUMBER
)
AS
--游标定义
type ref_cursor_type is REF CURSOR;
cursor_select ref_cursor_type;
select_cname varchar2(1000);
v_file_handle utl_file.file_type;
v_sql varchar2(1000);
v_filepath Varchar2(500);
v_filename Varchar2(500);
--缓冲区
v_results Varchar2(500);
v_pid varchar2(1000);
v_cpcnshortname Varchar2(500);
begin
v_filepath := V_TEMP;
if v_filepath is null then
v_filepath := '/home/zxin10/file3';
end if;
v_filename:='free_'|| substr(to_char(sysdate,'YYYYMMDDHH24MI'),1,10) ||'.all' ;
--游标开始
select_cname:='select cpid,cpcnshortname from zxdbm_ismp.scp_basic';
--打开一个文件句柄 ,同时fopen的第一个参数必须是大写
v_file_handle:=utl_file.fopen('BBB',v_filename,'A');
Open cursor_select For select_cname;
Fetch cursor_select into v_pid,v_cpcnshortname;
While cursor_select%Found
Loop
v_results := v_pid||'|'||v_cpcnshortname;
--将v_results写入文件
utl_file.put_line(v_file_handle,v_results);
Fetch cursor_select into v_pid,v_cpcnshortname;
End Loop;
Close cursor_select; --关闭游标
utl_file.fClose(v_file_handle); --关闭句柄
v_retvalue :=1;
exception when others then
v_retvalue :=0;
end V3_SUB_FETCH_TEST_2;
 
[2]将文件信息导入到表中
和上面一样，先对指定文件路径进行chmod,然后想Oracle的sys.dir$进行路径注册．
文件zte.apsuic位于/home/zxin10/file下,其数据格式：
1|22|cheng
2|33|zhou
3|44|heng
4|55|yaya

表LOADDATA脚本：
-- Create table
create table LOADDATA
(
ID VARCHAR2(50),
AGE VARCHAR2(50),
NAME VARCHAR2(50)
)
tablespace SYSTEM
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
程序如下：(读取文件时,指定文件名一定要预先存在,否则程序会失败)
create or replace directory BBB as '/home/zxin10/file';
/
--作用法是将特定的文件路径信息想Oracle注册(注册信息存放在sys.dir$表中)
CREATE OR REPLACE PROCEDURE V3_SUB_FETCH_TEST_3
(
--文件中的信息导入表中
V_TEMP VARCHAR2,
v_retvalue OUT NUMBER --1 成功 ，0失败
AS
v_file_handle utl_file.file_type;
v_sql varchar2(1000);
v_filepath Varchar2(500);
v_filename Varchar2(500);
--文件到表字段的映射
v_id varchar2(1000);
v_age varchar2(1000);
v_name varchar2(1000);
--缓冲区
v_str varchar2(1000);
--列指针
v_i number;
--字符串定位解析指针
v_sposition1 number;
v_sposition2 number;
begin
v_filepath := V_TEMP;
if v_filepath is null then
v_filepath := '/home/zxin10/file';
end if;
v_filename:='zte.apsuic';
--v_sql:= 'create or replace directory CCC as '''|| v_filepath || '''';
--execute immediate v_sql;
v_file_handle:=utl_file.fopen('CCC',v_filename,'r');
Loop
--将文件信息读取到缓冲区v_str中,每次读取一行
utl_file.get_line(v_file_handle,v_str);
--dbms_output.put_line(v_str);
--针对每行的列数
v_i := 1;
--针对字符串每次的移动指针
v_sposition1 := 1;
--文件中每行信息3列,循环3次
FOR I IN 1..3 loop
--当instr(v_str, '|', 6)其中v_str为1|22|wuzhuocheng ，它返回0
v_sposition2 := instr(v_str, '|', v_sposition1);
--字符串解析正常情况
if v_sposition2 <> 0 then
if v_i=1 then
v_id := substr(v_str, v_sposition1, v_sposition2 - v_sposition1); --第一列
elsif v_i=2 then
v_age := substr(v_str, v_sposition1, v_sposition2 - v_sposition1); --第二列
elsif v_i=3 then
v_name := substr(v_str, v_sposition1, v_sposition2 - v_sposition1); --第三列
else
return;
end if;
--字符串解析异常情况
else
if v_i=1 then
v_id := substr(v_str, v_sposition1); --第一列
elsif v_i=2 then
v_age := substr(v_str, v_sposition1); --第二列
elsif v_i=3 then
v_name := substr(v_str, v_sposition1); --第三列
else
return;
end if;
end if;
v_sposition1 := v_sposition2 + 1;
v_i := v_i+1;
end loop;
--每列循环完后将信息insert into表中
insert into zxdbm_ismp.loaddata values(v_id,v_age,v_name);
End Loop;
--关闭句柄
utl_file.fClose(v_file_handle);
v_retvalue :=1;
exception when others then
v_retvalue :=0;
end V3_SUB_FETCH_TEST_3;

posted on 2011-03-12 13:57  RJ  阅读(5706)  评论(0)    收藏  举报

导航