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 is not supported.
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. |
| FCOPY | Copies lines from the source file to the destination file. |
| FCLOSE_ALL | Closes all UTL_FILE files opened in the current session. |
| FFLUSH | Flushes the data in the cache to the file. |
| FOPEN | Opens the file based on the specified information. |
| FOPEN_NCHAR | Opens the file in NCHAR mode. |
| FGETPOS | Obtains the current position (offset) in the file, in bytes. |
| FGETATTR | Reads operating system file attributes (such as existence and size). |
| 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. |
| NEW_LINE_NCHAR | Inserts a newline character into the file. |
| GET_RAW | Reads raw binary data from the operating system file. |
| GET_LINE | Reads a line of text. |
| GET_LINE_NCHAR | Reads a line of content from a text file in Unicode mode. |
| PUT | Writes a string to the file. |
| PUT_RAW | Stores the input parameter as RAW in the specified file. |
| PUT_LINE | Writes a line to the file and appends an operating system-specific line terminator. |
| PUT_NCHAR | Appends to the current line buffer in NCHAR mode. |
| PUT_LINE_NCHAR | Writes Unicode characters to the file. |
| PUTF | Writes to the specified file according to the format string. |
| PUTF_NCHAR | Writes to the specified file in NCHAR mode according to the format string. |
| IS_OPEN | Determines whether the handle is still open. |