UTL FILE PKG
UTL FILE PKG
UTL_FILE
The UTL_FILE package
lets your PL/SQL programs read and write operating system (OS) text
files. It provides a restricted version of standard OS stream file
input/output (I/O).
The file I/O capabilities are similar to those of the standard operating system stream file I/O (OPEN, GET, PUT, CLOSE), with some limitations. For example, call the FOPEN function to return a file handle, which you then use in subsequent calls to GET_LINE or PUT to perform stream I/O to a file. When you are done performing I/O on the file, call FCLOSE to complete any output and to free any resources associated with the file.
Server Security
Server
security for PL/SQL file I/O consists of a restriction on the
directories that can be accessed. Accessible directories must be
specified in the instance parameter initialization file (
INIT
.ORA
).
Specify the accessible directories for the
UTL_FILE
functions in the initialization file using the UTL_FILE_DIR
parameter. For example:UTL_FILE_DIR = <directory name>
Examples (UNIX-Specific)
UTL_FILE_DIR=/appl/gl/log
UTL_FILE_DIR=/appl/gl/out
FILE LOCATION FILENAME
/appl/gl/log L10324.log
/appl/gl/out O10324.out
Types
TYPE file_type IS RECORD (id BINARY_INTEGER);
Summary of Subprograms
FOPEN function
Opens a file for input or output with the default line size.
IS_OPEN function
Determines if a file handle refers to an open file.
FCLOSE procedure
Closes a file.
FCLOSE_ALL procedure
Closes all open file handles.
GET_LINE procedure
Reads a line of text from an open file.
PUT procedure
Writes a line to a file. This does not append a line terminator.
NEW_LINE procedure
Writes one or more OS-specific line terminators to a file.
PUT_LINE procedure
Writes a line to a file. This appends an OS-specific line terminator.
PUTF procedure
A PUT procedure with formatting.
FFLUSH procedure
Physically writes all pending output to a file.
FOPEN function
Opens a file with the maximum line size specified.
FOPEN function
This
function opens a file for input or output. The file location must be an
accessible directory, as defined in the instance's initialization
parameter
UTL_FILE_DIR
. The complete directory path must already exist; it is not created by FOPEN
.
This version of
FOPEN
does
not take a parameter for the maximum line size. Thus, the default
(which is 1023 on most systems) is used. To specify a different maximum
line size, use the other, overloaded version of "FOPEN function".UTL_FILE.FOPEN (
location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2)
RETURN UTL_FILE.FILE_TYPE;
IS_OPEN function
This function tests a file handle to see if it identifies an open file.
IS_OPEN
reports
only whether a file handle represents a file that has been opened, but
not yet closed. It does not guarantee that there will be no operating
system errors when you attempt to use the file handle.UTL_FILE.IS_OPEN (
file IN FILE_TYPE)
RETURN BOOLEAN;
FCLOSE procedure
This procedure closes an open file identified by a file handle. If there is buffered data yet to be written when
FCLOSE
runs, then you may receive a WRITE_ERROR
exception when closing a file.UTL_FILE.FCLOSE (
file IN OUT FILE_TYPE);
FCLOSE_ALL procedure
This
procedure closes all open file handles for the session. This should be
used as an emergency cleanup procedure, for example, when a PL/SQL
program exits on an exception.
Syntax
UTL_FILE.FCLOSE_ALL;
GET_LINE procedure
This
procedure reads a line of text from the open file identified by the
file handle and places the text in the output buffer parameter. Text is
read up to but not including the line terminator, or up to the end of
the file.
If the line does not fit in the buffer, then a
VALUE_ERROR
exception is raised. If no text was read due to "end of file," then theNO_DATA_FOUND
exception is raised.
Because the line terminator character is not read into the buffer, reading blank lines returns empty strings.
The maximum size of an input record is 1023 bytes, unless you specify a larger size in the overloaded version of
FOPEN
.UTL_FILE.GET_LINE (
file IN FILE_TYPE,
buffer OUT VARCHAR2);
PUT
writes
the text string stored in the buffer parameter to the open file
identified by the file handle. The file must be open for write
operations. No line terminator is appended by PUT
; use NEW_LINE
to terminate the line or use PUT_LINE
to write a complete line with a line terminator.
The maximum size of an input record is 1023 bytes, unless you specify a larger size in the overloaded version of
FOPEN
.UTL_FILE.PUT (
file IN FILE_TYPE,
buffer IN VARCHAR2);
NEW_LINE procedure
This
procedure writes one or more line terminators to the file identified by
the input file handle. This procedure is separate from
PUT
because the line terminator is a platform-specific character or sequence of characters.UTL_FILE.NEW_LINE (
file IN FILE_TYPE,
lines IN NATURAL := 1);
PUT_LINE procedure
This
procedure writes the text string stored in the buffer parameter to the
open file identified by the file handle. The file must be open for write
operations.
PUT_LINE
terminates the line with the platform-specific line terminator character or characters.
The maximum size for an output record is 1023 bytes, unless you specify a larger value using the overloaded version of
FOPEN
.UTL_FILE.PUT_LINE (
file IN FILE_TYPE,
buffer IN VARCHAR2);
PUTF procedure
This procedure is a formatted
PUT
procedure. It works like a limited printf
(). The format string can contain any text, but the character sequences '%s' and '\n' have special meaning.
%s - Substitute this sequence with the string value of the next argument in the argument list.
\n --Substitute with the appropriate platform-specific line terminator
Syntax
UTL_FILE.PUTF (
file IN FILE_TYPE,
format IN VARCHAR2,
[arg1 IN VARCHAR2 DEFAULT NULL,
. . .
arg5 IN VARCHAR2 DEFAULT NULL]);
FFLUSH procedure
FFLUSH
physically
writes all pending data to the file identified by the file handle.
Normally, data being written to a file is buffered. TheFFLUSH
procedure forces any buffered data to be written to the file.
Flushing
is useful when the file must be read while still open. For example,
debugging messages can be flushed to the file so that they can be read
immediately.
UTL_FILE.FFLUSH (
file IN FILE_TYPE);
invalid_maxlinesize EXCEPTION;
Exceptions
File location or filename was invalid.
The open_mode parameter in FOPEN was invalid.
File handle was invalid.
File could not be opened or operated on as requested.
Operating system error occurred during the read operation.
Operating system error occurred during the write operation.
No comments:
Post a Comment