oracle讀寫檔案--利用utl_file包對磁碟檔案的讀寫操作

標籤：

摘要:使用者提出一個需求,即ORACLE中的一個表格儲存體了照片資訊,欄位類型為BLOB,要求能匯出成檔案形式. 本想寫個C#程式來做,後來想起ORACLE有很多包,功能很好很強大,於是網上參考了些文章完成了. 主要是用了ORACLE的兩個包:UTL_FILE和DBMS_LOB.

實現過程:第一步：以管理使用者登陸設定可操作目錄 --CREATE DIRECTORY privilege is granted only to SYS and SYSTEM by default.create or replace directory BLOBDIR as ‘D:\PIC‘;grant read,write on directory BLOBDIR to sharedb;GRANT EXECUTE ON utl_file TO sharedb;select * from ALL_DIRECTORIES;

第二步：普通使用者登陸,編寫預存程序 CREATE OR REPLACE PROCEDURE GET_PIC_BLOB (i_xh VARCHAR2) ISl_file UTL_FILE.FILE_TYPE;l_buffer RAW(32767);l_amount BINARY_INTEGER := 32767;l_pos INTEGER := 1;l_blob BLOB;l_blob_len INTEGER;BEGINSELECT PIC INTO L_BLOB FROM TB_ZP WHERE PSNNO = i_xh; l_blob_len := DBMS_LOB.GETLENGTH(l_blob); l_file := UTL_FILE.FOPEN(‘BLOBDIR‘,i_xh || ‘.jpg‘,‘WB‘,32767); WHILE l_pos < l_blob_len LOOP    DBMS_LOB.READ (l_blob, l_amount, l_pos, l_buffer);    UTL_FILE.PUT_RAW(l_file, l_buffer, TRUE);    l_pos := l_pos + l_amount; END LOOP; UTL_FILE.FCLOSE(l_file);EXCEPTION  --WHEN NO_DATA_FOUND THEN  --DBMS_OUTPUT.put_line(‘no data : ‘ || i_xh); WHEN OTHERS THEN  IF UTL_FILE.IS_OPEN(l_file) THEN   UTL_FILE.FCLOSE(l_file);  RAISE;  END IF;END GET_PIC_BLOB;

第三步：編寫PL/SQL 塊,迴圈執行該預存程序 複製代碼declare     cursor cur_01 is         select xh from xs_xsjbk where rownum <= 5000 ;begin    for rec_01 in cur_01 loop                GET_PIC_BLOB(rec_01.xh);    end loop;   end;複製代碼

測試結果.取了5000條資料,其中有照片資訊的為3407條.用時1分12秒,感覺還可以.總結:1.由管理員建立可訪問目錄和授權給普通使用者比較重要,一開始沒有注意,總是報非法路徑錯誤,搞了較長時間在這上面.2.預存程序中的NO_DATA_FOUND異常本來是屏顯輸出無照片的學號資訊,但是實際運行時出錯,原因是DBMS_OUTPUT.put_line列印條數過多,於是注釋掉了.3.Oracle本身提供了大量使用的包,如UTL_HTTP,DBMS_OUTPUT等,分別封裝了不同的功能,進行大量的應用程式開發的可能,從而拓展了Oracle的功能.網上參考:

CREATE OR REPLACE PROCEDURE P_WRITE_EMP ASV_FILE UTL_FILE.FILE_TYPE;V_BUFFER VARCHAR2(32767);BEGINV_FILE := UTL_FILE.FOPEN(‘D_OUTPUT‘, ‘EMP‘ || TO_CHAR(SYSDATE, ‘YYYY_MM_DD‘) || ‘.csv‘, ‘w‘, 32767);V_BUFFER := ‘EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO‘;UTL_FILE.PUT_LINE(V_FILE, V_BUFFER);FOR I IN ( SELECT ‘"‘ || EMPNO || ‘","‘ ||  ENAME || ‘","‘ ||  JOB || ‘","‘ ||  MGR || ‘","‘ ||  HIREDATE || ‘","‘ ||  SAL || ‘","‘ ||  COMM || ‘","‘ ||  DEPTNO || ‘"‘ RESULT FROM EMP ) LOOP UTL_FILE.PUT_LINE(V_FILE, I.RESULT); END LOOP; UTL_FILE.FCLOSE(V_FILE); END;PL/SQL 3.3以上的版本中，UTL_FILE包允許使用者通過PL/SQL讀寫作業系統檔案。如下： declarefile_handle UTL_FILE.FILE_TYPE;beginfile_handle := UTL_FILE.FOPEN(‘TMP‘, ‘檔案名稱‘, ‘w‘,[1-32767]);--四個參數：目錄，檔案名稱，開啟檔案，最大行數(預設為2000)UTL_FILE.PUTF(file_handle, ‘寫入的資訊\n‘);UTL_FILE.FCLOSE(file_handle);exceptionWHEN utl_file.invalid_path THENraise_application_error(-20000, ‘ERROR: Invalid path for file or path not in INIT.ORA.‘);end; --PutF()過程用來以指定格式把文本寫入一個檔案--Put_Line()過程把一個指定的字串寫入檔案並在檔案中開始新的一行

