7.17 UTL_FILE

Table of Contents Previous Next


7 Built-In Packages : 7.17 UTL_FILE

The UTL_FILE package provides the capability to read from, and write to files on the operating system’s file system. Non-superusers must be granted EXECUTE privilege on the UTL_FILE package by a superuser before using any of the functions or procedures in the package. For example the following command grants the privilege to user mary:
Also, the operating system username, enterprisedb, must have the appropriate read and/or write permissions on the directories and files to be accessed using the UTL_FILE functions and procedures. If the required file permissions are not in place, an exception is thrown in the UTL_FILE function or procedure.
A handle to the file to be written to, or read from is used to reference the file. The file handle is defined by a public variable in the UTL_FILE package named, UTL_FILE.FILE_TYPE. A variable of type FILE_TYPE must be declared to receive the file handle returned by calling the FOPEN function. The file handle is then used for all subsequent operations on the file.
References to directories on the file system are done using the directory name or alias that is assigned to the directory using the CREATE DIRECTORY command. The procedures and functions available in the UTL_FILE package are listed in the following table.
FCLOSE(file IN OUT)
FCOPY(location, filename, dest_dir, dest_file [, start_line [, end_line ] ])
Copies filename in the directory identified by location to file, dest_file, in directory, dest_dir, starting from line, start_line, to line, end_line.
FFLUSH(file)
FOPEN(location, filename, open_mode [, max_linesize ])
Opens file, filename, in the directory identified by location.
FREMOVE(location, filename)
FRENAME(location, filename, dest_dir, dest_file [, overwrite ])
GET_LINE(file, buffer OUT)
Reads a line of text into variable, buffer, from the file identified by file.
NEW_LINE(file [, lines ])
PUT(file, buffer)
Writes buffer to the given file. PUT does not write an end-of-line character sequence.
PUT_LINE(file, buffer)
Writes buffer to the given file. An end-of-line character sequence is added by the PUT_LINE procedure.
PUTF(file, format [, arg1 ] [, ...])
Writes a formatted string to the given file. Up to five substitution parameters, arg1,...arg5 may be specified for replacement in format.
Advanced Server's implementation of UTL_FILE is a partial implementation when compared to Oracle's version. Only those functions and procedures listed in the table above are supported.
If a call to a UTL_FILE procedure or function raises an exception, you can use the condition name to catch the exception. For more information about error handling, see Section 4.5.7, Exception Handling.
The UTL_FILE package reports the following exception codes compatible with Oracle databases:
When a UTL_FILE function or procedure creates a file, there are default file permissions as shown by the following.
Note that all permissions are denied on users belonging to the enterprisedb group as well as all other users. Only the enterprisedb user has read and write permissions on the created file.
If you wish to have a different set of file permissions on files created by the UTL_FILE functions and procedures, you can accomplish this by setting the utl_file.umask configuration parameter.
The utl_file.umask parameter sets the file mode creation mask or simply, the mask, in a manner similar to the Linux umask command. This is for usage only within the Advanced Server UTL_FILE package.
Note: The utl_file.umask parameter is not supported on Windows systems.
The value specified for utl_file.umask is a 3 or 4-character octal string that would be valid for the Linux umask command. The setting determines the permissions on files created by the UTL_FILE functions and procedures. (Refer to any information source regarding Linux or Unix systems for information on file permissions and the usage of the umask command.)
First, set up the directory in the file system to be used by the UTL_FILE package. Be sure the operating system account, enterprisedb or postgres, whichever is applicable, can read and write in the directory.
The CREATE DIRECTORY command is issued in psql to create the directory database object using the file system directory created in the preceding step.
Set the utl_file.umask configuration parameter. The following setting allows the file owner any permission. Group users and other users are permitted any permission except for the execute permission.
In the same session during which the utl_file.umask parameter is set to the desired value, run the UTL_FILE functions and procedures.
This parameter can also be set on a per role basis with the ALTER ROLE command, on a per database basis with the ALTER DATABASE command, or for the entire database server instance by setting it in the postgresql.conf file.
7.17.2 FCLOSE
The FCLOSE procedure closes an open file.
FCLOSE(file IN OUT FILE_TYPE)
Variable of type FILE_TYPE containing a file handle of the file to be closed.
7.17.3 FCLOSE_ALL
The FLCLOSE_ALL procedures closes all open files. The procedure executes successfully even if there are no open files to close.
7.17.4 FCOPY
The FCOPY procedure copies text from one file to another.
FCOPY(location VARCHAR2, filename VARCHAR2,
dest_dir VARCHAR2, dest_file VARCHAR2
[, start_line PLS_INTEGER [, end_line PLS_INTEGER ] ])
Directory name, as stored in pg_catalog.edb_dir.dirname, of the directory containing the file to be copied.
Directory name, as stored in pg_catalog.edb_dir.dirname, of the directory to which the file is to be copied.
The following makes a copy of a file, C:\TEMP\EMPDIR\empfile.csv, containing a comma-delimited list of employees from the emp table. The copy, empcopy.csv, is then listed.
7.17.5 FFLUSH
The FFLUSH procedure flushes unwritten data from the write buffer to the file.
FFLUSH(file FILE_TYPE)
Variable of type FILE_TYPE containing a file handle.
Each line is flushed after the NEW_LINE procedure is called.
7.17.6 FOPEN
The FOPEN function opens a file for I/O.
filetype FILE_TYPE FOPEN(location VARCHAR2,
filename VARCHAR2,open_mode VARCHAR2
[, max_linesize BINARY_INTEGER ])
Directory name, as stored in pg_catalog.edb_dir.dirname, of the directory containing the file to be opened.
Mode in which the file will be opened. Modes are: a - append to file; r - read from file; w - write to file.
Maximum size of a line in characters. In read mode, an exception is thrown if an attempt is made to read a line exceeding max_linesize. In write and append modes, an exception is thrown if an attempt is made to write a line exceeding max_linesize. The end-of-line character(s) are not included in determining if the maximum line size is exceeded. This behavior is not compatible with Oracle databases; Oracle does count the end-of-line character(s).
Variable of type FILE_TYPE containing the file handle of the opened file.
7.17.7 FREMOVE
The FREMOVE procedure removes a file from the system.
FREMOVE(location VARCHAR2, filename VARCHAR2)
Directory name, as stored in pg_catalog.edb_dir.dirname, of the directory containing the file to be removed.
7.17.8 FRENAME
The FRENAME procedure renames a given file. This effectively moves a file from one location to another.
FRENAME(location VARCHAR2, filename VARCHAR2,
dest_dir VARCHAR2, dest_file VARCHAR2,
[ overwrite BOOLEAN ])
Directory name, as stored in pg_catalog.edb_dir.dirname, of the directory containing the file to be renamed.
Directory name, as stored in pg_catalog.edb_dir.dirname, of the directory to which the renamed file is to exist.
Replaces any existing file named dest_file in dest_dir if set to TRUE, otherwise an exception is thrown if set to FALSE. This is the default.
The following renames a file, C:\TEMP\EMPDIR\empfile.csv, containing a comma-delimited list of employees from the emp table. The renamed file, C:\TEMP\NEWDIR\newemp.csv, is then listed.
7.17.9 GET_LINE
The GET_LINE procedure reads a line of text from a given file up to, but not including the end-of-line terminator. A NO_DATA_FOUND exception is thrown when there are no more lines to read.
GET_LINE(file FILE_TYPE, buffer OUT VARCHAR2)
Variable of type FILE_TYPE containing the file handle of the opened file.
7.17.10 IS_OPEN
The IS_OPEN function determines whether or not the given file is open.
status BOOLEAN IS_OPEN(file FILE_TYPE)
Variable of type FILE_TYPE containing the file handle of the file to be tested.
TRUE if the given file is open, FALSE otherwise.
7.17.11 NEW_LINE
The NEW_LINE procedure writes an end-of-line character sequence in the file.
NEW_LINE(file FILE_TYPE [, lines INTEGER ])
Variable of type FILE_TYPE containing the file handle of the file to which end-of-line character sequences are to be written.
7.17.12 PUT
The PUT procedure writes a string to the given file. No end-of-line character sequence is written at the end of the string. Use the NEW_LINE procedure to add an end-of-line character sequence.
PUT(file FILE_TYPE, buffer { DATE | NUMBER | TIMESTAMP |
Variable of type FILE_TYPE containing the file handle of the file to which the given string is to be written.
The following example uses the PUT procedure to create a comma-delimited file of employees from the emp table.
The following is the contents of empfile.csv created above:
7.17.13 PUT_LINE
The PUT_LINE procedure writes a single line to the given file including an end-of-line character sequence.
PUT_LINE(file FILE_TYPE,
buffer {DATE|NUMBER|TIMESTAMP|VARCHAR2})
Variable of type FILE_TYPE containing the file handle of the file to which the given line is to be written.
The following example uses the PUT_LINE procedure to create a comma-delimited file of employees from the emp table.
The following is the contents of empfile.csv created above:
7.17.14 PUTF
The PUTF procedure writes a formatted string to the given file.
PUTF(file FILE_TYPE, format VARCHAR2 [, arg1 VARCHAR2]
Variable of type FILE_TYPE containing the file handle of the file to which the formatted line is to be written.
String to format the text written to the file. The special character sequence, %s, is substituted by the value of arg. The special character sequence, \n, indicates a new line. Note, however, in Advanced Server, a new line character must be specified with two consecutive backslashes instead of one - \\n. This characteristic is not compatible with Oracle databases.
Up to five arguments, arg1,...arg5, to be substituted in the format string for each occurrence of %s. The first arg is substituted for the first occurrence of %s, the second arg is substituted for the second occurrence of %s, etc.
The following anonymous block produces formatted output containing data from the emp table. Note the use of the E literal syntax and double backslashes for the new line character sequence in the format string which are not compatible with Oracle databases.
The following is the contents of empfile.csv created above:

7 Built-In Packages : 7.17 UTL_FILE

Table of Contents Previous Next