--四个参数:目录,文件名,打开方式,最大行数(默认为2000) UTL_FILE.PUTF(file_handle, '写入的信息\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()过程用来以指定格式把文本写入一个文件 --Put_Line()过程把一个指定的字符串写入文件并在文件中开始新的一行
9i中函数说明: 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 /.
2、在sqlplus中运行create or replace directory TMP as 'd:\testtmp'; 在sqlplus中运行grant read on directory TMP to public
3、在scott用户下建立存储过程如: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;