You can use the UTL_FILE package to read and write text files in the operating system from PL/SQL programs. UTL_FILE provides a restricted version of stream I/O for operating system files.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL-compatible mode.
Privilege requirements
You must have the EXECUTE privilege on the UTL_FILE package.
Data structures
The UTL_FILE package defines a RECORD type named FILE_TYPE.
The content of FILE_TYPE is 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. Valid values: CHAR, NCHAR, or 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 of the FOPEN function is invalid. |
| INVALID_FILEHANDLE | The file handle is invalid. |
| INVALID_OPERATION | The file operation is not supported. |
| READ_ERROR | The cache size is too small, or an error occurred during the read operation. |
| WRITE_ERROR | An error occurred during the write operation. |
| INTERNAL_ERROR | A PL/SQL error occurred. |
| FILE_OPEN | An error occurred while opening the file. |
| INVALID_MAXLINESIZE | The max_linesize parameter of the FOPEN function is invalid. Valid value: [1,32767]. |
| INVALID_FILENAME | The file name is invalid. |
| ACCESS_DENIED | You do not have the required permissions to access the file. |
| INVALID_OFFSET | The offset is invalid. Possible causes:
|
Subprograms
The following table describes the subprograms supported by OceanBase Database in the current version.
| 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 | Obtains the current position (offset) in the file, in bytes. |
| FREMOVE | Deletes a file from the disk. This operation requires the appropriate delete privileges. It is similar to the UNIX rm command. |
| FRENAME | Renames an existing file. It is 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-specific 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-specific line terminator. |