关于在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;
