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_filePROCEDURE FCLOSE   --Closes a file Argument NameTypeIn/Out Default? ------------------------------ ----------------------- ------ -------- FILERECORDIN/OUT   IDBINARY_INTEGERIN/OUT   DATATYPEBINARY_INTEGERIN/OUT   BYTE_MODEBOOLEAN IN/OUTPROCEDURE FCLOSE_ALL  --Closes all open file handlesPROCEDURE FCOPY  --Copies a contiguous portion of a file to a newly created file Argument NameTypeIn/Out Default? ------------------------------ ----------------------- ------ -------- SRC_LOCATIONVARCHAR2IN SRC_FILENAMEVARCHAR2IN DEST_LOCATIONVARCHAR2IN DEST_FILENAMEVARCHAR2IN START_LINEBINARY_INTEGERIN     DEFAULT END_LINEBINARY_INTEGERIN     DEFAULTPROCEDURE FFLUSH  --Physically writes all pending output to a file Argument NameTypeIn/Out Default? ------------------------------ ----------------------- ------ -------- FILERECORDIN   IDBINARY_INTEGERIN   DATATYPEBINARY_INTEGERIN   BYTE_MODEBOOLEAN INPROCEDURE FGETATTR-Reads and returns the attributes of a disk file Argument NameTypeIn/Out Default? ------------------------------ ----------------------- ------ -------- LOCATIONVARCHAR2IN FILENAMEVARCHAR2IN FEXISTSBOOLEAN OUT FILE_LENGTHNUMBEROUT BLOCK_SIZEBINARY_INTEGEROUTFUNCTION FGETPOS RETURNS BINARY_INTEGER  --Returns the current relative offset position within a file, in bytes Argument NameTypeIn/Out Default? ------------------------------ ----------------------- ------ -------- FILERECORDIN   IDBINARY_INTEGERIN   DATATYPEBINARY_INTEGERIN   BYTE_MODEBOOLEAN INFUNCTION FOPEN RETURNS RECORD  --Opens a file for input or output Argument NameTypeIn/Out Default? ------------------------------ ----------------------- ------ --------   IDBINARY_INTEGEROUT   DATATYPEBINARY_INTEGEROUT   BYTE_MODEBOOLEAN OUT LOCATIONVARCHAR2IN FILENAMEVARCHAR2IN OPEN_MODEVARCHAR2IN MAX_LINESIZEBINARY_INTEGERIN     DEFAULTFUNCTION FOPEN_NCHAR RETURNS RECORD  --Opens a file in Unicode for input or output Argument NameTypeIn/Out Default? ------------------------------ ----------------------- ------ --------   IDBINARY_INTEGEROUT   DATATYPEBINARY_INTEGEROUT   BYTE_MODEBOOLEAN OUT LOCATIONVARCHAR2IN FILENAMEVARCHAR2IN OPEN_MODEVARCHAR2IN MAX_LINESIZEBINARY_INTEGERIN     DEFAULTPROCEDURE FREMOVE  --Deletes a disk file, assuming that you have sufficient privileges Argument NameTypeIn/Out Default? ------------------------------ ----------------------- ------ -------- LOCATIONVARCHAR2IN FILENAMEVARCHAR2INPROCEDURE FRENAME-Renames an existing file to a new name, similar to the UNIX mv function Argument NameTypeIn/Out Default? ------------------------------ ----------------------- ------ -------- SRC_LOCATIONVARCHAR2IN SRC_FILENAMEVARCHAR2IN DEST_LOCATIONVARCHAR2IN DEST_FILENAMEVARCHAR2IN OVERWRITEBOOLEAN IN     DEFAULTPROCEDURE FSEEK  --Adjusts the file pointer forward or backward within the file by the number of bytes specified Argument NameTypeIn/Out Default? ------------------------------ ----------------------- ------ -------- FILERECORDIN/OUT   IDBINARY_INTEGERIN/OUT   DATATYPEBINARY_INTEGERIN/OUT   BYTE_MODEBOOLEAN IN/OUT ABSOLUTE_OFFSETBINARY_INTEGERIN     DEFAULT RELATIVE_OFFSETBINARY_INTEGERIN     DEFAULTPROCEDURE GET_LINE--Reads text from an open file Argument NameTypeIn/Out Default? ------------------------------ ----------------------- ------ -------- FILERECORDIN   IDBINARY_INTEGERIN   DATATYPEBINARY_INTEGERIN   BYTE_MODEBOOLEAN IN BUFFER VARCHAR2OUT LENBINARY_INTEGERIN     DEFAULTPROCEDURE GET_LINE_NCHAR--Reads text in Unicode from an open file Argument NameTypeIn/Out Default? ------------------------------ ----------------------- ------ -------- FILERECORDIN   IDBINARY_INTEGERIN   DATATYPEBINARY_INTEGERIN   BYTE_MODEBOOLEAN IN BUFFER NVARCHAR2OUT LENBINARY_INTEGERIN     DEFAULTPROCEDURE GET_RAW--Reads a RAW string value from a file and adjusts the file pointer ahead by the number of bytes read Argument NameTypeIn/Out Default? ------------------------------ ----------------------- ------ -------- FILERECORDIN   IDBINARY_INTEGERIN   DATATYPEBINARY_INTEGERIN   BYTE_MODEBOOLEAN IN BUFFER RAWOUT LENBINARY_INTEGERIN     DEFAULTFUNCTION IS_OPEN RETURNS BOOLEAN--Determines if a file handle refers to an open file Argument NameTypeIn/Out Default? ------------------------------ ----------------------- ------ -------- FILERECORDIN   IDBINARY_INTEGERIN   DATATYPEBINARY_INTEGERIN   BYTE_MODEBOOLEAN INPROCEDURE NEW_LINE--Writes one or more operating system-specific line terminators to a file Argument NameTypeIn/Out Default? ------------------------------ ----------------------- ------ -------- FILERECORDIN   IDBINARY_INTEGERIN   DATATYPEBINARY_INTEGERIN   BYTE_MODEBOOLEAN IN LINESBINARY_INTEGERIN     DEFAULTPROCEDURE PUT--Writes a string to a file Argument NameTypeIn/Out Default? ------------------------------ ----------------------- ------ -------- FILERECORDIN   IDBINARY_INTEGERIN   DATATYPEBINARY_INTEGERIN   BYTE_MODEBOOLEAN IN BUFFER VARCHAR2INPROCEDURE PUTF--A PUT procedure with formatting Argument NameTypeIn/Out Default? ------------------------------ ----------------------- ------ -------- FILERECORDIN   IDBINARY_INTEGERIN   DATATYPEBINARY_INTEGERIN   BYTE_MODEBOOLEAN IN FORMAT VARCHAR2IN ARG1VARCHAR2IN     DEFAULT ARG2VARCHAR2IN     DEFAULT ARG3VARCHAR2IN     DEFAULT ARG4VARCHAR2IN     DEFAULT ARG5VARCHAR2IN     DEFAULTPROCEDURE PUTF_NCHAR Argument NameTypeIn/Out Default? ------------------------------ ----------------------- ------ -------- FILERECORDIN   IDBINARY_INTEGERIN   DATATYPEBINARY_INTEGERIN   BYTE_MODEBOOLEAN IN FORMAT NVARCHAR2IN ARG1NVARCHAR2IN     DEFAULT ARG2NVARCHAR2IN     DEFAULT ARG3NVARCHAR2IN     DEFAULT ARG4NVARCHAR2IN     DEFAULT ARG5NVARCHAR2IN     DEFAULTPROCEDURE PUT_LINE-Writes a line to a file, and so appends an operating system-specific line terminator Argument NameTypeIn/Out Default? ------------------------------ ----------------------- ------ -------- FILERECORDIN   IDBINARY_INTEGERIN   DATATYPEBINARY_INTEGERIN   BYTE_MODEBOOLEAN IN BUFFER VARCHAR2IN AUTOFLUSHBOOLEAN IN     DEFAULTPROCEDURE PUT_LINE_NCHAR Argument NameTypeIn/Out Default? ------------------------------ ----------------------- ------ -------- FILERECORDIN   IDBINARY_INTEGERIN   DATATYPEBINARY_INTEGERIN   BYTE_MODEBOOLEAN IN BUFFER NVARCHAR2INPROCEDURE PUT_NCHAR Argument NameTypeIn/Out Default? ------------------------------ ----------------------- ------ -------- FILERECORDIN   IDBINARY_INTEGERIN   DATATYPEBINARY_INTEGERIN   BYTE_MODEBOOLEAN IN BUFFER NVARCHAR2INPROCEDURE PUT_RAW Argument NameTypeIn/Out Default? ------------------------------ ----------------------- ------ -------- FILERECORDIN   IDBINARY_INTEGERIN   DATATYPEBINARY_INTEGERIN   BYTE_MODEBOOLEAN IN BUFFER RAWIN AUTOFLUSHBOOLEAN 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 onEODA@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 unloadedPL/SQL procedure successfully completed.EODA@PROD1> ho cat /home/oracle/abc.sqlCLARK,MANAGERKING,PRESIDENTJONES,MANAGERSCOTT,ANALYSTFORD,ANALYSTBLAKE,MANAGER