9.5.2 UTL

Overview: Text files can be read or written from PL/pgSQL.

Features

Feature	Description
FCLOSE	Closes the specified file.
FCLOSE_ALL	Closes all files open in a session.
FCOPY	Copies a file.
FFLUSH	Flushes the buffer.
FGETATTR	Acquires the file existence and the size information.
FOPEN	Opens a file.
FRENAME	Changes the name of the file.
GET_LINE	Reads one line from a text file.
IS_OPEN	Checks if there are open files.
NEW_LINE	Writes a newline.
PUT	Writes a character string.
PUT_LINE	Adds a string and a newline and writes them.
PUTF	Writes a formatted character string.

Specification format

The file handler type is UTL_FILE.FILE_TYPE. A definition example is shown below.

Example

DECLARE
f UTL_FILE.FILE_TYPE;

General rules

FCLOSE

FCLOSE closes a file that is open.

Note

Specify an open file handler as the file handler.
The value returned is a NULL value.

Example

f := UTL_FILE.FCLOSE(f);

FCLOSE_ALL

FCLOSE_ALL closes all files open in a session.

Note

Files closed with FCLOSE_ALL can no longer be read or written.

Example

PERFORM UTL_FILE.FCLOSE_ALL();

FCOPY

FCOPY copies from a copy source file to a copy destination file.

Note

Specify the directory where the copy source file exists as the copy source directory name.
Specify the copy source file name as the file name of the copy source.
Specify the directory where the copy destination file is stored as the copy destination directory name.
Specify the copy destination file name as the file name of the copy destination.
Specify a slash (/) or a backslash (\) as a separator in the copy source directory name and the copy destination directory name.
Specify the starting line number of the file to be copied as the beginning line. If specifying, specify a value greater than 0. The starting line number is 1 if this is not specified.
Specify the end line number of the file to be copied as the end line. The last line number of the file is used if this is not specified.
The directories specified as the copy source and the copy destination must be registered beforehand in the UTL_FILE.UTL_FILE_DIR table by using the INSERT statement.
Specify a slash (/) as a separator in the directory name to be registered in the UTL_FILE.UTL_FILE_DIR table.

Example

Linux

PERFORM UTL_FILE.FCOPY('/home/symfo', 'regress_symfo.txt', '/home/symfo', 'regress_symfo2.txt');

Windows(R)

PERFORM UTL_FILE.FCOPY('c:/symfo', 'regress_symfo.txt', 'c:/symfo', 'regress_symfo2.txt');

The following is an example of registering a directory.

1) Check if the directory is registered. If it is already registered, step 2 is not required.

Linux

SELECT * FROM UTL_FILE.UTL_FILE_DIR WHERE dir='/home/symfo';

Windows(R)

SELECT * FROM UTL_FILE.UTL_FILE_DIR WHERE dir='c:/symfo';

2) Register the directory.

Linux

INSERT INTO UTL_FILE.UTL_FILE_DIR VALUES('/home/symfo');

Windows(R)

INSERT INTO UTL_FILE.UTL_FILE_DIR VALUES('c:/symfo');

The following is an example of deleting the directory information.

Linux

DELETE FROM UTL_FILE.UTL_FILE_DIR WHERE dir='/home/symfo';

Windows(R)

DELETE FROM UTL_FILE.UTL_FILE_DIR WHERE dir='c:/symfo';

FFLUSH

FFLUSH forcibly writes the buffer data to a file.

Note

Specify an open file handler as the file handler.

Example

PERFORM UTL_FILE.FFLUSH(f);

FGETATTR

FGETATTR extracts the file existence, file size, and information about the block size of the file.

Note

Specify the directory where the relevant file exists as the directory name.
Specify a slash (/) or a backslash (\) as a separator in the directory name.
Specify the relevant file name as the file name.
The information extracted is acquired as the values of the fexists, file_length, and blocksize columns with the SELECT statement.
fexists is a BOOLEAN type. If the file exists, the logical value is "true". If the file does not exist, the logical value is "false".
file_length is an INTEGER type. The length of the file is set in bytes. If the file does not exist, the value is NULL.
blocksize is an INTEGER type. The block size of the file is set in bytes. If the file does not exist, the value is NULL.
The directory specified as the directory name must be registered beforehand in the UTL_FILE.UTL_FILE_DIR table by using the INSERT statement.
Specify a slash (/) as a separator in the directory name to be registered in the UTL_FILE.UTL_FILE_DIR table.

Example

Linux

SELECT fexists, file_length, blocksize INTO file_flag, file_chack_length, size FROM UTL_FILE.FGETATTR('/home/symfo', 'regress_symfo.txt');

Windows(R)

SELECT fexists, file_length, blocksize INTO file_flag, file_chack_length, size FROM UTL_FILE.FGETATTR('c:/symfo', 'regress_symfo.txt');

Refer to "FCOPY" for an example of registering a directory in the UTL_FILE.UTL_FILE_DIR table.

FOPEN

FOPEN opens a file.

Note

