utl_file包使用浅析

With the UTL_FILE package, PL/SQL programs can read and write operating system text files. UTL_FILE provides a restricted version of operating system stream file I/O.

SYS@PROD1> desc utl_file
PROCEDURE FCLOSE   --Closes a file
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE				RECORD			IN/OUT
   ID				BINARY_INTEGER		IN/OUT
   DATATYPE			BINARY_INTEGER		IN/OUT
   BYTE_MODE			BOOLEAN 		IN/OUT
PROCEDURE FCLOSE_ALL  --Closes all open file handles
PROCEDURE FCOPY  --Copies a contiguous portion of a file to a newly created file
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SRC_LOCATION			VARCHAR2		IN
 SRC_FILENAME			VARCHAR2		IN
 DEST_LOCATION			VARCHAR2		IN
 DEST_FILENAME			VARCHAR2		IN
 START_LINE			BINARY_INTEGER		IN     DEFAULT
 END_LINE			BINARY_INTEGER		IN     DEFAULT
PROCEDURE FFLUSH  --Physically writes all pending output to a file
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE				RECORD			IN
   ID				BINARY_INTEGER		IN
   DATATYPE			BINARY_INTEGER		IN
   BYTE_MODE			BOOLEAN 		IN
PROCEDURE FGETATTR	-Reads and returns the attributes of a disk file
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOCATION			VARCHAR2		IN
 FILENAME			VARCHAR2		IN
 FEXISTS			BOOLEAN 		OUT
 FILE_LENGTH			NUMBER			OUT
 BLOCK_SIZE			BINARY_INTEGER		OUT
FUNCTION FGETPOS RETURNS BINARY_INTEGER  --Returns the current relative offset position within a file, in bytes
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE				RECORD			IN
   ID				BINARY_INTEGER		IN
   DATATYPE			BINARY_INTEGER		IN
   BYTE_MODE			BOOLEAN 		IN
FUNCTION FOPEN RETURNS RECORD  --Opens a file for input or output
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
   ID				BINARY_INTEGER		OUT
   DATATYPE			BINARY_INTEGER		OUT
   BYTE_MODE			BOOLEAN 		OUT
 LOCATION			VARCHAR2		IN
 FILENAME			VARCHAR2		IN
 OPEN_MODE			VARCHAR2		IN
 MAX_LINESIZE			BINARY_INTEGER		IN     DEFAULT
FUNCTION FOPEN_NCHAR RETURNS RECORD  --Opens a file in Unicode for input or output
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
   ID				BINARY_INTEGER		OUT
   DATATYPE			BINARY_INTEGER		OUT
   BYTE_MODE			BOOLEAN 		OUT
 LOCATION			VARCHAR2		IN
 FILENAME			VARCHAR2		IN
 OPEN_MODE			VARCHAR2		IN
 MAX_LINESIZE			BINARY_INTEGER		IN     DEFAULT
PROCEDURE FREMOVE  --Deletes a disk file, assuming that you have sufficient privileges
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOCATION			VARCHAR2		IN
 FILENAME			VARCHAR2		IN
PROCEDURE FRENAME	-Renames an existing file to a new name, similar to the UNIX mv function
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SRC_LOCATION			VARCHAR2		IN
 SRC_FILENAME			VARCHAR2		IN
 DEST_LOCATION			VARCHAR2		IN
 DEST_FILENAME			VARCHAR2		IN
 OVERWRITE			BOOLEAN 		IN     DEFAULT
PROCEDURE FSEEK  --Adjusts the file pointer forward or backward within the file by the number of bytes specified
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE				RECORD			IN/OUT
   ID				BINARY_INTEGER		IN/OUT
   DATATYPE			BINARY_INTEGER		IN/OUT
   BYTE_MODE			BOOLEAN 		IN/OUT
 ABSOLUTE_OFFSET		BINARY_INTEGER		IN     DEFAULT
 RELATIVE_OFFSET		BINARY_INTEGER		IN     DEFAULT
PROCEDURE GET_LINE	--Reads text from an open file
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE				RECORD			IN
   ID				BINARY_INTEGER		IN
   DATATYPE			BINARY_INTEGER		IN
   BYTE_MODE			BOOLEAN 		IN
 BUFFER 			VARCHAR2		OUT
 LEN				BINARY_INTEGER		IN     DEFAULT
PROCEDURE GET_LINE_NCHAR	--Reads text in Unicode from an open file
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE				RECORD			IN
   ID				BINARY_INTEGER		IN
   DATATYPE			BINARY_INTEGER		IN
   BYTE_MODE			BOOLEAN 		IN
 BUFFER 			NVARCHAR2		OUT
 LEN				BINARY_INTEGER		IN     DEFAULT
PROCEDURE GET_RAW	--Reads a RAW string value from a file and adjusts the file pointer ahead by the number of bytes read
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE				RECORD			IN
   ID				BINARY_INTEGER		IN
   DATATYPE			BINARY_INTEGER		IN
   BYTE_MODE			BOOLEAN 		IN
 BUFFER 			RAW			OUT
 LEN				BINARY_INTEGER		IN     DEFAULT
