Using the UTL_FILE system package, PL programs can read from and write to text files on the operating system. UTL_FILE provides a limited version of operating system stream file I/O.
Applicability
This content applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL-compatible mode.
UTL_FILE Privilege Description
You must have the EXECUTE privilege on the UTL_FILE system package.
UTL_FILE data structure
The UTL_FILE system package defines a RECORD type, which is a FILE_TYPE.
The contents of FILE_TYPE are 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 value of the internal file handle number. |
| datatype | The file isCHARFiles,NcharFile or other (binary). |
| byte_mode | Whether the file is opened as a binary file or a text file. |
UTL_FILE Exceptions
| Exception name | Meaning |
| INVALID_PATH | The file location is invalid. |
| INVALID_MODE | FOPENIn the function'sopen_modeParameter is invalid. |
| INVALID_FILEHANDLE | File handle is invalid. |
| INVALID_OPERATION | Unsupported file operation. |
| READ_ERROR | The read cache is too small, or an operating system-related error occurred during the read. |
| WRITE_ERROR | An operating system-related error occurred during the write operation. |
| INTERNAL_ERROR | Internally defined PL errors. |
| FILE_OPEN | An error occurred when opening the file. |
| INVALID_MAXLINESIZE | FOPENFunction parametersmax_linesizeInvalid. The supported range is [1,32767]. |
| INVALID_FILENAME | Invalid parameter file name. |
| ACCESS_DENIED | Unable to access the file due to insufficient permissions. |
| INVALID_OFFSET | Possible causes:
|
The following table describes the UTL_FILE subprograms supported by the current version of OceanBase Database and provides brief descriptions.
| subprogram | Description |
| FCLOSE | Close the file. | | FFLUSH | Refreshes the data in the cache to a file. | | FOPEN | Open a file based on the information specified by the user. | | FGETPOS | Gets the current file access position (offset), in bytes. | | FREMOVE | Delete disk files (requires the corresponding delete permission), similar to UNIX.rmcommand. | | FRENAME | Rename the existing file to the new name, similar to UNIX.mvcommand. | | FSEEK | Move the file pointer forward or backward by a specified number of bytes. | | NEW_LINE | Writes one or more line terminators specified by the operating system 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, appending the line terminator specified by the operating system. | | PUT | Writes a string to a file. | | PUT_LINE | Writes a line to a file, appending the line terminator specified by the operating system. |