Specify the directory where the relevant file exists as the directory name.
Specify a slash (/) or a backslash (\) as a separator in the directory name.
Specify the relevant file name as the file name.
Specify the mode for opening the file at open method. Specify the following:
r: Read
w: Write
a: Add
Specify the maximum string length (in bytes) that can be processed with one operation. If omitted, the default is "1024". Specify in the range from 1 to 32767.
The directory specified as the directory name must be registered beforehand in the UTL_FILE.UTL_FILE_DIR table by using the INSERT statement.
Specify a slash (/) as a separator in the directory name to be registered in the UTL_FILE.UTL_FILE_DIR table
Up to 50 files per session can be open at the same time.

Example

Linux

f := UTL_FILE.FOPEN('/home/symfo','regress_symfo.txt','r',1024);

Windows(R)

f := UTL_FILE.FOPEN('c:/symfo','regress_symfo.txt','r',1024);

Refer to "FCOPY" for an example of registering a directory in the UTL_FILE.UTL_FILE_DIR table.

FRENAME

FRENAME changes the name of an existing file.

Note

The directory name before change is the directory where the file was before change.
The file name before change is the file name before change.
The directory name after change is the directory where the file was created after change.
The file name after change is the file name after change.
Specify a slash (/) or a backslash (\) as a separator in the directory name.
Specify whether to overwrite a file with changes, if one exists in the directory with the logical value. If the "true" logical value is specified, files with changes are overwritten even if they exist. If the "false" logical value is specified, an error occurs if files with changes exist. If this is omitted, the logical value for "False" will be set.
Refer to "Boolean Type" in "The SQL Language" in the PostgreSQL Documentation for information on logical values.
The directories specified as the directories before the change and after the change need to be registered with INSERT statement in the UTL_FILE.UTL_FILE_DIR table.
Specify a slash (/) as a separator in the directory name to be registered in the UTL_FILE.UTL_FILE_DIR table.

Example

Linux

PERFORM UTL_FILE.FRENAME('/home/symfo', 'regress_symfo.txt', '/home/symfo',
 'regress_symfo2.txt', TRUE);

Windows(R)

PERFORM UTL_FILE.FRENAME('c:/symfo', 'regress_symfo.txt', 'c:/symfo',
 'regress_symfo2.txt', TRUE);

Refer to "FCOPY" for an example of registering a directory in the UTL_FILE.UTL_FILE_DIR table.

GET_LINE

GET_LINE reads one line from a file.

Note

Specify an open file handler as the file handler. Specify the file handler returned by FOPEN using the r (read) mode.
The load size is the number of bytes to be loaded from the file. If not specified, it will be the maximum string length of FOPEN.
The return value is the buffer that receives the line loaded from the file.
Newline characters are not loaded to the buffer.
An empty string is returned if a blank line is loaded.
Specify the maximum size(in bytes) of the data to be loaded at load size. Specify in the range from 1 to 32767. If a load size is not specified, then the maximum string length specified at FOPEN is set when a maximum string length is specified at FOPEN. 1024 is set if a maximum string length is not set at FOPEN.
If the line length is greater than the load size, the load size is loaded and the remaining is loaded upon the next call.
The NO_DATA_FOUND will be raised when trying to read past the last line.

Example

buff := UTL_FILE.GET_LINE(f);

IS_OPEN

IS_OPEN checks if there are open files.

Note

Specify the file handler to be verified as the file handler.
The return value is a BOOLEAN type. A "true" logical value represents the open state and the "false" logical value represents the closed state.

See

Refer to "Boolean Type" under "The SQL Language" in the PostgreSQL Documentation for information on logical values.

Example

   IF UTL_FILE.IS_OPEN(f) THEN
     PERFORM UTL_FILE.FCLOSE(f);
   END IF;

NEW_LINE

NEW_LINE writes one or more newlines.

Note

Specify an open file handler as the file handler.
The number of newlines is the number of newlines to be written to the file. If omitted, "1" is set.

Example

PERFORM UTL_FILE.NEW_LINE(f, 2);

PUT

PUT writes a string to a file.

Note

Specify an open file handler as the file handler. Specify the file handler that was opened with w (write) or a (append) with the FOPEN opening method.
Specify the character string to be written to the file for the character string.
The maximum size(in bytes) of the string is the maximum string length specified at FOPEN.
The return value is a TEXT type and is the buffer that receives the line loaded from the file.

Example

PERFORM UTL_FILE.PUT(f, 'ABC');

PUT_LINE

PUT_LINE adds a newline to a string and writes it.

Note

Specify an open file handler as the file handler. Specify the file handler that was opened with w (write) or a (append) with the FOPEN opening method.
The logical value specifies whether to forcibly write to the file. If the "true" logical value is specified, file writing is forced. If the "false" logical value is specified, file writing is asynchronous. If this is omitted, the logical value for "False" will be set.
The maximum size of the string (in bytes) is the maximum string length value specified at FOPEN.

Example

PERFORM UTL_FILE.PUT_LINE(f, 'ABC', TRUE);

PUTF

PUTF writes a string that uses formatting.