FUNCTION IS_OPEN RETURNS BOOLEAN	--Determines if a file handle refers to an open file
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE				RECORD			IN
   ID				BINARY_INTEGER		IN
   DATATYPE			BINARY_INTEGER		IN
   BYTE_MODE			BOOLEAN 		IN
PROCEDURE NEW_LINE	--Writes one or more operating system-specific line terminators to a file
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE				RECORD			IN
   ID				BINARY_INTEGER		IN
   DATATYPE			BINARY_INTEGER		IN
   BYTE_MODE			BOOLEAN 		IN
 LINES				BINARY_INTEGER		IN     DEFAULT
PROCEDURE PUT	--Writes a string to a file
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE				RECORD			IN
   ID				BINARY_INTEGER		IN
   DATATYPE			BINARY_INTEGER		IN
   BYTE_MODE			BOOLEAN 		IN
 BUFFER 			VARCHAR2		IN
PROCEDURE PUTF	--A PUT procedure with formatting
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE				RECORD			IN
   ID				BINARY_INTEGER		IN
   DATATYPE			BINARY_INTEGER		IN
   BYTE_MODE			BOOLEAN 		IN
 FORMAT 			VARCHAR2		IN
 ARG1				VARCHAR2		IN     DEFAULT
 ARG2				VARCHAR2		IN     DEFAULT
 ARG3				VARCHAR2		IN     DEFAULT
 ARG4				VARCHAR2		IN     DEFAULT
 ARG5				VARCHAR2		IN     DEFAULT
PROCEDURE PUTF_NCHAR
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE				RECORD			IN
   ID				BINARY_INTEGER		IN
   DATATYPE			BINARY_INTEGER		IN
   BYTE_MODE			BOOLEAN 		IN
 FORMAT 			NVARCHAR2		IN
 ARG1				NVARCHAR2		IN     DEFAULT
 ARG2				NVARCHAR2		IN     DEFAULT
 ARG3				NVARCHAR2		IN     DEFAULT
 ARG4				NVARCHAR2		IN     DEFAULT
 ARG5				NVARCHAR2		IN     DEFAULT
PROCEDURE PUT_LINE	-Writes a line to a file, and so appends an operating system-specific line terminator
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE				RECORD			IN
   ID				BINARY_INTEGER		IN
   DATATYPE			BINARY_INTEGER		IN
   BYTE_MODE			BOOLEAN 		IN
 BUFFER 			VARCHAR2		IN
 AUTOFLUSH			BOOLEAN 		IN     DEFAULT
PROCEDURE PUT_LINE_NCHAR
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE				RECORD			IN
   ID				BINARY_INTEGER		IN
   DATATYPE			BINARY_INTEGER		IN
   BYTE_MODE			BOOLEAN 		IN
 BUFFER 			NVARCHAR2		IN
PROCEDURE PUT_NCHAR
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE				RECORD			IN
   ID				BINARY_INTEGER		IN
   DATATYPE			BINARY_INTEGER		IN
   BYTE_MODE			BOOLEAN 		IN
 BUFFER 			NVARCHAR2		IN
PROCEDURE PUT_RAW
 Argument Name			Type			In/Out Default?
 ------------------------------ ----------------------- ------ --------
 FILE				RECORD			IN
   ID				BINARY_INTEGER		IN
   DATATYPE			BINARY_INTEGER		IN
   BYTE_MODE			BOOLEAN 		IN
 BUFFER 			RAW			IN
 AUTOFLUSH			BOOLEAN 		IN     DEFAULT

--实验

EODA@PROD1> create or replace directory ext as '/home/oracle';	--创建路径文件夹

Directory created.

SYS@PROD1> grant read,write on directory ext to eoda;	--授予读写目录权限给用户

Grant succeeded.

SYS@PROD1> grant execute on utl_file to eoda;	--将UTL_FILE包授权给用户

Grant succeeded.

EODA@PROD1> set echo on
EODA@PROD1> DECLARE
  2  	     v1 pls_integer := 0;
  3  	     f1 utl_file.file_type;  --定义文件类型
  4  begin
  5  	     f1 := utl_file.fopen('EXT', 'abc.sql', 'W', 200);  --打开文件
  6  	     for i in (select t.ename || ',' || t.job as msg from scott.emp t where t.sal>2000)
  7  	     loop
  8  		     utl_file.put_line(f1, i.msg);  --逐行写入
  9  		     v1 := v1 + 1;
 10  	     end loop;
 11  	     utl_file.fflush(f1);
 12  	     utl_file.fclose(f1);  --关闭文件
 13  	     dbms_output.put_line(v1 || ' rows unloaded');
 14  end;
 15  /
6 rows unloaded

PL/SQL procedure successfully completed.

EODA@PROD1> ho cat /home/oracle/abc.sql
CLARK,MANAGER
KING,PRESIDENT
JONES,MANAGER
SCOTT,ANALYST
FORD,ANALYST
BLAKE,MANAGER