PL/SQL 3.3以上的版本中，UTL_FILE包允許用戶通過(guò)PL/SQL讀寫(xiě)操作系統(tǒng)文件。如下：
declare
file_handle UTL_FILE.FILE_TYPE;
begin
file_handle := UTL_FILE.FOPEN('TMP', '文件名', 'w',[1-32767]);
--四個(gè)參數(shù)：目錄，文件名，打開(kāi)方式，最大行數(shù)(默認(rèn)為2000)
UTL_FILE.PUTF(file_handle, '寫(xiě)入的信息n');
UTL_FILE.FCLOSE(file_handle);
exception
WHEN utl_file.invalid_path THEN
raise_application_error(-20000, 'ERROR: Invalid path for file or path not in INIT.ORA.');
end;
PutF()過(guò)程用來(lái)以指定格式把文本寫(xiě)入一個(gè)文件
Put_Line()過(guò)程把一個(gè)指定的字符串寫(xiě)入文件并在文件中開(kāi)始新的一行
9i中函數(shù)說(shuō)明：
UTL_FILE.FOPEN (
location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2,
max_linesize IN BINARY_INTEGER)
RETURN file_type;
Parameters Description
location (IN) The directory location of the source file, a DIRECTORY_NAME from the ALL_DIRECTORIES view (case sensitive)
filename File name, including extension (file type), without directory path. In Unix,the filename cannot end with /.
一個(gè)使用文件的測(cè)試
1、用sys用戶登錄到數(shù)據(jù)庫(kù)(ora9i):conn sys/orcl@eai01 as sysdba
2、在sqlplus中運(yùn)行create or replace directory TMP as 'd:testtmp';
在sqlplus中運(yùn)行grant read on directory TMP to public
3、在scott用戶下建立存儲(chǔ)過(guò)程如：pReadFileTest
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;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('OTHER ERROR='||SQLERRM);
END pReadFileTest;
4、在sqlplus中運(yùn)行set serveroutput on
5、exec pReadFileTest('TMP','A.TXT','R')
6、正常輸出
小結(jié)：在9i中可以不在init.ora中增加utl_file_dir目錄，而是在sys用戶下創(chuàng)建一個(gè)directory目錄（實(shí)際上是在sys用戶下的dir$表中增加一個(gè)對(duì)應(yīng)的OS_PATH），然后將對(duì)該directory對(duì)象的讀/寫(xiě)操作的權(quán)限grant給public。
|
