The UTL_FILE system package allows PL programs to read and write OS text files. UTL_FILE provides a restricted version of the OS stream file I/O.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
Privileges
You must have the EXECUTE privilege on the UTL_FILE system package.
Data structure
The UTL_FILE system package defines a RECORD type, namely FILE_TYPE.
The content of FILE_TYPE is private to the UTL_FILE system package and therefore cannot be referenced or modified.
Syntax
TYPE file_type IS RECORD (
id BINARY_INTEGER,
datatype BINARY_INTEGER,
byte_mode BOOLEAN);
Parameters
| Parameter | Description |
|---|---|
| id | The ID of the internal file handle. |
| datatype | The data type of the file, such as CHAR, Nchar, or another binary type. |
| byte_mode | Specifies whether to open the file in the binary or text format. |
Exceptions
| Exception | Description |
|---|---|
| INVALID_PATH | The file location is invalid. |
| INVALID_MODE | The open_mode parameter in the FOPEN function is invalid. |
| INVALID_FILEHANDLE | The file handle is invalid. |
| INVALID_OPERATION | The file operation is not supported. |
| READ_ERROR | The read buffer is too small, or an OS error occurred during the reading process. |
| WRITE_ERROR | An OS error occurred during the write process. |
| INTERNAL_ERROR | An internal PL error occurred. |
| FILE_OPEN | An error occurred because the file is open. |
| INVALID_MAXLINESIZE | The max_linesize parameter in the FOPEN function is invalid. Value range: [1,32767]. |
| INVALID_FILENAME | The file name is invalid. |
| ACCESS_DENIED | Access is denied due to insufficient privileges. |
| INVALID_OFFSET | The possible causes are as follows:
|
Subprograms
The following table describes the UTL_FILE subprograms supported by the current OceanBase Database version.
| Subprogram | Description |
|---|---|
| FCLOSE | Closes a file. |
| FFLUSH | Flushes data in the buffer to a file. |
| FOPEN | Opens a file based on user-specified information. |
| FGETPOS | Obtains the currently accessed position (offset in bytes) in a file. |
| FREMOVE | Deletes a file from the disk. (You must have the required delete privilege.) It is similar to the rm command in UNIX. |
| FRENAME | Renames an existing file. It is similar to the mv command in UNIX. |
| FSEEK | Adjusts the accessed position forward or backward in a file by the specified number of bytes. |
| NEW_LINE | Writes one or more OS-specified line terminators to a file. |
| GET_LINE | Reads a line of text. |
| PUT | Writes a string to a file. |
| PUT_LINE | Writes a line to a file and appends an OS-specified line terminator. |