CREATE OR REPLACE PROCEDURE pReadFileTest     (FPATH IN STRING,FNAME IN STRING,MAX_NUM IN NUMBER) IS    FILE_HANDLE UTL_FILE.FILE_TYPE;   TEXT_BUFFER STRING(1000);   LINE_NUM NUMBER;BEGIN    DBMS_OUTPUT.PUT_LINE(‘INPUT PATH=‘FPATH);    DBMS_OUTPUT.PUT_LINE(‘INPUT FILENAME=‘FNAME);   LINE_NUM :=0;   BEGIN      FILE_HANDLE := UTL_FILE.FOPEN(FPATH,FNAME,‘R‘,MAX_NUM);     LOOP        LINE_NUM:= LINE_NUM + 1;       UTL_FILE.GET_LINE(FILE_HANDLE,TEXT_BUFFER);       DBMS_OUTPUT.PUT_LINE(‘LINE‘LINE_NUM‘ : ‘TEXT_BUFFER);     END LOOP;   EXCEPTION     WHEN NO_DATA_FOUND THEN       RETURN;     WHEN UTL_FILE.INVALID_PATH THEN       DBMS_OUTPUT.PUT_LINE(‘INVALID PATH‘);     WHEN UTL_FILE.INVALID_MODE THEN        DBMS_OUTPUT.PUT_LINE(‘INVALID MODE‘);     WHEN UTL_FILE.INVALID_FILEHANDLE THEN       DBMS_OUTPUT.PUT_LINE(‘INVALID FILEHANDLE‘);     WHEN UTL_FILE.INVALID_OPERATION THEN       DBMS_OUTPUT.PUT_LINE(‘INVALID OPERATION‘);     WHEN UTL_FILE.READ_ERROR THEN       DBMS_OUTPUT.PUT_LINE(‘READ ERROR‘);     WHEN UTL_FILE.WRITE_ERROR THEN       DBMS_OUTPUT.PUT_LINE(‘WRITE ERROR‘);     WHEN UTL_FILE.INTERNAL_ERROR THEN       DBMS_OUTPUT.PUT_LINE(‘INTERNAL ERROR‘);     WHEN OTHERS THEN        DBMS_OUTPUT.PUT_LINE(SQLERRM);   END;EXCEPTIONWHEN OTHERS THENDBMS_OUTPUT.PUT_LINE(‘OTHER ERROR=‘SQLERRM);END pReadFileTest;

