PL/SQL 3.3以上的版本中，UTL_FILE包答應(yīng)用戶通過PL/SQL讀寫操作系統(tǒng)文件。如下： declarefile_handle UTL_FILE.FILE_TYPE;beginfile_handle := UTL_FILE.FOPEN('TMP', '文件名', 'w',[1-32767]); ;--四個參數(shù)：目錄，文件名，打開方式，最大行數(shù)(默認為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()過程把一個指定的字符串寫入文件并在文件中開始新的一行9i中函數(shù)說明：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 /. 一個使用文件的測試1、用sys用戶登錄到數(shù)據(jù)庫(ora9i):conn sys/orcl@eai01 as sysdba2、在sqlplus中運行create or replace directory TMP as 'd:testtmp'; 在sqlplus中運行g(shù)rant read on directory TMP to public3、在scott用戶下建立存儲過程如：pReadFileTestCREATE 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;4、在sqlplus中運行set serveroutput on5、exec pReadFileTest('TMP','A.TXT','R')6、正常輸出小結(jié)：在9i中可以不在init.ora中增加utl_file_dir目錄，而是在sys用戶下創(chuàng)建一個directory目錄（實際上是在sys用戶下的dir$表中增加一個對應(yīng)的OS_PATH），然后將對該directory對象的讀/寫操作的權(quán)限grant給public。