UTL FILE
UTL_FILE#
The UTL_FILE package enables writing and reading by accessing the text tiles which are managed by the operation system.
The procedures and functions which are comprised of the UTL_FILE package are listed in the following table below.
| Procedures/Functions | Description |
|---|---|
| FCLOSE | Closes a file |
| FCLOSE_ALL | Closes all open files in the current session |
| FCOPY | Copies a file |
| FFLUSH | Physically archives the data into a file |
| FOPEN | Opens a file with the object of writing or reading |
| FREMOVE | Deletes a file |
| FRENAME | Changes a file name |
| GET_LINE | Searches for a single line in a file |
| IS_OPEN | Checks if the file is opened |
| NEW_LINE | Prints the new-line characters |
| PUT | Records a character string into a file |
| PUT_LINE | Records a character string by attaching the new-line characters(= PUT+NEW_LINE) |
Refer to File Control in Altibase Stored Procedures manual for in-depth information on each procedure and function pertaining to the UTL_FILE prcedures and packages.
FCLOSE#
The FCLOSE is a procedure providing a function of closing and re-initializing the file handle which is opened.
Syntax#
UTL_FILE.FCLOSE(file IN OUT FILE_TYPE);
Parameter#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| file | IN OUT | FILE_TYPE | File handle |
Return Value#
Because it is a stored procedure, there is no return value.
Exception#
There is no exception.
FCLOSE_ALL#
The FCLOSE_ALL is a procedure providing a function of closing all the file handles that are open in the current session.
Syntax#
UTL_FILE.FCLOSE_ALL;
Parameter#
None
Return Value#
Because it is a stored procedure, there is no return value.
Exception#
It always succeeds unless an error occurs during the execution.
FCOPY#
The FCOPY is a procedure providing a function of copying a file by line unit. If the result file does not exist in the related directory, the contents of source file is copied when creating a file. If the result file exits, the contents of source file are overwritten.
Syntax#
UTL_FILE.FCOPY (
location IN VARCHAR(40),
filename IN VARCHAR(256),
dest_dir IN VARCHAR(40),
dest_file IN VARCHAR(256),
start_line IN INTEGER DEFAULT 1,
end_line IN INTEGER DEFAULT NULL);
Parameters#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| location | IN | VARCHAR(40) | The directory name in which the original file, the target of copy, is located. |
| filename | IN | VARCHAR(256) | The name of the source file |
| dest_dir | IN | VARCHAR(40) | The directory name in which result files are located |
| dest_file | IN | VARCHAR(256) | The name of the result file |
| start_line | IN | INTEGER | The startling line number to copy (Default value: 1) |
| end_line | IN | INTEGER | The last line number to copy. If it is default value, the file is copied to the end of the file. (Default value: NULL) |
Return Value#
Because it is a stored procedure, there is no return value.
Exception#
The FCOPY might cause the following system-defined exceptions.
-
INVALID_PATH
-
ACCESS_DENIED
-
INVALID_OPERATION
-
READ_ERROR
-
WRITE_ERROR
FFLUSH#
The FFLUSH is a procedure which physically archives the data existing in the buffer into a file.
Syntax#
UTL_FILE.FFLUSH(file IN FILE_TYPE);
Parameter#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| file | IN | FILE_TYPE | The file handle |
Return Value#
Because it is a stored procedure, there is no return value.
Exception#
The FFLUSH might cause the following system-defined exceptions.
-
INVALID_FILEHANDLE
-
WRITE_ERROR
FOPEN#
The FOPEN procedure opens a file to read or write.
Syntax#
UTL_FILE.FOPEN(
location IN VARCHAR(40),
filename IN VARCHAR(256),
open_mode IN VARCHAR(4),
max_linesize IN INTEGER DEFAULT NULL);
Parameters#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| location | IN | VARCHAR(40) | The name of a directory object located in a file |
| filename | IN | VARCHAR(256) | The file name |
| open_mode | IN | VARCHAR(4) | The input available options are as follows. r: Read w: Write a: Subsequent writing * Caution: Such options cannot be combined to use. (e.g., rw, wa) |
| max_linesize | IN | INTEGER | This is the parameter only for Integer compatibility which can be neglected. |
Return Value#
The file handle with FILE_TYPE data type are returned if successfully executed.
Exception#
The FOPEN might cause the following system-defined exceptions.
-
INVALID_PATH
-
ACCESS_DENIED
-
INVALID_OPERATION
-
INVALID_MODE
FREMOVE#
The FREMOVE is a procedure deleting a file.
Syntax#
UTL_FILE.FREMOVE (
location IN VARCHAR(40),
filename IN VARCHAR(256));
Parameters#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| location | IN | VARCHAR(40) | The directory name in which a file is located |
| filename | IN | VARCHAR(256) | The file name |
Return Value#
Because it is a stored procedure, there is no return value.
Exception#
The FREMOVE might cause the following system-defined exceptions.
-
INVALID_PATH
-
ACCESS_DENIED
-
DELETE_FAILED
FRENAME#
The FRENAME is a stored procedure which can modifies the file name, or transfer the file to a different location. It is the same with UNIX mv command.
Syntax#
UTL_FILE.FRENAME (
location IN VARCHAR(40),
filename IN VARCHAR(256),
dest_dir IN VARCHAR(40),
dest_file IN VARCHAR(256),
overwrite IN BOOLEAN DEFAULT FALSE );
Parameters#
| Name | In/Out | Data Type | Description |
|---|---|---|---|
| location | IN | VARCHAR(40) | The directory in which the source file is situated. |
| filename | IN | VARCHAR(256) | The name of source file. |
| dest_dir | IN | VARCHAR(40) | The directory in which the result file is situated. |
| dest_file | IN | VARCHAR(256) | The name of result file. |
| overwrite | IN | BOOLEAN | Update option when the result file already exists. TRUE: Update as a new file FALSE(Default Value): Not to update. |
Return Value#
Because it is stored procedure, there is no return value.
Exception#
The FRENAME might cause the following system-defined exceptions.
-
INVALID_PATH
-
ACCESS_DENIED
-
RENAME_FAILED
GET_LINE#
The GET_LINE is a stored procedure reading every other line from a file.
Syntax#
UTL_FILE.GET_LINE(
file IN FILE_TYPE,
buffer OUT VARCHAR(32768),
len IN INTEGER DEFAULT NULL);
Parameters#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| file | IN | FILE_TYPE | The file handle |
| buffer | OUT | VARCHAR(32768) | The buffer to store the every other line from a file. |
| len | IN | INTEGER | The maximum bytes which can read a line form a file. 1024bytes are read unless otherwise specified. Default Value: NULL |
Return Value#
Because it is a stored procedure, there is no return value.
Exception#
The GET_LINE might cause the following system-defined exceptions.
-
NO_DATA_FOUND
-
READ_ERROR
-
INVALID_FILEHANDLE
IS_OPEN#
The IS_OPEN function checks on the file whether it is open or not.
Syntax#
UTL_FILE.IS_OPEN(file IN FILE_TYPE);
Parameter#
| Name | In/Out | Data Type | Description |
|---|---|---|---|
| file | IN | FILE_TYPE | The file handle |
Return Value#
It returns TRUE when it is open, but FALSE is returned when it is closed.
Exception#
There is no exception.
NEW_LINE#
The NEW_LINE is a procedure which archives the new-line characters to a file(\n for Unix).
Syntax#
UTL_FILE.NEW_LINE(
file IN FILE_TYPE,
lines IN INTEGER DEFAULT 1);
Parameters#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| file | IN | FILE_TYPE | The file handle |
| lines | IN | INTEGER | The number of line to record. Default Value: 1 |
Return Value#
Because it is a stored procedure, there is no return value.
Exception#
There is no exception.
PUT#
The PUT is a procedure storing a character string which is read from a file in the buffer.
Syntax#
UTL_FILE.PUT(
file IN FILE_TYPE,
buffer IN VARCHAR(32768));
Parameters#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| file | IN | FILE_TYPE | The file handle |
| buffer | IN | VARCHAR(32768) | The buffer to store a file from the read character strings |
Return Value#
Because it is a stored procedure, there is no return value.
Exceptions#
The PUT might cause the following system-defined exceptions.
-
INVALID_FILEHANDLE
-
WRITE_ERROR
PUT_LINE#
The PUT_LINE is a stored procedure archiving a line including a character string to a file.
Syntax#
UTL_FILE.PUT_LINE(
file IN FILE_TYPE,
buffer IN VARCHAR(32768)
autoflush IN BOOLEAN DEFAULT FALSE);
Parameters#
| Name | In/Output | Data Type | Description |
|---|---|---|---|
| file | IN | FILE_TYPE | The file handle |
| buffer | IN | VARCHAR(32768) | The buffer storing a character string which is read from a file |
| autoflush | IN | BOOLEAN | Options to empty the buffer. Default Value: FALSE (Not to empty) |
Return Value#
Because it is a stored procedure, there is no return value.
Exceptions#
The PUT_LINE might cause the following system-defined exceptions.
-
INVALID_FILEHANDLE
-
WRITE_ERROR