檔案I/O對於資料庫的開發來說顯得很重要，比如如果資料庫中的一部分資料來自於磁碟檔案，那麼就需要使用I/O介面把資料匯入到資料庫中來。在PL/SQL中沒有直接的I/O介面，一般在偵錯工具時可以使用Oracle內建的DBMS_OUTPUT包的put_line函數（即向螢幕進行I/O操作）即可，但是對於磁碟檔案的I/O操作它就無能為力了。其實Oracle同樣也提供了可以進行檔案I/O的實用包-----UTL_FILE包，利用這個實用包提供的函數來實現對磁碟的I/O操作。UTL_FILE包提供了很多實用的函數來進行I/O操作，主要有以下幾個函數：fopen 開啟指定的目錄路徑的檔案。get_line 擷取指定檔案的一行的文本。put_line 向指定的檔案寫入一行文本。fclose 關閉指定的檔案。下面利用這些函數，實現從檔案取資料，然後將資料寫入到相應的資料庫中。

 create or replace procedure loadfiledata(p_path varchar2,p_filename varchar2) as    v_filehandle utl_file.file_type; --定義一個檔案控制代碼  v_text varchar2(100); --存放文本  v_name test_loadfile.name%type;  v_addr_jd test_loadfile.addr_jd%type;  v_region test_loadfile.region%type;  v_firstlocation number;  v_secondlocation number;  v_totalinserted number;  begin  if (p_path is null or p_filename is null) then  goto to_end;  end if;  v_totalinserted:=0;  /*open specified file*/  v_filehandle:=utl_file.fopen(p_path,p_filename,‘r‘);  loop  begin  utl_file.get_line(v_filehandle,v_text);  exception  when no_data_found then  exit;  end ;  v_firstlocation:=instr(v_text,‘,‘,1,1);  v_secondlocation:=instr(v_text,‘,‘,1,2);  v_name:=substr(v_text,1,v_firstlocation-1);  v_addr_jd:=substr(v_text,v_firstlocation+1,v_secondlocation-v_firstlocation-1);  v_region:=substr(v_text,v_secondlocation+1);  /*插入資料庫操作*/  insert into test_loadfile  values (v_name,v_addr_jd,v_region);  commit;  end loop;  <<to_end>>  null;  end loadfiledata;

create or replace procedure test_error(str out varchar2,str2 out varchar2)asbegin  declare     isto_file utl_file.file_type;    err_num number;    i number;    k number;    m number;    err_msg varchar2(100);    fp_buffer varchar2(4000);  begin     isto_file := utl_file.fopen(‘IST0_DIR‘, ‘kj021320.txt‘, ‘W‘);    i:=0;    while (i<2)    loop    utl_file.put_line(isto_file, ‘My‘);    i:=i+1;    end loop;    utl_file.fflush(isto_file);    utl_file.fclose(isto_file);            isto_file := utl_file.fopen(‘IST0_DIR‘, ‘kj021320.txt‘, ‘a‘);    m:=0;    while (m<2)    loop    utl_file.put_line(isto_file, ‘My‘);    m:=m+1;    end loop;    utl_file.fflush(isto_file);    utl_file.fclose(isto_file);        isto_file := utl_file.fopen(‘IST0_DIR‘, ‘kj021320.txt‘, ‘R‘);    str2:=‘‘;     loop    utl_file.get_line (isto_file , fp_buffer );    str2:=str2 || fp_buffer;    end loop;    utl_file.fclose(isto_file);           for j in 1..10  /* for */    loop      k:=11;    end loop;   EXCEPTION  WHEN OTHERS THEN    err_num:=sqlcode; /* 異常num */    err_msg:=substr(sqlerrm,1,100); /* 異常msg */    str:=substr(sqlerrm,1,100);  end; end test_error; 

