UTL_FILE module
The UTL_FILE module provides a set of routines for reading from and writing to files on the database server's file system.
The schema for this module is SYSIBMADM.
The UTL_FILE module includes the following built-in routines and types.
| Routine name | Description |
|---|---|
| FCLOSE procedure | Closes a specified file. |
| FCLOSE_ALL procedure | Closes all open files. |
| FCOPY procedure | Copies text from one file to another. |
| FFLUSH procedure | Flushes unwritten data to a file |
| FOPEN function | Opens a file. |
| FREMOVE procedure | Removes a file. |
| FRENAME procedure | Renames a file. |
| GET_LINE procedure | Gets a line from a file. |
| IS_OPEN function | Determines whether a specified file is open. |
| NEW_LINE procedure | Writes an end-of-line character sequence to a file. |
| PUT procedure | Writes a string to a file. |
| PUT_LINE procedure | Writes a single line to a file. |
| PUTF procedure | Writes a formatted string to a file. |
| UTL_FILE.FILE_TYPE | Stores a file handle. |
The following is a list of named conditions (these are
called
exceptions| Condition Name | Description |
|---|---|
| access_denied | Access to the file is denied by the operating system. |
| charsetmismatch | A file was opened using FOPEN_NCHAR, but later I/O operations used non-CHAR functions such as PUTF or GET_LINE. |
| delete_failed | Unable to delete file. |
| file_open | File is already open. |
| internal_error | Unhandled internal error in the UTL_FILE module. |
| invalid_filehandle | File handle does not exist. |
| invalid_filename | A file with the specified name does not exist in the path. |
| invalid_maxlinesize | The MAX_LINESIZE value for FOPEN is invalid. It must be between 1 and 32672. |
| invalid_mode | The open_mode argument in FOPEN is invalid. |
| invalid_offset | The ABSOLUTE_OFFSET argument for FSEEK is invalid. It must be greater than 0 and less than the total number of bytes in the file. |
| invalid_operation | File could not be opened or operated on as requested. |
| invalid_path | The specified path does not exist or is not visible to the database |
| read_error | Unable to read the file. |
| rename_failed | Unable to rename the file. |
| write_error | Unable to write to the file. |
Usage notes
To reference directories on
the file system, use a directory alias. You can create a directory
alias by calling the UTL_DIR.CREATE_DIRECTORY or UTL_DIR.CREATE_OR_REPLACE_DIRECTORY
procedures. For example, CALL UTL_DIR.CREATE_DIRECTORY('mydir',
'home/user/temp/mydir')@.
The UTL_FILE module executes file operations by using the Db2® instance ID. Therefore, if you are opening a file, verify that the Db2 instance ID has the appropriate operating system permissions.