Note

Specify an open file handler as the file handler. Specify the file handler that was opened with w (write) or a (append) with the FOPEN opening method.
Format is a string that includes the formatting characters \n and %s.
The \n in the format is code for a newline.
Specify the same number of input values as there are %s in the format. Up to a maximum of five input values can be specified. The %s in the format are replaced with the corresponding input characters. If an input value corresponding to %s is not specified, it is replaced with an empty string.

Example

PERFORM UTL_FILE.PUTF(f, '[1=%s, 2=%s, 3=%s, 4=%s, 5=%s]\n', '1', '2', '3', '4', '5');

Example

The procedures and examples are shown below.

Preparation
Before starting a new job that uses UTL_FILE, register the directory in the UTL_FILE.UTL_FILE_DIR table. Perform this step only once before starting a new job. Then, use the SELECT statement to check if the directory is registered. If it is not registered, register it by using the INSERT statement.
Example:
Linux
```
SELECT * FROM UTL_FILE.UTL_FILE_DIR WHERE dir='/home/symfo';
INSERT INTO UTL_FILE.UTL_FILE_DIR(dir) VALUES('/home/symfo');
```
Windows(R)
```
SELECT * FROM UTL_FILE.UTL_FILE_DIR WHERE dir='c:/symfo';
INSERT INTO UTL_FILE.UTL_FILE_DIR(dir) VALUES('c:/symfo');
```

Performing a job

Perform a job that uses UTL_FILE.

Example:

Linux

CREATE OR REPLACE FUNCTION gen_file(mydir TEXT, infile TEXT, outfile TEXT, copyfile TEXT) RETURNS void AS $$
DECLARE
  v1 VARCHAR(32767);
  inf UTL_FILE.FILE_TYPE;
  otf UTL_FILE.FILE_TYPE;
BEGIN
  inf := UTL_FILE.FOPEN(mydir, infile,'r',256);
  otf := UTL_FILE.FOPEN(mydir, outfile,'w');
  v1 := UTL_FILE.GET_LINE(inf,256);
  PERFORM UTL_FILE.PUT_LINE(otf,v1,TRUE);
  v1 := UTL_FILE.GET_LINE(inf,256);
  PERFORM UTL_FILE.PUTF(otf,'%s\n',v1);
  v1 := UTL_FILE.GET_LINE(inf, 256);
  PERFORM UTL_FILE.PUT(otf,v1);
  PERFORM UTL_FILE.NEW_LINE(otf);
  PERFORM UTL_FILE.FFLUSH(otf);

  inf := UTL_FILE.FCLOSE(inf);
  otf := UTL_FILE.FCLOSE(otf);

  PERFORM UTL_FILE.FCOPY(mydir, outfile, mydir, copyfile, 2, 3);
  PERFORM UTL_FILE.FRENAME(mydir, outfile, mydir, 'rename.txt');

END;
$$ LANGUAGE plpgsql;

SELECT gen_file('/home/symfo', 'input.txt', 'output.txt', 'copyfile.txt');

Windows(R)

CREATE OR REPLACE FUNCTION gen_file(mydir TEXT, infile TEXT, outfile TEXT, copyfile TEXT) RETURNS void AS $$
DECLARE
  v1 VARCHAR(32767);
  inf UTL_FILE.FILE_TYPE;
  otf UTL_FILE.FILE_TYPE;
BEGIN
  inf := UTL_FILE.FOPEN(mydir, infile,'r',256);
  otf := UTL_FILE.FOPEN(mydir, outfile,'w');
  v1 := UTL_FILE.GET_LINE(inf,256);
  PERFORM UTL_FILE.PUT_LINE(otf,v1,TRUE);
  v1 := UTL_FILE.GET_LINE(inf,256);
  PERFORM UTL_FILE.PUTF(otf,'%s\n',v1);
  v1 := UTL_FILE.GET_LINE(inf, 256);
  PERFORM UTL_FILE.PUT(otf,v1);
  PERFORM UTL_FILE.NEW_LINE(otf);
  PERFORM UTL_FILE.FFLUSH(otf);

  inf := UTL_FILE.FCLOSE(inf);
  otf := UTL_FILE.FCLOSE(otf);

  PERFORM UTL_FILE.FCOPY(mydir, outfile, mydir, copyfile, 2, 3);
  PERFORM UTL_FILE.FRENAME(mydir, outfile, mydir, 'rename.txt');

END;
$$ LANGUAGE plpgsql;

SELECT gen_file('c:/symfo', 'input.txt', 'output.txt', 'copyfile.txt');

Post-processing
If you remove a job that uses UTL_FILE, delete the directory information from the UTL_FILE.UTL_FILE_DIR table. Ensure that the directory information is not being used by another job before deleting it.
Example:
Linux
```
DELETE FROM UTL_FILE.UTL_FILE_DIR WHERE dir='/home/symfo';
```
Windows(R)
```
DELETE FROM UTL_FILE.UTL_FILE_DIR WHERE dir='c:/symfo';
```

9.5.2 UTL_FILE