/*0.為避免目錄修改導致程式的修改，目錄可以定義為一個常量，或ORACLE的directory。1.fopen的模式： R（唯讀），W（讀寫，且首先清除原有資料），A（讀寫，原有資料基礎上追加資料）。  fopen的限制：  （1）目錄和檔案名稱必須合法  （2）目錄必須存在  （3）若為R模式，檔案必須存在  （4）若為W模式，若檔案不存在，則自動建立  （5）若為A模式，則檔案必須存在2.is_open：檢查檔案是否開啟（其實只檢查控制代碼是否為空白，比如fclose_all關閉的檔案，is_open仍返回true）。3.get_line：讀取一行資料到varchar2變數中。  nvarchar2資料使用get_line_nchar；raw資料使用get_raw。  讀取到檔案末尾，產生no_data_found異常。還有如下三種情況也會產生no_data_found異常    （1）無返回行的select    （2）pl/sql集合中未定義的行    （3）使用dbms_lob讀取bfile檔案至末尾。    所以以上四種no_data_found情況在一個pl/sql塊中，要區分捕獲異常4.put    9.fclose：若關閉前緩衝區中仍有未寫入檔案的資料，則觸發write_error異常 .fclose_all:關閉所有開啟的檔案。關閉後，所有檔案的控制代碼不變（仍為非NULL），例如測試中  步驟3使用fclose_all關閉檔案，而is_opened仍為TRUE。但此時檔案以不可讀寫。  原因：fclose傳入檔案控制代碼參數，且為IN OUT模式，調用後將控制代碼設定為NULL，而fclose_all  並未傳入任何檔案控制代碼參數，所以並未修改檔案控制代碼的值（仍保持原值）。6.frename：可以重新命名檔案，也可重新命名路徑（相當於FCOPY+FREMOVE），也可都改變7.putf：put format8.fgetattr：擷取檔案屬性（是否存在，大小，塊大小）*/PROCEDURE prc_utl_fileIS  file_read_handle utl_file.file_type;  file_write_handle utl_file.file_type;  is_opened BOOLEAN;  v_one_line VARCHAR2(1000);  b_file_exist BOOLEAN;  n_file_length NUMBER(10,2);  bi_block_size BINARY_INTEGER;BEGIN  --1.讀/讀寫入模式開啟檔案  file_read_handle := utl_file.fopen(‘TEST_UTL_FILE_DIR_READ‘, ‘orcl_ora_396.trc‘, ‘R‘);  file_write_handle := utl_file.fopen(‘TEST_UTL_FILE_DIR_WRITE‘, ‘TEST_UTL_FILE_DIR_WRITE.txt‘, ‘W‘);  --2.檢查檔案是否開啟  is_opened := utl_file.is_open(file_read_handle);  IF is_opened THEN    dbms_output.put_line(‘file is opened‘);  ELSE    dbms_output.put_line(‘file is not opened‘);  END IF;  --3.讀檔案  LOOP    BEGIN      utl_file.get_line(file_read_handle, v_one_line);      dbms_output.put_line(v_one_line);      -- 4.將讀入結果寫入新檔案中      utl_file.put(file_write_handle, v_one_line);      utl_file.new_line(file_write_handle, 2);      --utl_file.put_line(file_write_handle, v_one_line);      --utl_file.put_line(file_write_handle, v_one_line, TRUE);    EXCEPTION      WHEN no_data_found THEN        EXIT;      WHEN OTHERS THEN         dbms_output.put_line(‘error1:‘||SQLERRM);        EXIT;    END;      END LOOP;    --5.關閉檔案  utl_file.fclose(file_read_handle);  --6確認所有未決的資料都寫到物理檔案中  --utl_file.fflush(file_write_handle);  utl_file.fclose(file_write_handle);  --utl_file.fclose_all;  --6.檢查檔案是否關閉  is_opened := utl_file.is_open(file_read_handle);  IF is_opened THEN    dbms_output.put_line(‘file is still opened‘);  ELSE    dbms_output.put_line(‘file is already closed‘);  END IF;  --7.拷貝檔案  utl_file.fcopy(‘TEST_UTL_FILE_DIR_WRITE‘, ‘TEST_UTL_FILE_DIR_WRITE.txt‘, ‘TEST_UTL_FILE_DIR_WRITE‘, ‘TEST_UTL_FILE_DIR_WRITE_COPY.txt‘, 1, 10);  --8.刪除檔案  utl_file.fcopy(‘TEST_UTL_FILE_DIR_WRITE‘, ‘TEST_UTL_FILE_DIR_WRITE.txt‘, ‘TEST_UTL_FILE_DIR_WRITE‘, ‘TEST_UTL_FILE_DIR_WRITE_COPY_DELETE.txt‘, 1, 10);  utl_file.fremove(‘TEST_UTL_FILE_DIR_WRITE‘, ‘TEST_UTL_FILE_DIR_WRITE_COPY_DELETE.txt‘);  --9.重新命名  --utl_file.frename(‘TEST_UTL_FILE_DIR_WRITE‘, ‘TEST_UTL_FILE_DIR_WRITE_COPY.txt‘, ‘TEST_UTL_FILE_DIR_WRITE‘, ‘TEST_UTL_FILE_DIR_WRITE_COPY_DELETE_RENAME.txt‘, FALSE);  --10.擷取重新命名後的檔案屬性  utl_file.fgetattr(‘TEST_UTL_FILE_DIR_WRITE‘, ‘TEST_UTL_FILE_DIR_WRITE_COPY.txt‘,b_file_exist,n_file_length, bi_block_size);  IF b_file_exist THEN    dbms_output.put_line(‘n_file_length:‘||n_file_length||‘\n‘||‘bi_block_size‘||bi_block_size);  END IF;  END; 

 

 

文章來源：http://www.cnblogs.com/pengyq/archive/2008/08/27/1277739.html

oracle讀寫檔案--利用utl_file包對磁碟檔案的讀寫操作