Oracle 讀寫檔案方法__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);

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;