oracle写文件方式,Oracle 读写文件方法

最新推荐文章于 2024-02-05 11:03:15 发布

护城河河长

最新推荐文章于 2024-02-05 11:03:15 发布

阅读量559

点赞数

文章标签： oracle写文件方式

这里描述的是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); beginv_filepath := V_TEMP; if v_filepath is null thenv_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 Loopv_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 thenv_retvalue :=0; end V3_SUB_FETCH_TEST_2; [2]将文件信息导入到表中和上面一样，先对指定文件路径进行chmod,然后想Oracle的sys.dir$进行路径注册．文件zte.apsuic位于/home/zxin10/file下,其数据格式：1|22|cheng2|33|zhou3|44|heng4|55|yaya表LOADDATA脚本：-- Create tablecreate table LOADDATA(ID VARCHAR2(50),AGE VARCHAR2(50),NAME VARCHAR2(50))tablespace SYSTEMpctfree 10pctused 40initrans 1maxtrans 255storage(initial 64Kminextents 1maxextents 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; beginv_filepath := V_TEMP; if v_filepath is null thenv_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 ，它返回0v_sposition2 := instr(v_str, '|', v_sposition1); --字符串解析正常情况if v_sposition2 <> 0 thenif v_i=1 thenv_id := substr(v_str, v_sposition1, v_sposition2 - v_sposition1); --第一列 elsif v_i=2 thenv_age := substr(v_str, v_sposition1, v_sposition2 - v_sposition1); --第二列elsif v_i=3 thenv_name := substr(v_str, v_sposition1, v_sposition2 - v_sposition1); --第三列 elsereturn; end if; --字符串解析异常情况else if v_i=1 thenv_id := substr(v_str, v_sposition1); --第一列elsif v_i=2 thenv_age := substr(v_str, v_sposition1); --第二列elsif v_i=3 thenv_name := substr(v_str, v_sposition1); --第三列 elsereturn; 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 thenv_retvalue :=0; end V3_SUB_FETCH_TEST_3;