| PL/SQL Packages and Types Reference 10g Release 1 (10.1) Part Number B10802-01 |
|
|
< a href="../b10802.pdf">View PDF |
| PL/SQL Packages and Types Reference 10g Release 1 (10.1) Part Number B10802-01 |
|
|
< a href="../b10802.pdf">View PDF |
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.
This chapter contains the following topics:
UTL_FILE is available for both cli
ent-side and server-side PL/SQL. Both the client (text I/O) and server implementations are subject to server-side file system permiss
ion checking.
In the past, accessible directories for the UTL_FILE functions we
re specified in the initialization file using the UTL_FILE_DIR parameter. However, UTL_FILE_DIR access is n
ot recommended. It is recommended that you use the CREATE DIRECTORY feature, which replaces UTL_FILE_DIR. D
irectory objects offer more flexibility and granular control to the UTL_FILE application administrator, can be maintaine
d dynamically (that is, without shutting down the database), and are consistent with other Oracle tools. CREATE DIRECTORY privilege is granted only to SYS and SYSTEM by default.
Use the CREATE
DIRECTORY feature instead of UTL_FILE_DIR for directory access verification.
On UNIX systems, the owner of a file created by the FOPEN function is the owner of the
shadow process running the instance. Normally, this owner is ORACLE. Files created using FOPEN are always
writable and readable using the UTL_FILE subprograms, but non privileged users who need to read these files outside of P
L/SQL may need access from a system administrator.
The contents of FILE_TYPE are private to the
T YPE file_type IS RECORD ( id BINARY_INTEGER, datatype BINARY_INTEGER);
The file location and file name parameters are supplied to the FOPEN function as separate strings, so that the file location can be checked against the list of accessible directories as specified
by the ALL_DIRECTORIES view of accessible directory objects. Together, the file location and name must represent a legal
filename on the system, and the directory must be accessible. A subdirectory of an accessible directory is not necessarily also acce
ssible; it too must be specified using a complete path name matching an ALL_DIRECTORIES object.
UTL_FILE implicitly interprets line terminators on read requests, thereby affecting the number of byte
s returned on a GET_LINE call. For example, the len parameter of UTL_FILE.GET_LINE specifies t
he requested number of bytes of character data. The number of bytes actually returned to the user will be the lesser of:
The FOPEN max_linesize parameter must be a number in the range 1 and 32767. If unspecified
, Oracle supplies a default value of 1024. The GET_LINE len parameter must be a number in the range 1 and 3
2767. If unspecified, Oracle supplies the default value of max_linesize. If max_linesize and len are defined to be different values, then the lesser value takes precedence.
UTL_FIL
E.GET_RAW ignores line terminators and returns the actual number of bytes requested by the GET_RAW len parameter.
When data encoded in one character set is read and Globalization Support is t
old (such as by means of NLS_LANG) that it is encoded in another character set, the result is indeterminate. If NL
S_LANG is set, it should be the same as the database character set.
Operating system-specific parameters, such as C-shell environment variables under UNIX, cannot be used in the file location or file n ame parameters.
UTL_FILE I/O capabilities are similar to standard operating sy
stem stream file I/O (OPEN, GET, PUT, CLOSE) capabilities, but with some limitati
ons. For example, you call the FOPEN function to return a file handle, which you use in subsequent calls to GET_LI
NE or PUT to perform stream I/O to a file. When file I/O is done, you call FCLOSE to complete any ou
tput and free resources associated with the file.
Procedures in UTL_FILE can also raise predefined PL/SQL exceptions such as NO_DATA_FOUND or VALUE_ERR
OR.
Given the following:
SQL> CREATE DIRECTORY log_dir AS '/appl/gl/log'; SQL> GRANT REA D ON DIRECTORY log_dir TO DBA; SQL> CREATE DIRECTORY out_dir AS '/appl/gl/user''; < a name="1004455">SQL> GRANT READ ON DIRECTORY user_dir TO PUBLIC;
The following file locations and filenames are valid and accessible as follows:
| File Location | Filename | Accessible By |
|---|---|---|
|
|
|
Users with DBA privilege |
|
|
|
All users |
The following file locations and filenames are invalid:
| |
Filename | Invalid Because |
|---|---|---|
|
|
|
# subdirectories are not accessible |
|
|
|
# directory strings must follow case sensitivity rules as required by the O/S |
|
|
|
# filenames may not include portions of directory paths |
|
|
|
# no corresponding |
DECLARE V1 VARCHAR2(32767 ); F1 UTL_FILE.FILE_TYPE; BEGIN -- In this example MAX_LINES IZE is less than GET_LINE's length request -- so the number of bytes returned will be 256 or less if a line terminator is seen. F1 := UTL_FILE.FOPEN('MYDIR','MYFILE','R',256); UTL_FILE.GET_L INE(F1,V1,32767); UTL_FILE.FCLOSE(F1); -- In this example, F OPEN's MAX_LINESIZE is NULL and defaults to 1024, -- so the number of bytes returned will be 1024 or less if a line terminator is seen. F1 := UTL_FILE.FOPEN('MYDIR','MYFILE','R'); UTL_FILE.G ET_LINE(F1,V1,32767); UTL_FILE.FCLOSE(F1); -- In this exampl e, GET_LINE doesn't specify a number of bytes, so it defaults to -- the same value as FOPEN's MAX_LINESIZE which is NULL in this case and defaults to 1024. -- So the number of bytes returned will be 1024 or less if a line terminator is seen. F1 := UTL_FILE.FOPEN('MYDIR','MYFILE','R'); UTL_FILE.G ET_LINE(F1,V1); UTL_FILE.FCLOSE(F1); END;
| Subprogram | Description |
|---|---|
|
Closes a file | |
|
Closes all open file handles | |
| < td class="Formal"> |
Physically writ es all pending output to a file |
|
Reads and returns the attributes of a disk file | |
|
Returns the current relative offset position within a file, in bytes | |
|
Opens a file for input or output | |
|
Opens a file in Unicode for input or output | |
|
Deletes a disk file, assuming that yo u have sufficient privileges | |
| < p class="TB">FRENAME Procedure |
Renames an existing file to a new name, similar to the UNIX |
|
Adjusts the file po inter forward or backward within the file by the number of bytes specified | |
|
Reads text from an open file | |
|
Reads text in Unicode f rom an open file | |
|
Reads a | |
|
Determi nes if a file handle refers to an open file | |
|
Writes one or more operating system-specific line terminators to a file |
Writes a string to a f ile |
|
A | |
|
Writes a Unicode string to a file | |
|
Accepts as input a | |
|
Writes a line to a file, and so appends an operating system-specific line terminator | |
|
Writes a Unico de line to a file | |
|
A |
This procedure closes an open file identified by a file h andle.
UTL_FILE.FCLOSE ( file IN OUT FILE_TYPE);
| Parameter | Description |
|---|---|
|
|
Active file handle returned by an |
If there is buffered data yet to be written when FCLOSE runs
, then you may receive a WRITE_ERROR exception when closing a file.
WRITE_ERROR INVALID_FILEHANDLE
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 except ion.
UTL_FILE.FCLOSE_ALL;
WRITE_ERROR
This procedure copies a contiguous portion of a file to a newly created file. By default, the wh
ole file is copied if the start_line and end_line parameters are omitted. The source file is opened in read
mode. The destination file is opened in write mode. A starting and ending line number can optionally be specified to select a portio
n from the center of the source file for copying.
UTL_FILE.FCOPY ( location IN V ARCHAR2, filename IN VARCHAR2, dest_dir IN VARCHAR2, d est_file IN VARCHAR2, start_line IN PLS_INTEGER DEFAULT 1, end_line IN PLS_INTEG ER DEFAULT NULL);
FFLUSH physically writes pend
ing data to the file identified by the file handle. Normally, data being written to a file is buffered. The FFLUSH proce
dure forces the buffered data to be written to the file. The data must be terminated with a newline character.
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_TY PE); invalid_maxlinesize EXCEPTION;
| |
Description |
|---|---|
|
|
Active file handle returned by an |
INVALID_FILEHANDLE INVALID_OPERATION WRITE_ERROR
This procedure reads and returns the attributes of a disk file.
U TL_FILE.FGETATTR( location IN VARCHAR2, filename IN VARCHAR2, exists OUT BOOLEAN, file_length OUT NUMBER, blocksize OUT NUMB ER);
This function returns the current relative offset position within a file, in by tes.
UTL_FILE.FGETPOS ( fileid IN file_type) RETURN PLS_INT EGER;
| Parameters | Description< /th> |
|---|---|
|
|
The directory location of the source file | <
/tr>
FGETPOS returns the relative offset position for an open file, in byte
s. It raises an exception if the file is not open. It returns 0 for the beginning of the file.
This function opens a file. You can specify the maximum line size and have a maximum of 50 files open simultaneously. See also "FOPEN_NCHAR Function".
UTL_FILE.FOPEN ( location IN VARCHAR2, filename IN VARCHAR2, open_mode IN VARCHAR2, max_linesize IN BINARY_INTEGER) RETURN f ile_type;
| Parameter | Description |
|---|---|
|
|
Directory location of file. This string
is a directory object name and is case sensitive. The default is uppercase. Read privileges must be granted on this directory object
for the |
|
|
File name, including extension (file type), without directory path. If a directory path is given as a part of the filename, it is i
gnored by |
|
Specifies how the file is opened. Modes include: If you try to open a file specifying ' | |
|
|
Maximum number of characters for each line, including the newline character, for t his file. (minimum value 1, maximum value 32767). If unspecified, Oracle supplies a default value of 1024. |
| Return | Description |
|---|---|
|
|
Handle to open file. |
The file location an d file name parameters must be supplied to the FOPEN function as quoted strings so that the file location can be checked against the list of accessible directories as specified by the ALL_DIRECTORIES view of accessible directory objects.
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.
This function opens a file in Unicode for input or output, with the maximum line size specified. You can have a maximum of 50 files open simultaneously. With this function, yo u can read or write a text file in Unicode instead of in the database charset. See also FOPEN Function.
UTL_FILE.FOPEN_NCHAR ( location IN VARCHAR2, filename IN VARCHAR2, open_mode IN VARCHAR2, max_line size IN BINARY_INTEGER) RETURN file_type;
This procedure deletes a disk file, assuming that you have sufficient privileges.
UTL_FILE.FREMOVE ( location IN VARCHAR2, filename IN VARCHAR2);
| Parameters | Description |
|---|---|
|
|
The directory location of the file, a |
|
|
The name of the file to be d eleted |
The FREMOVE procedure does not verify privileges before
deleting a file. The O/S verifies file and directory permissions. An exception is returned on failure.
This procedure renames an existing file to a new name, similar to the UNIX mv function.
UTL_FILE.FRENAME ( location IN VARCHAR2, filename IN VARCH AR2, dest_dir IN VARCHAR2, dest_file IN VARCHAR2, overwr ite IN BOOLEAN DEFAULT FALSE);
Permission on both the source and destination directories must be granted. You can use the overwrite parameter to specify whether or not to overwrite a file if one exists in the destination directory. The default is
FALSE for no overwrite.
This procedure adjusts the file pointer forward or backward within the file by the number of bytes specified.
UTL_FILE.FSEEK ( < /a> fid IN utl_file.file_type, absolute_offset IN PL_INTEGER DEFAULT NULL, < /a> relative_offset IN PLS_INTEGER DEFAULT NULL);
Using FSEEK, you can read previous lines in the file without first clos
ing and reopening the file. You must know the number of bytes by which you want to navigate.
If relative_offset, the procedure seeks forward. If relative_offset > 0, or backward, if relativ
e_offset < 0, the procedure seeks through the file by the number of relative_offset bytes specified.
If the beginning of the file is reached before the number of bytes specified, then the file pointer
is placed at the beginning of the file. If the end of the file is reached before the number of bytes specified, then an INVALID
_OFFSET error is raised.
If absolute_offset, the procedure seeks to an
absolute location specified in bytes.
This procedure reads 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, th
e line terminator, or up to the end of the file, or up to the end of the len parameter. It cannot exceed the max_l
inesize specified in FOPEN.
UTL_FILE.GET_LINE ( file IN FILE_TYPE, buffer OUT VARCHAR2, len IN PLS_INTEGER DEFAULT NULL); < /pre>Parameters
Table 155-14 GET_LINE Pro cedure Parameters
If the line does not fit in the buffer, a VALUE_ERROR exception is raised. If no text was read du
e to end of file, the NO_DATA_FOUND exception is raised. If the file is opened for byte mode operations, the INVAL
ID_OPERATION exception is raised.
Because the line terminator character is not read into the buffer, reading blank lines returns empty strings.
The maximum size of the b
uffer parameter is 32767 bytes unless you specify a smaller size in FOPEN. If unspecified, Oracle supplies a defa
ult value of 1024. See also "GET_LINE_NCHAR Procedure".
INVALID_FILEHANDLE INVALID_OPERATION READ_ERROR NO_DATA_FOUND VALUE_ERROR
This procedure reads text from the open file identified by the file handle and places the text in the output buffer parameter. With this function, y ou can read a text file in Unicode instead of in the database charset. See also "GET_LINE_NCHAR Procedur e".
UTL_FILE.GET_LINE_NCHAR ( file IN FILE_TYPE, buffer OUT NVARCHAR2, len IN PLS_INTEGER DEFAULT NULL); < /pre>Parameters
Table 155-15 GET_LINE_NCH AR Procedure Parameters
This function reads a RAW strin
g value from a file and adjusts the file pointer ahead by the number of bytes read. UTL_FILE.GET_RAW ignores line termin
ators and returns the actual number of bytes requested by the GET_RAW len parameter.
UTL_FILE.GET_RAW ( fid IN utl_file.file_type, r OUT NOCOPY RAW, len IN PLS_INTEGER DEFAULT NULL);
| Description | |
|---|---|
|
|
The file ID. |
|
|
The |
|
|
The number of bytes read from the file. Default is |
This function tests a file handle to see if it identifies an open file. IS_OPEN reports only wh
ether 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 F ILE_TYPE) RETURN BOOLEAN;
| Parameter | Description |
|---|---|
|
|
Active f
ile handle returned by an |
TRUE or FALSE
This procedure writes one or m
ore line terminators to the file identified by the input file handle. This procedure is separate from PUT because the li
ne terminator is a platform-specific character or sequence of characters.
UTL_FILE.NEW_LINE ( file IN FILE_TYPE, lines IN NATURAL := 1);
file
Active file handle returned by an FOPEN or FOPEN_NCHAR call.
lines
Number of line terminators to be written to the file.
INVALID_FILEHANDLE INVALID_OPERATION WRITE_ERROR
PUT writes the text string stored in the buffer parameter to th
e 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. S
ee also "PUT_NCHAR Procedure".
UTL_FILE.PUT ( file IN FILE_TYPE, buffer IN VARCHAR2);
The maximum size of the FOPEN. If unspecified, Oracle supplies a
default value of 1024. The sum of all sequential PUT calls cannot exceed 32767 without intermediate buffer flushes.
INVALID_FILEHANDLE INVALID_OPERATION WRITE_ERROR
This procedure is a formatted PUT procedure. It works like a limited pr
intf(). See also "PUTF_NCHAR Procedure".
UTL_FILE.PUTF ( file IN FILE_TYPE, format IN VARCHAR2, < /a> [arg1 IN VARCHAR2 DEFAULT NULL, . . . arg5 IN VARCHAR2 DEFAULT NULL] );
The format string can contain any text, but the character sequence
s %s and \n have special meaning.
| Chara cter Sequence | Meaning | |||||||||
|---|---|---|---|---|---|---|---|---|---|---|
|
|
Substitute this sequence with the string value of the next argument in the argument list. | |||||||||
|
|
Substitute with the appropriate platform-specific line terminator. |
| Parameters | Description |
|---|---|
|
|
The file ID. |
|
|
The |
|
|
If |
You can request an automatic flush of the buffer b
y setting the third argument to TRUE.
The maximum size of the buffer parameter is 32767 bytes unless you specify a smaller size in FOPEN. If unspecified, Oracle supplies a default value
of 1024. The sum of all sequential PUT calls cannot exceed 32767 without intermediate buffer flushes.
This procedure writes the text string stored in the buffer parameter to the open file identi
fied by the file handle. The file must be open for write operations. PUT_LINE terminates the line with the platform-spec
ific line terminator character or characters.
See also "PUT_LINE _NCHAR Procedure".
UTL_FILE.PUT_LINE ( file IN FILE_TYPE, buffer IN VARCHAR2, autoflush IN BOOLEAN DEFAULT FALSE);
The maximum size of the buffer parameter is 32767 bytes unless you specify a
smaller size in FOPEN. If unspecified, Oracle supplies a default value of 1024. The sum of all sequential PUT calls cannot exceed 32767 without intermediate buffer flushes.
INVALID_FILEHANDLE INVALID_OPERATION WRITE_ERROR
This procedu re writes the text string stored in the buffer parameter to the open file identified by the file handle. With this function, you can write a text file in Unicode instead of in the database charset. See also "PUT_LINE Procedure".
UTL_FILE.PUT_LINE_NCHAR ( file IN FILE_TYPE, buffer IN NVARCHAR2);
| Parameters | Description |
|---|---|
|
|
Active file
handle returned by an |
|
|
Text buffer that contains the lines to be written to the file. |
The maximum size of the buffer parameter is 32767 bytes unless you specify a smaller size in FOPEN. If unspecified, Oracle supplies a default value of 1024. The sum of all sequential PUT calls cannot exceed 32767 wit
hout intermediate buffer flushes.
This procedure is a formatted PUT
_NCHAR procedure. Using PUTF_NCHAR, you can write a text file in Unicode instead of in the database charset. See
also "PUTF_NCHAR Procedure" and "PUT_LINE
Procedure".
UTL_FILE.PUTF_NCHAR ( file IN FIL E_TYPE, format IN NVARCHAR2, [arg1 IN NVARCHAR2 DEFAULT NULL, . . . arg5 IN NVARCHAR2 DEFAULT NULL]);
The maximum size of the buffer parameter is 32767 bytes unless you specify a smaller size in FOPEN. If unspecified, Oracle supplies a default value of 1024. The sum of all sequential PUT calls cannot excee
d 32767 without intermediate buffer flushes.
|
 Copyright © 1998, 2003 Oracle Corporation All Rights Reserved. |
|
