The UTL_FILE package enables PL/SQL programs to read and write text files on the operating system. UTL_FILE provides a restricted version of operating system stream file I/O.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support this feature.
Privilege requirements
The user must have the EXECUTE privilege on the UTL_FILE package.
Data structures
The UTL_FILE package defines a RECORD type named FILE_TYPE.
The contents of FILE_TYPE are private to the UTL_FILE package and 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 numeric value of the internal file handle. |
| datatype | The file type: CHAR, NCHAR, or other (binary). |
| byte_mode | Indicates whether the file is opened as a binary file or a text file. |
Exceptions
| Exception | Description |
|---|---|
| INVALID_PATH | The file path 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 operating system-related error occurred during the read. |
| WRITE_ERROR | An operating system-related error occurred during the write. |
| INTERNAL_ERROR | A PL/SQL error defined internally. |
| FILE_OPEN | An error occurred while opening the file. |
| INVALID_MAXLINESIZE | The max_linesize parameter in the FOPEN function is invalid. The supported range is [1,32767]. |
| INVALID_FILENAME | The filename parameter is invalid. |
| ACCESS_DENIED | The file cannot be accessed due to insufficient privileges. |
| INVALID_OFFSET | Possible reasons:
|
Subprograms
The following table lists the UTL_FILE subprograms supported in the current OceanBase Database version and their brief descriptions.
| Subprogram | Description |
|---|---|
| FCLOSE | Closes the file. |
| FFLUSH | Flushes the data in the cache to the file. |
| FOPEN | Opens the file based on the specified information. |
| FGETPOS | Retrieves the current access position (offset) of the file, in bytes. |
| FREMOVE | Deletes the disk file (requires appropriate deletion privileges), similar to the UNIX rm command. |
| FRENAME | Renames the existing file to a new name, similar to the UNIX mv command. |
| FSEEK | Adjusts the file position forward or backward by the specified number of bytes. |
| NEW_LINE | Writes one or more operating system-specified line terminators to the file. |
| GET_LINE | Reads a line of text. |
| PUT | Writes a string to the file. |
| PUT_LINE | Writes a line to the file and appends an operating system-specified line terminator. |