CREATE
OR
REPLACE
PACKAGE
SYS.utl_file
AUTHID
CURRENT_USER
AS
/*
FILE_TYPE - File handle
*/
--utl_file包的操作都需要指定一个file_type型的对象(用FOPEN来装入)
TYPE
file_type
IS
RECORD
(id
BINARY_INTEGER
,
datatype
BINARY_INTEGER
,
byte_mode
BOOLEAN
);
/*
Exceptions
*/
file_open
EXCEPTION
;--打开请求失败,文件已经被打开
charsetmismatch
EXCEPTION
;--文件使用FOPEN_NCHAR打开,但是进行了nonchar操作(如PUTF/GET_LINE)
invalid_path
EXCEPTION
;--错误的路径
invalid_mode
EXCEPTION
;--提供了FOPEN不支持的打开模式
invalid_filehandle
EXCEPTION
;--文件处理错误
invalid_operation
EXCEPTION
;--文件无法打开
read_error
EXCEPTION
;--在read时发生操作系统错误
write_error
EXCEPTION
;--在write时发生操作系统错误
internal_error
EXCEPTION
;--未指定的PLSQL错误
invalid_maxlinesize
EXCEPTION
;--FOPEN的最大行定义错误(必须在1到32767之间)
invalid_filename
EXCEPTION
;--文件名错误,或无法找到
access_denied
EXCEPTION
;--无法进入指定路径
invalid_offset
EXCEPTION
;--比较复杂,不管了
delete_failed
EXCEPTION
;--删除操作发生错误
rename_failed
EXCEPTION
;--重命名操作发生错误
--以下都是自己定义的Exception Errcode.
charsetmismatch_errcode
CONSTANT
PLS_INTEGER
:= -
29298
;
invalid_path_errcode
CONSTANT
PLS_INTEGER
:= -
29280
;
invalid_mode_errcode
CONSTANT
PLS_INTEGER
:= -
29281
;
invalid_filehandle_errcode
CONSTANT
PLS_INTEGER
:= -
29282
;
invalid_operation_errcode
CONSTANT
PLS_INTEGER
:= -
29283
;
read_error_errcode
CONSTANT
PLS_INTEGER
:= -
29284
;
write_error_errcode
CONSTANT
PLS_INTEGER
:= -
29285
;
internal_error_errcode
CONSTANT
PLS_INTEGER
:= -
29286
;
invalid_maxlinesize_errcode
CONSTANT
PLS_INTEGER
:= -
29287
;
invalid_filename_errcode
CONSTANT
PLS_INTEGER
:= -
29288
;
access_denied_errcode
CONSTANT
PLS_INTEGER
:= -
29289
;
invalid_offset_errcode
CONSTANT
PLS_INTEGER
:= -
29290
;
delete_failed_errcode
CONSTANT
PLS_INTEGER
:= -
29291
;
rename_failed_errcode
CONSTANT
PLS_INTEGER
:= -
29292
;
--自定义的错误
PRAGMA
EXCEPTION_INIT
(charsetmismatch, -
29298
);
PRAGMA
EXCEPTION_INIT
(invalid_path, -
29280
);
PRAGMA
EXCEPTION_INIT
(invalid_mode, -
29281
);
PRAGMA
EXCEPTION_INIT
(invalid_filehandle, -
29282
);
PRAGMA
EXCEPTION_INIT
(invalid_operation, -
29283
);
PRAGMA
EXCEPTION_INIT
(read_error, -
29284
);
PRAGMA
EXCEPTION_INIT
(write_error, -
29285
);
PRAGMA
EXCEPTION_INIT
(internal_error, -
29286
);
PRAGMA
EXCEPTION_INIT
(invalid_maxlinesize, -
29287
);
PRAGMA
EXCEPTION_INIT
(invalid_filename, -
29288
);
PRAGMA
EXCEPTION_INIT
(access_denied, -
29289
);
PRAGMA
EXCEPTION_INIT
(invalid_offset, -
29290
);
PRAGMA
EXCEPTION_INIT
(delete_failed, -
29291
);
PRAGMA
EXCEPTION_INIT
(rename_failed, -
29292
);
/*
** FOPEN - open file
**
** As of 8.0.6, you can have a maximum of 50 files open simultaneously.
**
** As of 9.0.2, UTL_FILE allows file system access for directories
** created as database objects. See the CREATE DIRECTORY command.
** Directory object names are case sensitive and must match exactly
** the NAME string in ALL_DIRECTORIES. The LOCATION parameter may be
** either a directory string from the UTL_FILE_DIR init.ora parameter
** or a directory object name.
**
** IN
** location - directory location of file
** filename - file name (including extention)
** open_mode - open mode ('r', 'w', 'a' 'rb', 'wb', 'ab')
--'r'：读文件(清空后再写入)
--'w'：写文件
--'a'：追加文件(即不删除之前内容)
--'rb'：byte mode 读文件(清空后再写入)
--'wb'：byte mode 写文件
--'ab'：byte mode 追加文件(即不删除之前内容)
** max_linesize - maximum number of characters per line, including the
** newline character, for this file.
** Valid values are 1 through 32767 and NULL. A NULL
** value for max_linesize indicates that UTL_FILE should
** calculate an operating system specific value at runtime.
** RETURN
** file_type handle to open file
** EXCEPTIONS
** invalid_path - file location or name was invalid
** invalid_mode - the open_mode string was invalid
** invalid_operation - file could not be opened as requested
** invalid_maxlinesize - specified max_linesize is too large or too small
** access_denied - access to the directory object is denied
*/
FUNCTION
fopen(location
IN
VARCHAR2
,
filename
IN
VARCHAR2
,
open_mode
IN
VARCHAR2
,
max_linesize
IN
BINARY_INTEGER
DEFAULT
NULL
)
RETURN
file_type;
PRAGMA
RESTRICT_REFERENCES
(fopen,
WNDS
,
RNDS
,
TRUST
);
/*
** FOPEN_NCHAR - open file
**
** Note: since NCHAR contains mutibyte character, it is highly recommended
** that the max_linesize is less than 6400.
*/
FUNCTION
fopen_nchar(location
IN
VARCHAR2
,
filename
IN
VARCHAR2
,
open_mode
IN
VARCHAR2
,
max_linesize
IN
BINARY_INTEGER
DEFAULT
NULL
)
RETURN
file_type;
PRAGMA
RESTRICT_REFERENCES
(fopen_nchar,
WNDS
,
RNDS
,
TRUST
);
/*
** IS_OPEN - Test if file handle is open
**
** IN
** file - File handle
**
** RETURN
** BOOLEAN - Is file handle open/valid?
*/
FUNCTION
is_open(file
IN
file_type)
RETURN
BOOLEAN
;
PRAGMA
RESTRICT_REFERENCES
(is_open,
WNDS
,
RNDS
,
WNPS
,
RNPS
,
TRUST
);
/*
** FCLOSE - close an open file
**
** IN
** file - File handle (open)
** EXCEPTIONS
** invalid_filehandle - not a valid file handle
** write_error - OS error occured during write operation
*/
PROCEDURE
fclose(file
IN
OUT
file_type);
PRAGMA
RESTRICT_REFERENCES
(fclose,
WNDS
,
RNDS
,
TRUST
);
/*
** FCLOSE_ALL - close all open files for this session
**
** For Emergency/Cleanup use only. FILE_TYPE handles will not be
** cleared (IS_OPEN will still indicate they are valid)
**
** IN
** file - File handle (open)
** EXCEPTIONS
** write_error - OS error occured during write operation
*/
PROCEDURE
fclose_all;
PRAGMA
RESTRICT_REFERENCES
(fclose_all,
WNDS
,
RNDS
,
TRUST
);
/*
** GET_LINE - Get (read) a line of text from the file
**
** IN
** file - File handle (open in read mode)
** len - input buffer length, default is null, max is 32767
** OUT
** buffer - next line of text in file
** EXCEPTIONS
** no_data_found - reached the end of file
** value_error - line to long to store in buffer
** invalid_filehandle - not a valid file handle
** invalid_operation - file is not open for reading
** - file is open for byte mode access
** read_error - OS error occurred during read
** charsetmismatch - if the file is open for nchar data.
*/
--注意这个函数会读取一行一直到line terminator,然后下一次读取时接着下面的内容
--当到达最后一行时返回no_data_found错误
PROCEDURE
get_line(file
IN
file_type,
buffer
OUT
VARCHAR2
,
len
IN
BINARY_INTEGER
DEFAULT
NULL
);
PRAGMA
RESTRICT_REFERENCES
(get_line,
WNDS
,
RNDS
,
WNPS
,
RNPS
,
TRUST
);
/* GET_LINE_NCHAR - Get (read a line of nchar data from the file.
**
** IN
** file - File handle (open in read mode)
** len - input buffer length, default is null, max is 32767
** OUT
** buffer - next line of text in file
** the data might be convert from UTF8 to current charset.
** EXCEPTIONS
** no_data_found - reached the end of file
** value_error - line to long to store in buffer
** invalid_filehandle - not a valid file handle
** invalid_operation - file is not open for reading
** - file is open for byte mode access
** read_error - OS error occurred during read
** charsetmismatch - if the file is open for char data.
*/
PROCEDURE
get_line_nchar(file
IN
file_type,
buffer
OUT
NVARCHAR2
,
len
IN
BINARY_INTEGER
DEFAULT
NULL
);
PRAGMA
RESTRICT_REFERENCES
(get_line_nchar,
WNDS
,
RNDS
,
WNPS
,
TRUST
);
/*
** PUT - Put (write) text to file
**
** IN
** file - File handle (open in write/append mode)
** buffer - Text to write
** EXCEPTIONS
** invalid_filehandle - not a valid file handle
** invalid_operation - file is not open for writing/appending
** - file is open for byte mode access
** write_error - OS error occured during write operation
** charsetmismatch - if the file is open for nchar data.
*/
--这个就是没有换行符的PUT_LINE,需要自己加NEW_LINE
PROCEDURE
put(file
IN
file_type,
buffer
IN
VARCHAR2
);
PRAGMA
RESTRICT_REFERENCES
(put,
WNDS
,
RNDS
,
TRUST
);
/*
** PUT_NCHAR - Put (write) nchar data to file
** IN
** file - File handle (open in write/append mode)
** buffer - Text to write. the data will convert to UTF8 if needed.
** EXCEPTIONS
** invalid_filehandle - not a valid file handle
** invalid_operation - file is not open for writing/appending
** - file is open for byte mode access
** write_error - OS error occured during write operation
** charsetmismatch - if the file is open for char data.
*/
PROCEDURE
put_nchar(file
IN
file_type,
buffer
IN
NVARCHAR2
);
PRAGMA
RESTRICT_REFERENCES
(put_nchar,
WNDS
,
RNDS
,
TRUST
);
/*
** NEW_LINE - Write line terminators to file
**
** IN
** file - File handle (open in write/append mode)
** lines - Number of newlines to write (default 1)
** EXCEPTIONS
** invalid_filehandle - not a valid file handle
** invalid_operation - file is not open for writing/appending
** - file is open for byte mode access
** write_error - OS error occured during write operation
*/
--表示在这个位置插入一个换行符(也可以指定空的行数)
--这个还是比较实用的
PROCEDURE
new_line(file
IN
file_type,
lines
IN
NATURAL
:=
1
);
PRAGMA
RESTRICT_REFERENCES
(new_line,
WNDS
,
RNDS
,
TRUST
);
/*
** PUT_LINE - Put (write) line to file
**
** IN
** file - File handle (open in write/append mode)
** buffer - Text to write.
** autoflush - Flush following write, default=no flush
** EXCEPTIONS
** invalid_filehandle - not a valid file handle
** invalid_operation - file is not open for writing/appending
** - file is open for byte mode access
** write_error - OS error occured during write operation
** charsetmismatch - if the file is open for nchar data.
*/
PROCEDURE
put_line(file
IN
file_type,
buffer
IN
VARCHAR2
,
autoflush
IN
BOOLEAN
DEFAULT
FALSE
);
PRAGMA
RESTRICT_REFERENCES
(put_line,
WNDS
,
RNDS
,
TRUST
);
/*
** PUT_LINE_NCHAR - Put (write) line of nchar data to file
** IN
** file - File handle (open in write/append mode)
** buffer - Text to write. The data might convert to UTF8 if needed.
** EXCEPTIONS
** invalid_filehandle - not a valid file handle
** invalid_operation - file is not open for writing/appending
** - file is open for byte mode access
** write_error - OS error occured during write operation
** charsetmismatch - if the file is open for char data.
*/
PROCEDURE
put_line_nchar(file
IN
file_type,
buffer
IN
NVARCHAR2
);
PRAGMA
RESTRICT_REFERENCES
(put_line_nchar,
WNDS
,
RNDS
,
TRUST
);
/*
** PUTF - Put (write) formatted text to file
**
** Format string special characters
** '%s' - substitute with next argument
** '\n' - newline (line terminator)
**
** IN
** file - File handle (open in write/append mode)
** format - Formatting string
** arg1 - Substitution argument #1
** ...
** EXCEPTIONS
** invalid_filehandle - not a valid file handle
** invalid_operation - file is not open for writing/appending
** - file is open for byte mode access
** write_error - OS error occured during write operation
** charsetmismatch - if the file is open for nchar data.
*/
/***********************************************************************
这是一个输入格式化内容的过程定义，相当于C语言中的printf，具体的可以看下面这个例子：
Hello, world!
I come from Zork with greetings for all earthlings.
my_world varchar2(4) := 'Zork';
...
PUTF(my_handle, 'Hello, world!\nI come from %s with %s.\n',
my_world,
'greetings for all earthlings');
************************************************************************/
procedure
putf(file
IN
file_type,
format
IN
VARCHAR2
,
arg1
IN
VARCHAR2
DEFAULT
NULL
,
arg2
IN
VARCHAR2
DEFAULT
NULL
,
arg3
IN
VARCHAR2
DEFAULT
NULL
,
arg4
IN
VARCHAR2
DEFAULT
NULL
,
arg5
IN
VARCHAR2
DEFAULT
NULL
);
PRAGMA
RESTRICT_REFERENCES
(putf,
WNDS
,
RNDS
,
TRUST
);
/*
** PUTF_NCHAR - Put (write) formatted text to file
**
** Format string special characters
** N'%s' - substitute with next argument
** N'\n' - newline (line terminator)
**
** IN
** file - File handle (open in write/append mode)
** format - Formatting string
** arg1 - Substitution argument #1
** ...
** EXCEPTIONS
** invalid_filehandle - not a valid file handle
** invalid_operation - file is not open for writing/appending
** - file is open for byte mode access
** write_error - OS error occured during write operation
** charsetmismatch - if the file is open for char data.
*/
procedure
putf_nchar(file
IN
file_type,
format
IN
NVARCHAR2
,
arg1
IN
NVARCHAR2
DEFAULT
NULL
,
arg2
IN
NVARCHAR2
DEFAULT
NULL
,
arg3
IN
NVARCHAR2
DEFAULT
NULL
,
arg4
IN
NVARCHAR2
DEFAULT
NULL
,
arg5
IN
NVARCHAR2
DEFAULT
NULL
);
PRAGMA
RESTRICT_REFERENCES
(putf_nchar,
WNDS
,
RNDS
,
TRUST
);
/*
** FFLUSH - Force physical write of buffered output
**
** IN
** file - File handle (open in write/append mode)
** EXCEPTIONS
** invalid_filehandle - not a valid file handle
** invalid_operation - file is not open for writing/appending
** write_error - OS error occured during write operation
*/
--FFLUSH是用来手工将buffer中的内容写到文件中的
--特别是在文件依然打开的状态下需要读取的时候，可以FFLUSH之后进行read
PROCEDURE
fflush(file
IN
file_type);
PRAGMA
RESTRICT_REFERENCES
(fflush,
WNDS
,
RNDS
,
TRUST
);
/*
** PUT_RAW - Write a raw value to file.
**
** IN file - File handle (open in write/append mode)
** IN buffer - Raw data
** IN autoflush - Flush following write, default=no flush
** EXCEPTIONS
** invalid_filehandle - not a valid file handle
** invalid_operation - file is not open for writing/appending
** write_error - OS error occured during write operation
*/
PROCEDURE
put_raw(file
IN
file_type,
buffer
IN
RAW
,
autoflush
IN
BOOLEAN
DEFAULT
FALSE
);
PRAGMA
RESTRICT_REFERENCES
(put_raw,
WNDS
,
RNDS
,
TRUST
);
/*
** GET_RAW - Read a raw value from file.
**
** The GET_RAW() will read until it sees a line termination character
** or until the number of bytes specified in the LEN parameter has been read.
**
** IN file - File handle (open in write/append mode)
** OUT buffer - Raw data
** IN len - Nubmer of bytes to be read
** EXCEPTIONS
** invalid_filehandle - not a valid file handle
** invalid_operation - file is not open for writing/appending
** read_error - OS error occured during read operation
*/
PROCEDURE
get_raw(file
IN
file_type,
buffer
OUT
NOCOPY
RAW
,
len
IN
BINARY_INTEGER
DEFAULT
NULL
);
PRAGMA
RESTRICT_REFERENCES
(get_raw,
WNDS
,
RNDS
,
TRUST
);
/*
** FSEEK - Move the file pointer to a specified position within the file.
**
** IN file - File handle (open in write/append mode)
** IN absolute_offset - Absolute offset to which to seek.
** IN relative_offset - Relative offset, forward or backwards, to which
** to seek.
**
** The file must be open in read mode in order to use fseek().
**
** If both absolute_offset and relative_offset are not NULL, absolute_offset
** takes precedence. A negative relative_offset will cause fseek to
** close and reopen the file and seek in a forward direction.
**
** EXCEPTIONS
** invalid_filehandle - not a valid file handle
** invalid_offset - file is not open for writing/appending
** invalid_operation - file is opened for byte mode access
*/
PROCEDURE
fseek(file
IN
OUT
file_type,
absolute_offset
IN
BINARY_INTEGER
DEFAULT
NULL
,
relative_offset
IN
BINARY_INTEGER
DEFAULT
NULL
);
PRAGMA
RESTRICT_REFERENCES
(fseek,
WNDS
,
RNDS
,
TRUST
);
/*
** FREMOVE - Delete the specified file from disk.
**
** IN location - directory location of file
** IN filename - file name (including extention)
** EXCEPTIONS
** invalid_path - not a valid file handle
** invalid_filename - file not found or file name NULL
** file_open - file is not open for writing/appending
** access_denied - access to the directory object is denied
** remove_failed - failed to delete file
*/
--删除文件
PROCEDURE
fremove(location
IN
VARCHAR2
,
filename
IN
VARCHAR2
);
PRAGMA
RESTRICT_REFERENCES
(fremove,
WNDS
,
RNDS
,
TRUST
);
/*
** FCOPY - Copy all or part of a file to a new file.
**
** IN location - source directory of file
** IN filename - source file name (including extention)
** IN dest_dir - destination directory of file
** IN dest_file - destination file name (including extention)
** IN start_line - line number from which to begin copying, default is
** 1 referring to the first line in the file
** IN end_line - line number from which to end copying, default is NULL
** referring to end-of-file
** EXCEPTIONS
** invalid_path - not a valid file handle
** invalid_filename - file not found or file name is NULL
** invalid_lineno - bad start_line or end_line value
*/
PROCEDURE
fcopy(src_location
IN
VARCHAR2
,
src_filename
IN
VARCHAR2
,
dest_location
IN
VARCHAR2
,
dest_filename
IN
VARCHAR2
,
start_line
IN
BINARY_INTEGER
DEFAULT
1
,
end_line
IN
BINARY_INTEGER
DEFAULT
NULL
);
PRAGMA
RESTRICT_REFERENCES
(fcopy,
WNDS
,
RNDS
,
TRUST
);
/*
** FGETATTR - Get file attributes
**
** IN location - directory location of file
** IN filename - file name (including extention)
** OUT fexists - true or false, for exists or doesn't exist. Note:
** the following parameters have no meaning if the file
** doesn't exist, in which case, they return NULL.
** OUT file_length - length of the file in bytes.
** OUT block_size - filesystem block size in bytes.
** EXCEPTIONS
** invalid_path - not a valid file handle
** invalid_filename - file not found or file name NULL
** file_open - file is not open for writing/appending
** access_denied - access to the directory object is denied
*/
--这个过程是用于获得文件属性的,具体内容上面说的很清楚了
PROCEDURE
fgetattr(location
IN
VARCHAR2
,
filename
IN
VARCHAR2
,
fexists
OUT
BOOLEAN
,
file_length
OUT
NUMBER
,
block_size
OUT
BINARY_INTEGER
);
PRAGMA
RESTRICT_REFERENCES
(fgetattr,
WNDS
,
RNDS
,
TRUST
);
/*
** FGETPOS - Return the current position in the file in bytes.
**
** IN file - File handle (open in write/append mode)
** EXCEPTIONS
** invalid_filehandle - not a valid file handle
** invalid_operation - file is not open for writing/appending
** invalid_operation - file is open for byte mode access
*/
FUNCTION
fgetpos(file
IN
file_type)
RETURN
BINARY_INTEGER
;
PRAGMA
RESTRICT_REFERENCES
(fgetpos,
WNDS
,
RNDS
,
TRUST
);
/*
** FRENAME - Rename a file to a new name.
**
** IN location - source directory of file
** IN filename - source file name (including extention)
** IN dest_dir - destination directory of file
** IN dest_file - destination file name (including extention)
** IN overwrite - boolean signifying whether to overwrite an existing
** in the event that one exists, default no overwrite
** EXCEPTIONS
** invalid_path - not a valid file handle
** invalid_filename - file not found or file name NULL
** rename_failed - rename of the file failed
** access_denied - access to the directory object is denied
*/
PROCEDURE
frename(src_location
IN
VARCHAR2
,
src_filename
IN
VARCHAR2
,
dest_location
IN
VARCHAR2
,
dest_filename
IN
VARCHAR2
,
overwrite
IN
BOOLEAN
DEFAULT
FALSE
);
PRAGMA
RESTRICT_REFERENCES
(frename,
WNDS
,
RNDS
,
TRUST
);
END
utl_file;