UTL_FILE v16

Suggest edits

The UTL_FILE package reads from and writes to files on the operating system’s file system. A superuser must grant non-superusers EXECUTE privilege on the UTL_FILE package before they can use any of the functions or procedures in the package. For example, the following command grants the privilege to user mary:

GRANT EXECUTE ON PACKAGE SYS.UTL_FILE TO mary;

Also, the operating system username enterprisedb must have the appropriate read/write permissions on the directories and files that the UTL_FILE functions and procedures access. If the required file permissions aren't in place, an exception is thrown in the UTL_FILE function or procedure.

A handle to the file to write to or read from is used to reference the file. The file handle is defined by a public variable UTL_FILE.FILE_TYPE in the UTL_FILE package. A variable of type FILE_TYPE must be declared to receive the file handle returned by calling the FOPEN function. The file handle is then used for all subsequent operations on the file.

References to directories on the file system are done using the directory name or alias that's assigned to the directory using the CREATE DIRECTORY command.

The procedures and functions available in the UTL_FILE package are listed in the following table.

Function/procedure	Return type	Description
`FCLOSE(file IN OUT)`	n/a	Closes the specified file identified by `file`.
`FCLOSE_ALL`	n/a	Closes all open files.
`FCOPY(location, filename, dest_dir, dest_file [, start_line [, end_line ] ])`	n/a	Copies `filename` in the directory identified by `location` to file `dest_file` in directory `dest_dir`, from line `start_line` to line `end_line`.
`FFLUSH(file)`	n/a	Forces data in the buffer to be written to disk in the file identified by `file`.
`FGETATTR(location, filename, exists, file_length, block_size)`	n/a	Retrieves the attributes for a file when its `filename` and `location` are given.
`FGETPOS(file)`	`INTEGER`	Returns the relative offset position in the opened file as an integer, in bytes.
`FOPEN(location, filename, open_mode [, max_linesize ])`	`FILE_TYPE`	Opens file `filename` in the directory identified by `location`.
`FOPEN_NCHAR(location, filename, open_mode, max_linesize)`	`FILE_TYPE`	Opens file `filename` in the directory identified by `location` to read and/or write Unicode character set data.
`FREMOVE(location, filename)`	n/a	Removes the specified file from the file system.
`FRENAME(location, filename, dest_dir, dest_file [, overwrite ])`	n/a	Renames the specified file.
`FSEEK(file, absolute_offset, relative_offset)`	n/a	Moves the file pointer within a given file by the number of bytes specified.
`GET_LINE(file, buffer OUT)`	n/a	Reads a line of text into the variable `buffer` from the file identified by `file`.
`GET_RAW(file, buffer OUT [, len ])`	`BYTEA`	Reads a `RAW` string value from a file, keeps those into read buffer, and adjusts the file pointer accordingly by the number of bytes read, ignoring the end-of-file terminator.
`IS_OPEN(file)`	`BOOLEAN`	Determines whether the given file is open.
`NEW_LINE(file [, lines ])`	n/a	Writes an end-of-line character sequence into the file.
`PUT(file, buffer)`	n/a	Writes `buffer` to the given file. `PUT` doesn't write an end-of-line character sequence.
`PUT_LINE(file, buffer)`	n/a	Writes `buffer` to the given file. An end-of-line character sequence is added by the `PUT_LINE` procedure.
`PUTF(file, format [, arg1 ] [, ...])`	n/a	Writes a formatted string to the given file. You can specify up to five substitution parameters, `arg1,...arg5`, for replacement in `format`.
`PUT_NCHAR(file, buffer)`	n/a	Writes `buffer` to the given file and converts data to Unicode. `PUT` doesn't write an end-of-line character sequence.
`PUT_RAW(file, buffer [, autoflush ])`	`BOOLEAN`	Accepts a `RAW` data value as input and writes those values to the output buffer.

EDB Postgres Advanced Server's implementation of UTL_FILE is a partial implementation when compared to Oracle's version. Only the functions and procedures listed in the table are supported.

UTL_FILE exception codes

If a call to a UTL_FILE procedure or function raises an exception, you can use the condition name to catch the exception. The UTL_FILE package reports the following exception codes compatible with Oracle databases.

Exception code	Condition name
`-29283`	`invalid_operation`
`-29285`	`write_error`
`-29284`	`read_error`
`-29282`	`invalid_filehandle`
`-29287`	`invalid_maxlinesize`
`-29281`	`invalid_mode`
`-29280`	`invalid_path`

Setting file permissions with utl_file.umask

When a UTL_FILE function or procedure creates a file, the following are the default file permissions:

-rw------- 1 enterprisedb enterprisedb 21 Jul 24 16:08 utlfile

All permissions are denied on users belonging to the enterprisedb group as well as all other users. Only the enterprisedb user has read and write permissions on the created file.

If you want to have a different set of file permissions on files created by the UTL_FILE functions and procedures, set the utl_file.umask configuration parameter.

The utl_file.umask parameter sets the file mode creation mask (or simply the mask) in a manner similar to the Linux umask command. This parameter is for use only in the EDB Postgres Advanced Server UTL_FILE package.

Note

The utl_file.umask parameter isn't supported on Windows systems.

The value specified for utl_file.umask is a 3- or 4-character octal string that's valid for the Linux umask command. The setting determines the permissions on files created by the UTL_FILE functions and procedures. (Refer to any information source regarding Linux or Unix systems for information on file permissions and the use of the umask command.)

Example

This example sets the file permissions with utl_file.umask.

First, set up the directory in the file system for the UTL_FILE package to use. Be sure the applicable operating system account enterprisedb or postgres can read and write in the directory.

mkdir /tmp/utldir
chmod 777 /tmp/utldir

The CREATE DIRECTORY command is issued in psql to create the directory database object using the file system directory you created:

CREATE DIRECTORY utldir AS '/tmp/utldir';

Set the utl_file.umask configuration parameter. The following setting allows the file owner any permission. Group users and other users are permitted any permission except for the execute permission.

SET utl_file.umask TO '0011';

In the same session during which the utl_file.umask parameter is set to the desired value, run the UTL_FILE functions and procedures.

DECLARE
    v_utlfile       UTL_FILE.FILE_TYPE;
    v_directory     VARCHAR2(50) := 'utldir';
    v_filename      VARCHAR2(20) := 'utlfile';
BEGIN
    v_utlfile := UTL_FILE.FOPEN(v_directory, v_filename, 'w');
    UTL_FILE.PUT_LINE(v_utlfile, 'Simple one-line file');
    DBMS_OUTPUT.PUT_LINE('Created file: ' || v_filename);
    UTL_FILE.FCLOSE(v_utlfile);
END;

The permission settings on the resulting file show that, in addition to the file owner, group users and other users have read and write permissions on the file.

$ pwd
/tmp/utldir
$ ls -l
total 4
-rw-rw-rw- 1 enterprisedb enterprisedb 21 Jul 24 16:04 utlfile

You can also set this parameter on a per-role basis with the ALTER ROLE command. You can set it for a single database with the ALTER DATABASE command or for the entire database server instance by setting it in the postgresql.conf file.

FCLOSE

The FCLOSE procedure closes an open file.

FCLOSE(<file> IN OUT FILE_TYPE)

Parameters

file

Variable of type FILE_TYPE containing a file handle of the file to close.

FCLOSE_ALL

The FLCLOSE_ALL procedures closes all open files. The procedure executes successfully even if there are no open files to close.

FCLOSE_ALL

FCOPY

The FCOPY procedure copies text from one file to another.

FCOPY(<location> VARCHAR2, <filename> VARCHAR2, <dest_dir> VARCHAR2, <dest_file> VARCHAR2
  [, <start_line> PLS_INTEGER [, <end_line> PLS_INTEGER ] ])

Parameters

location

Directory name of the directory containing the file to copy, as stored in pg_catalog.edb_dir.dirname.

filename

Name of the source file to copy.

dest_dir

Directory name of the directory to which to copy the file, as stored in pg_catalog.edb_dir.dirname.

dest_file

Name of the destination file.

start_line

Line number in the source file from which copying begins. The default is 1.

end_line

Line number of the last line in the source file to copy. If omitted or null, copying goes to the last line of the file.

Examples

This example makes a copy of a file C:\TEMP\EMPDIR\empfile.csv, which contains a comma-delimited list of employees from the emp table. The copy, empcopy.csv, is then listed.

CREATE DIRECTORY empdir AS 'C:/TEMP/EMPDIR';

DECLARE
    v_empfile       UTL_FILE.FILE_TYPE;
    v_src_dir       VARCHAR2(50) := 'empdir';
    v_src_file      VARCHAR2(20) := 'empfile.csv';
    v_dest_dir      VARCHAR2(50) := 'empdir';
    v_dest_file     VARCHAR2(20) := 'empcopy.csv';
    v_emprec        VARCHAR2(120);
    v_count         INTEGER := 0;
BEGIN
    UTL_FILE.FCOPY(v_src_dir,v_src_file,v_dest_dir,v_dest_file);
    v_empfile := UTL_FILE.FOPEN(v_dest_dir,v_dest_file,'r');
    DBMS_OUTPUT.PUT_LINE('The following is the destination file, ''' ||
        v_dest_file || '''');
    LOOP
        UTL_FILE.GET_LINE(v_empfile,v_emprec);
        DBMS_OUTPUT.PUT_LINE(v_emprec);
        v_count := v_count + 1;
    END LOOP;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            UTL_FILE.FCLOSE(v_empfile);
            DBMS_OUTPUT.PUT_LINE(v_count || ' records retrieved');
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
            DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
END;

The following is the destination file, 'empcopy.csv'
7369,SMITH,CLERK,7902,17-DEC-80,800,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20
7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
7876,ADAMS,CLERK,7788,23-MAY-87,1100,,20
7900,JAMES,CLERK,7698,03-DEC-81,950,,30
7902,FORD,ANALYST,7566,03-DEC-81,3000,,20
7934,MILLER,CLERK,7782,23-JAN-82,1300,,10
14 records retrieved

FFLUSH

The FFLUSH procedure flushes unwritten data from the write buffer to the file.

FFLUSH(<file> FILE_TYPE)

Parameters

file

Variable of type FILE_TYPE containing a file handle.

Examples

Each line is flushed after the NEW_LINE procedure is called.

DECLARE
    v_empfile       UTL_FILE.FILE_TYPE;
    v_directory     VARCHAR2(50) := 'empdir';
    v_filename      VARCHAR2(20) := 'empfile.csv';
    CURSOR emp_cur IS SELECT * FROM emp ORDER BY empno;
BEGIN
    v_empfile := UTL_FILE.FOPEN(v_directory,v_filename,'w');
    FOR i IN emp_cur LOOP
        UTL_FILE.PUT(v_empfile,i.empno);
        UTL_FILE.PUT(v_empfile,',');
        UTL_FILE.PUT(v_empfile,i.ename);
        UTL_FILE.PUT(v_empfile,',');
        UTL_FILE.PUT(v_empfile,i.job);
        UTL_FILE.PUT(v_empfile,',');
        UTL_FILE.PUT(v_empfile,i.mgr);
        UTL_FILE.PUT(v_empfile,',');
        UTL_FILE.PUT(v_empfile,i.hiredate);
        UTL_FILE.PUT(v_empfile,',');
        UTL_FILE.PUT(v_empfile,i.sal);
        UTL_FILE.PUT(v_empfile,',');
        UTL_FILE.PUT(v_empfile,i.comm);
        UTL_FILE.PUT(v_empfile,',');
        UTL_FILE.PUT(v_empfile,i.deptno);
        UTL_FILE.NEW_LINE(v_empfile);
        UTL_FILE.FFLUSH(v_empfile);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Created file: ' || v_filename);
    UTL_FILE.FCLOSE(v_empfile);
END;

FGETATTR

The FGETATTR function retrieves the attributes for a given file. When you enter filename and location values, it returns whether the file exists. If the file exists, it also returns file_length and block_size values. If the file doesn't exist or an error occurs, the attributes return as NULL.

FGETATTR(<location> VARCHAR2, <filename> VARCHAR2, <exists> BOOLEAN, <file_length> NUMBER, <block_size> BINARY_INTEGER)

Parameters

location

Name of the directory containing the file whose attributes are being retrieved, stored in pg_catalog.edb_dir.dirname.

filename

Name of the file whose attributes are being retrieved.

exists

Report of whether the file exists.

file_length

Size of the file in bytes.

block_size

System block size of the file in bytes.

Examples

This example retrieves the attributes for the file empfile.csv:

DECLARE
   l_file_exists BOOLEAN;
   l_file_len    NUMBER;
   l_blocksize   BINARY_INTEGER;
   f utl_file.file_type;
   v_empfile       UTL_FILE.FILE_TYPE;
   v_directory     VARCHAR2(50) := 'empdir';
   v_filename      VARCHAR2(20) := 'empfile.csv';
     
BEGIN
   utl_file.fgetattr(
     location    => v_directory,
     filename    => v_filename,
     fexists     => l_file_exists,
     file_length => l_file_len,
     block_size  => l_blocksize);
   IF l_file_exists THEN
     dbms_output.put_line('File found, size=' || l_file_len);
   ELSE
     dbms_output.put_line('File not found.');
   END IF;
END;
File found, size=677

FGETPOS

The FGETPOS function returns the relative offset position in the opened file as an integer, in bytes.

FGETPOS(<file>)

Parameters

file

Variable of type FILE_TYPE containing the file handle of the opened file.

Examples

Thus example finds the position of the file empfile.csv:

declare
    f utl_file.file_type;
    v_empfile       UTL_FILE.FILE_TYPE;
    v_directory     VARCHAR2(50) := 'empdir';
    v_filename      VARCHAR2(20) := 'empfile.csv';
  pos int;
begin
  f := utl_file.fopen(v_directory, v_filename, 'r');
  utl_file.fseek(f, 15);
  pos := utl_file.fgetpos(f);
  dbms_output.put_line('1.position: ' || pos);
  utl_file.fseek(f, NULL, -5);
  pos := utl_file.fgetpos(f);
  dbms_output.put_line('2.position: ' || pos);
  utl_file.fclose(f);
end;
1.position: 15
2.position: 10

FOPEN

The FOPEN function opens a file for I/O.

<filetype> FILE_TYPE FOPEN(<location> VARCHAR2, <filename> VARCHAR2,<open_mode> VARCHAR2
  [, <max_linesize> BINARY_INTEGER ])

Parameters

location

Directory name of the directory containing the file to open, as stored in pg_catalog.edb_dir.dirname.

filename

Name of the file to open.

open_mode

Mode in which to open the file. Modes are:

- `a` &mdash; Append to file.
- `r` &mdash; Read from file. 
- `w` &mdash; Write to file.

max_linesize

Maximum size of a line in characters. In read mode, an exception is thrown if you try to read a line exceeding max_linesize. In write and append modes, an exception is thrown if you try to write a line exceeding max_linesize. The end-of-line characters aren't included in determining if the maximum line size is exceeded. This behavior isn't compatible with Oracle databases. Oracle counts the end-of-line characters.

filetype

Variable of type FILE_TYPE containing the file handle of the opened file.

FOPEN_NCHAR

The FOPEN_NCHAR procedure opens the file filename in the directory identified by location to read and/or write Unicode character set data.

FOPEN_NCHAR(location VARCHAR2, filename VARCHAR2, open_mode VARCHAR2, max_linesize INTEGER)

Parameters

location

Name of the directory containing the file to open, stored in pg_catalog.edb_dir.dirname.

filename

Name of the file to open.

open_mode

Mode in which to open the file. Modes are:

- `a` &mdash; Append to file.
- `r` &mdash; Read from file. 
- `w` &mdash; Write to file.

max_linesize

Examples

Thus example opens the file empfile1.csv:

declare
    f utl_file.file_type;
    t text;
    v_empfile       UTL_FILE.FILE_TYPE;
    v_directory     VARCHAR2(50) := 'empdir';
    v_filename      VARCHAR2(20) := 'empfile1.csv';
    txt1            text;
begin
  f := utl_file.fopen_nchar(v_directory, v_filename, 'w');
  utl_file.put_nchar(f, 'Hello - 1'::text);
  utl_file.put_nchar(f, 100::numeric);
  utl_file.put_nchar(f, '2006-08-13 12:34:56'::timestamp);
  utl_file.put_nchar(f, '2001-12-27 04:05:06.789-08'::pg_catalog.date);
  utl_file.put_nchar(f, '2001-12-27 04:05:06.789-08'::time);
  utl_file.fclose(f);

  f := utl_file.fopen_nchar(v_directory, v_filename, 'r');
  loop
    utl_file.get_line_nchar(f, txt1);
    raise notice '%', txt1;
  end loop;
exception
  when no_data_found then
    raise notice 'finish % ', sqlerrm;
    utl_file.fclose(f);

end;
NOTICE:  Hello - 110013-AUG-06 12:34:5627-DEC-01 00:00:00 +00:0004:05:06.789
NOTICE:  finish no data found

FREMOVE

The FREMOVE procedure removes a file from the system.

FREMOVE(<location> VARCHAR2, <filename> VARCHAR2)

An exception is thrown if the file doesn't exist.

Parameters

location

Directory name of the directory containing the file to remove, as stored in pg_catalog.edb_dir.dirname.

filename

Name of the file to remove.

Examples

This example removes the file empfile.csv:

DECLARE
    v_directory     VARCHAR2(50) := 'empdir';
    v_filename      VARCHAR2(20) := 'empfile.csv';
BEGIN
    UTL_FILE.FREMOVE(v_directory,v_filename);
    DBMS_OUTPUT.PUT_LINE('Removed file: ' || v_filename);
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
            DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
END;

Removed file: empfile.csv

FRENAME

The FRENAME procedure renames a file, effectively moving a file from one location to another.

FRENAME(<location> VARCHAR2, <filename> VARCHAR2, <dest_dir> VARCHAR2, <dest_file> VARCHAR2,
  [ <overwrite> BOOLEAN ])

Parameters

location

Directory name of the directory containing the file to rename, as stored in pg_catalog.edb_dir.dirname.

filename

Name of the source file to rename.

dest_dir

Directory name of the directory to which to locate the renamed file, as stored in pg_catalog.edb_dir.dirname.

dest_file

New name of the file.

overwrite

Replaces any existing file named dest_file in dest_dir if set to TRUE. An exception is thrown if set to FALSE (the default).

Examples

This example renames a file, C:\TEMP\EMPDIR\empfile.csv, containing a comma-delimited list of employees from the emp table. The renamed file, C:\TEMP\NEWDIR\newemp.csv, is then listed.

CREATE DIRECTORY "newdir" AS 'C:/TEMP/NEWDIR';

DECLARE
    v_empfile       UTL_FILE.FILE_TYPE;
    v_src_dir       VARCHAR2(50) := 'empdir';
    v_src_file      VARCHAR2(20) := 'empfile.csv';
    v_dest_dir      VARCHAR2(50) := 'newdir';
    v_dest_file     VARCHAR2(50) := 'newemp.csv';
    v_replace       BOOLEAN := FALSE;
    v_emprec        VARCHAR2(120);
    v_count         INTEGER := 0;
BEGIN
    UTL_FILE.FRENAME(v_src_dir,v_src_file,v_dest_dir,
        v_dest_file,v_replace);
    v_empfile := UTL_FILE.FOPEN(v_dest_dir,v_dest_file,'r');
    DBMS_OUTPUT.PUT_LINE('The following is the renamed file, ''' ||
        v_dest_file || '''');
    LOOP
        UTL_FILE.GET_LINE(v_empfile,v_emprec);
        DBMS_OUTPUT.PUT_LINE(v_emprec);
        v_count := v_count + 1;
    END LOOP;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            UTL_FILE.FCLOSE(v_empfile);
            DBMS_OUTPUT.PUT_LINE(v_count || ' records retrieved');
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
            DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
END;

The following is the renamed file, 'newemp.csv'
7369,SMITH,CLERK,7902,17-DEC-80 00:00:00,800.00,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81 00:00:00,1600.00,300.00,30
7521,WARD,SALESMAN,7698,22-FEB-81 00:00:00,1250.00,500.00,30
7566,JONES,MANAGER,7839,02-APR-81 00:00:00,2975.00,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81 00:00:00,1250.00,1400.00,30
7698,BLAKE,MANAGER,7839,01-MAY-81 00:00:00,2850.00,,30
7782,CLARK,MANAGER,7839,09-JUN-81 00:00:00,2450.00,,10
7788,SCOTT,ANALYST,7566,19-APR-87 00:00:00,3000.00,,20
7839,KING,PRESIDENT,,17-NOV-81 00:00:00,5000.00,,10
7844,TURNER,SALESMAN,7698,08-SEP-81 00:00:00,1500.00,0.00,30
7876,ADAMS,CLERK,7788,23-MAY-87 00:00:00,1100.00,,20
7900,JAMES,CLERK,7698,03-DEC-81 00:00:00,950.00,,30
7902,FORD,ANALYST,7566,03-DEC-81 00:00:00,3000.00,,20
7934,MILLER,CLERK,7782,23-JAN-82 00:00:00,1300.00,,10
14 records retrieved

FSEEK

The FSEEK procedure moves the file pointer within a given file by the number of bytes specified.

FSEEK( <file> FILE_TYPE, <absolute_offset> INTEGER, <relative_offset> INTEGER)

Parameters

file

Variable of type FILE_TYPE containing the file handle of the file where the file pointer is being moved.

absolute_offset

The absolute number of bytes to move the file pointer.

relative_offset

The relative number of bytes to move the file pointer.

Examples

This examples moves the file pointer in the file empfile.csv:

declare
    f utl_file.file_type;
    v_empfile       UTL_FILE.FILE_TYPE;
    v_directory     VARCHAR2(50) := 'empdir';
    v_filename      VARCHAR2(20) := 'empfile.csv';
  pos int;
begin
  f := utl_file.fopen(v_directory, v_filename, 'r');
  utl_file.fseek(f, 15);
  pos := utl_file.fgetpos(f);
  dbms_output.put_line('1.position: ' || pos);
  utl_file.fseek(f, NULL, -5);
  pos := utl_file.fgetpos(f);
  dbms_output.put_line('2.position: ' || pos);
  utl_file.fclose(f);
end;
1.position: 15
2.position: 10

GET_LINE

The GET_LINE procedure reads a line of text from a given file up to but not including the end-of-line terminator. A NO_DATA_FOUND exception is thrown when there are no more lines to read.

GET_LINE(<file> FILE_TYPE, <buffer> OUT VARCHAR2)

Parameters

file

Variable of type FILE_TYPE containing the file handle of the opened file.

buffer

Variable to receive a line from the file.

Examples

The following anonymous block reads through and displays the records in file empfile.csv.

DECLARE
    v_empfile       UTL_FILE.FILE_TYPE;
    v_directory     VARCHAR2(50) := 'empdir';
    v_filename      VARCHAR2(20) := 'empfile.csv';
    v_emprec        VARCHAR2(120);
    v_count         INTEGER := 0;
BEGIN
    v_empfile := UTL_FILE.FOPEN(v_directory,v_filename,'r');
    LOOP
        UTL_FILE.GET_LINE(v_empfile,v_emprec);
        DBMS_OUTPUT.PUT_LINE(v_emprec);
        v_count := v_count + 1;
    END LOOP;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            UTL_FILE.FCLOSE(v_empfile);
            DBMS_OUTPUT.PUT_LINE('End of file ' || v_filename || ' - ' ||
                v_count || ' records retrieved');
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
            DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
END;

7369,SMITH,CLERK,7902,17-DEC-80 00:00:00,800.00,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81 00:00:00,1600.00,300.00,30
7521,WARD,SALESMAN,7698,22-FEB-81 00:00:00,1250.00,500.00,30
7566,JONES,MANAGER,7839,02-APR-81 00:00:00,2975.00,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81 00:00:00,1250.00,1400.00,30
7698,BLAKE,MANAGER,7839,01-MAY-81 00:00:00,2850.00,,30
7782,CLARK,MANAGER,7839,09-JUN-81 00:00:00,2450.00,,10
7788,SCOTT,ANALYST,7566,19-APR-87 00:00:00,3000.00,,20
7839,KING,PRESIDENT,,17-NOV-81 00:00:00,5000.00,,10
7844,TURNER,SALESMAN,7698,08-SEP-81 00:00:00,1500.00,0.00,30
7876,ADAMS,CLERK,7788,23-MAY-87 00:00:00,1100.00,,20
7900,JAMES,CLERK,7698,03-DEC-81 00:00:00,950.00,,30
7902,FORD,ANALYST,7566,03-DEC-81 00:00:00,3000.00,,20
7934,MILLER,CLERK,7782,23-JAN-82 00:00:00,1300.00,,10
End of file empfile.csv - 14 records retrieved

GET_RAW

The GET_RAW procedure reads a RAW string value from a file, keeps those into read buffer, and adjusts the file pointer accordingly by the number of bytes read. GET_RAW ignores the end-of-file terminator. INVALID_FILEHANDLE, INVALID_OPERATION, and READ_ERROR exceptions are thrown when there are no more lines to read.

GET_RAW(<file> FILE_TYPE, <buffer> OUT BYTEA [, <len> INTEGER ])

Parameters

file

Variable of type FILE_TYPE containing the file handle of the opened file.

buffer

Assign RAW data from the file to the read buffer.

len

The number of bytes read from a file. Default is NULL. If NULL, len tries to read a maximum of 32767 RAW bytes.

Examples

This example attempts to read a RAW string value from the file.

CREATE DIRECTORY empdir  AS '/TMP/EMPDIR';

CREATE or REPLACE FUNCTION read_bin_file() RETURN void AS

DECLARE
    v_tempfile      UTL_FILE.FILE_TYPE;
    v_filename      VARCHAR2(20) := 'sample.png';
    v_temprec       BYTEA;
    v_count         INTEGER := 0;
BEGIN
    v_tempfile := UTL_FILE.FOPEN('empdir', v_filename, 'rb');
    UTL_FILE.GET_RAW(v_tempfile,v_temprec);
    INSERT INTO emp VALUES (1, v_temprec);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
       RAISE NOTICE 'Finish % ', SQLERRM;
       UTL_FILE.FCLOSE(v_tempfile);
END;

edb=# SELECT read_bin_file();

Output

 read_bin_file 
---------------
 (1 row)

IS_OPEN

The IS_OPEN function determines whether a file is open.

<status> BOOLEAN IS_OPEN(<file> FILE_TYPE)

Parameters

file

Variable of type FILE_TYPE containing the file handle of the file to test.

status

TRUE if the file is open, FALSE otherwise.

NEW_LINE

The NEW_LINE procedure writes an end-of-line character sequence in the file.

NEW_LINE(<file> FILE_TYPE [, <lines> INTEGER ])

Parameters

file

Variable of type FILE_TYPE containing the file handle of the file to which to write end-of-line character sequences.

lines

Number of end-of-line character sequences to write. The default is 1.

Examples

This example writes a file containing a double-spaced list of employee records.

DECLARE
    v_empfile       UTL_FILE.FILE_TYPE;
    v_directory     VARCHAR2(50) := 'empdir';
    v_filename      VARCHAR2(20) := 'empfile.csv';
    CURSOR emp_cur IS SELECT * FROM emp ORDER BY empno;
BEGIN
    v_empfile := UTL_FILE.FOPEN(v_directory,v_filename,'w');
    FOR i IN emp_cur LOOP
        UTL_FILE.PUT(v_empfile,i.empno);
        UTL_FILE.PUT(v_empfile,',');
        UTL_FILE.PUT(v_empfile,i.ename);
        UTL_FILE.PUT(v_empfile,',');
        UTL_FILE.PUT(v_empfile,i.job);
        UTL_FILE.PUT(v_empfile,',');
        UTL_FILE.PUT(v_empfile,i.mgr);
        UTL_FILE.PUT(v_empfile,',');
        UTL_FILE.PUT(v_empfile,i.hiredate);
        UTL_FILE.PUT(v_empfile,',');
        UTL_FILE.PUT(v_empfile,i.sal);
        UTL_FILE.PUT(v_empfile,',');
        UTL_FILE.PUT(v_empfile,i.comm);
        UTL_FILE.PUT(v_empfile,',');
        UTL_FILE.PUT(v_empfile,i.deptno);
        UTL_FILE.NEW_LINE(v_empfile,2);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Created file: ' || v_filename);
    UTL_FILE.FCLOSE(v_empfile);
END;

Created file: empfile.csv

This file is then displayed:

C:\TEMP\EMPDIR>TYPE empfile.csv

7369,SMITH,CLERK,7902,17-DEC-80 00:00:00,800.00,,20

7499,ALLEN,SALESMAN,7698,20-FEB-81 00:00:00,1600.00,300.00,30

7521,WARD,SALESMAN,7698,22-FEB-81 00:00:00,1250.00,500.00,30

7566,JONES,MANAGER,7839,02-APR-81 00:00:00,2975.00,,20

7654,MARTIN,SALESMAN,7698,28-SEP-81 00:00:00,1250.00,1400.00,30

7698,BLAKE,MANAGER,7839,01-MAY-81 00:00:00,2850.00,,30

7782,CLARK,MANAGER,7839,09-JUN-81 00:00:00,2450.00,,10

7788,SCOTT,ANALYST,7566,19-APR-87 00:00:00,3000.00,,20

7839,KING,PRESIDENT,,17-NOV-81 00:00:00,5000.00,,10

7844,TURNER,SALESMAN,7698,08-SEP-81 00:00:00,1500.00,0.00,30

7876,ADAMS,CLERK,7788,23-MAY-87 00:00:00,1100.00,,20

7900,JAMES,CLERK,7698,03-DEC-81 00:00:00,950.00,,30

7902,FORD,ANALYST,7566,03-DEC-81 00:00:00,3000.00,,20

7934,MILLER,CLERK,7782,23-JAN-82 00:00:00,1300.00,,10

PUT

The PUT procedure writes a string to the given file. No end-of-line character sequence is written at the end of the string. Use the NEW_LINE procedure to add an end-of-line character sequence.

PUT(<file> FILE_TYPE, <buffer> { DATE | NUMBER | TIMESTAMP | VARCHAR2 })

Parameters

file

Variable of type FILE_TYPE containing the file handle of the file to which to write the given string.

buffer

Text to write to the specified file.

Examples

This example uses the PUT procedure to create a comma-delimited file of employees from the emp table.

DECLARE
    v_empfile       UTL_FILE.FILE_TYPE;
    v_directory     VARCHAR2(50) := 'empdir';
    v_filename      VARCHAR2(20) := 'empfile.csv';
    CURSOR emp_cur IS SELECT * FROM emp ORDER BY empno;
BEGIN
    v_empfile := UTL_FILE.FOPEN(v_directory,v_filename,'w');
    FOR i IN emp_cur LOOP
        UTL_FILE.PUT(v_empfile,i.empno);
        UTL_FILE.PUT(v_empfile,',');
        UTL_FILE.PUT(v_empfile,i.ename);
        UTL_FILE.PUT(v_empfile,',');
        UTL_FILE.PUT(v_empfile,i.job);
        UTL_FILE.PUT(v_empfile,',');
        UTL_FILE.PUT(v_empfile,i.mgr);
        UTL_FILE.PUT(v_empfile,',');
        UTL_FILE.PUT(v_empfile,i.hiredate);
        UTL_FILE.PUT(v_empfile,',');
        UTL_FILE.PUT(v_empfile,i.sal);
        UTL_FILE.PUT(v_empfile,',');
        UTL_FILE.PUT(v_empfile,i.comm);
        UTL_FILE.PUT(v_empfile,',');
        UTL_FILE.PUT(v_empfile,i.deptno);
        UTL_FILE.NEW_LINE(v_empfile);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Created file: ' || v_filename);
    UTL_FILE.FCLOSE(v_empfile);
END;

Created file: empfile.csv

The following are the contents of empfile.csv:

C:\TEMP\EMPDIR>TYPE empfile.csv

7369,SMITH,CLERK,7902,17-DEC-80 00:00:00,800.00,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81 00:00:00,1600.00,300.00,30
7521,WARD,SALESMAN,7698,22-FEB-81 00:00:00,1250.00,500.00,30
7566,JONES,MANAGER,7839,02-APR-81 00:00:00,2975.00,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81 00:00:00,1250.00,1400.00,30
7698,BLAKE,MANAGER,7839,01-MAY-81 00:00:00,2850.00,,30
7782,CLARK,MANAGER,7839,09-JUN-81 00:00:00,2450.00,,10
7788,SCOTT,ANALYST,7566,19-APR-87 00:00:00,3000.00,,20
7839,KING,PRESIDENT,,17-NOV-81 00:00:00,5000.00,,10
7844,TURNER,SALESMAN,7698,08-SEP-81 00:00:00,1500.00,0.00,30
7876,ADAMS,CLERK,7788,23-MAY-87 00:00:00,1100.00,,20
7900,JAMES,CLERK,7698,03-DEC-81 00:00:00,950.00,,30
7902,FORD,ANALYST,7566,03-DEC-81 00:00:00,3000.00,,20
7934,MILLER,CLERK,7782,23-JAN-82 00:00:00,1300.00,,10

PUT_LINE

The PUT_LINE procedure writes a single line to a file, including an end-of-line character sequence.

PUT_LINE(<file> FILE_TYPE, <buffer> { DATE | NUMBER | TIMESTAMP | VARCHAR2 })

Parameters

file

Variable of type FILE_TYPE containing the file handle of the file to which to write the line.

buffer

Text to write to the file.

Examples

This example uses the PUT_LINE procedure to create a comma-delimited file of employees from the emp table.

DECLARE
    v_empfile       UTL_FILE.FILE_TYPE;
    v_directory     VARCHAR2(50) := 'empdir';
    v_filename      VARCHAR2(20) := 'empfile.csv';
    v_emprec        VARCHAR2(120);
    CURSOR emp_cur IS SELECT * FROM emp ORDER BY empno;
BEGIN
    v_empfile := UTL_FILE.FOPEN(v_directory,v_filename,'w');
    FOR i IN emp_cur LOOP
        v_emprec := i.empno || ',' || i.ename || ',' || i.job || ',' ||
            NVL(LTRIM(TO_CHAR(i.mgr,'9999')),'') || ',' || i.hiredate ||
            ',' || i.sal || ',' ||
            NVL(LTRIM(TO_CHAR(i.comm,'9990.99')),'') || ',' || i.deptno;
        UTL_FILE.PUT_LINE(v_empfile,v_emprec);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Created file: ' || v_filename);
    UTL_FILE.FCLOSE(v_empfile);
END;

The following are the contents of empfile.csv:

C:\TEMP\EMPDIR>TYPE empfile.csv

7369,SMITH,CLERK,7902,17-DEC-80 00:00:00,800.00,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81 00:00:00,1600.00,300.00,30
7521,WARD,SALESMAN,7698,22-FEB-81 00:00:00,1250.00,500.00,30
7566,JONES,MANAGER,7839,02-APR-81 00:00:00,2975.00,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81 00:00:00,1250.00,1400.00,30
7698,BLAKE,MANAGER,7839,01-MAY-81 00:00:00,2850.00,,30
7782,CLARK,MANAGER,7839,09-JUN-81 00:00:00,2450.00,,10
7788,SCOTT,ANALYST,7566,19-APR-87 00:00:00,3000.00,,20
7839,KING,PRESIDENT,,17-NOV-81 00:00:00,5000.00,,10
7844,TURNER,SALESMAN,7698,08-SEP-81 00:00:00,1500.00,0.00,30
7876,ADAMS,CLERK,7788,23-MAY-87 00:00:00,1100.00,,20
7900,JAMES,CLERK,7698,03-DEC-81 00:00:00,950.00,,30
7902,FORD,ANALYST,7566,03-DEC-81 00:00:00,3000.00,,20
7934,MILLER,CLERK,7782,23-JAN-82 00:00:00,1300.00,,10

PUTF

The PUTF procedure writes a formatted string to a file.

PUTF(<file> FILE_TYPE, <format> VARCHAR2 [, <arg1> VARCHAR2] [, ...])

Parameters

file

Variable of type FILE_TYPE containing the file handle of the file to which to write the formatted line.

format

String to format the text written to the file. The special character sequence %s is substituted by the value of arg. The special character sequence \n indicates a new line. In EDB Postgres Advanced Server, specify a new line character with two consecutive backslashes instead of one: \\n. This characteristic isn't compatible with Oracle databases.

arg1

Up to five arguments, arg1,...arg5, to substitute in the format string for each occurrence of %s. The first arg is substituted for the first occurrence of %s, the second arg is substituted for the second occurrence of %s, and so on.

Examples

The following anonymous block produces formatted output containing data from the emp table.

Note

The E literal syntax and double backslashes for the new-line character sequence in the format string aren't compatible with Oracle databases.

DECLARE
    v_empfile       UTL_FILE.FILE_TYPE;
    v_directory     VARCHAR2(50) := 'empdir';
    v_filename      VARCHAR2(20) := 'empfile.csv';
    v_format        VARCHAR2(200);
    CURSOR emp_cur IS SELECT * FROM emp ORDER BY empno;
BEGIN
    v_format := E'%s %s, %s\\nSalary: $%s Commission: $%s\\n\\n';
    v_empfile := UTL_FILE.FOPEN(v_directory,v_filename,'w');
    FOR i IN emp_cur LOOP
        UTL_FILE.PUTF(v_empfile,v_format,i.empno,i.ename,i.job,i.sal,
            NVL(i.comm,0));
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Created file: ' || v_filename);
    UTL_FILE.FCLOSE(v_empfile);
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('SQLERRM: ' || SQLERRM);
        DBMS_OUTPUT.PUT_LINE('SQLCODE: ' || SQLCODE);
END;

Created file: empfile.csv

The following are the contents of empfile.csv:

C:\TEMP\EMPDIR>TYPE empfile.csv
7369 SMITH, CLERK
Salary: $800.00 Commission: $0
7499 ALLEN, SALESMAN
Salary: $1600.00 Commission: $300.00
7521 WARD, SALESMAN
Salary: $1250.00 Commission: $500.00
7566 JONES, MANAGER
Salary: $2975.00 Commission: $0
7654 MARTIN, SALESMAN
Salary: $1250.00 Commission: $1400.00
7698 BLAKE, MANAGER
Salary: $2850.00 Commission: $0
7782 CLARK, MANAGER
Salary: $2450.00 Commission: $0
7788 SCOTT, ANALYST
Salary: $3000.00 Commission: $0
7839 KING, PRESIDENT
Salary: $5000.00 Commission: $0
7844 TURNER, SALESMAN
Salary: $1500.00 Commission: $0.00
7876 ADAMS, CLERK
Salary: $1100.00 Commission: $0
7900 JAMES, CLERK
Salary: $950.00 Commission: $0
7902 FORD, ANALYST
Salary: $3000.00 Commission: $0
7934 MILLER, CLERK
Salary: $1300.00 Commission: $0

PUT_NCHAR

The PUT_NCHAR procedure writes buffer to the given file and converts data to Unicode. PUT doesn't write an end-of-line character sequence.

PUT_NCHAR(<file> FILE_TYPE, <buffer> NVARCHAR2)

Parameters

file

Variable of type FILE_TYPE containing the file handle of the file to which to write the line.

buffer

Variable to write RAW data to the output buffer.

Examples

This example writes buffer to the file empfile.csv and converts data to Unicode:

declare
    f utl_file.file_type;
    t text;
    v_empfile       UTL_FILE.FILE_TYPE;
    v_directory     VARCHAR2(50) := 'empdir';
    v_filename      VARCHAR2(20) := 'empfile1.csv';
    txt1            text;
begin
  f := utl_file.fopen_nchar(v_directory, v_filename, 'w');
  utl_file.put_nchar(f, 'Hello - 1'::text);
  utl_file.put_nchar(f, 100::numeric);
  utl_file.put_nchar(f, '2006-08-13 12:34:56'::timestamp);
  utl_file.put_nchar(f, '2001-12-27 04:05:06.789-08'::pg_catalog.date);
  utl_file.put_nchar(f, '2001-12-27 04:05:06.789-08'::time);
  utl_file.fclose(f);

  f := utl_file.fopen_nchar(v_directory, v_filename, 'r');
  loop
    utl_file.get_line_nchar(f, txt1);
    raise notice '%', txt1;
  end loop;
exception
  when no_data_found then
    raise notice 'finish % ', sqlerrm;
    utl_file.fclose(f);

end;
NOTICE:  Hello - 110013-AUG-06 12:34:5627-DEC-01 00:00:00 +00:0004:05:06.789
NOTICE:  finish no data found

PUT_RAW

The PUT_RAW procedure accepts a RAW data value and writes those values to the output buffer. INVALID_FILEHANDLE, INVALID_OPERATION, WRITE_ERROR, and VALUE_ERROR exceptions are thrown when there are no more lines to read.

PUT_RAW(<file> FILE_TYPE, <buffer> BYTEA [, <autoflush> BOOLEAN ])

Parameters

file

Variable of type FILE_TYPE containing the file handle of the file to which to write the line.

buffer

Variable to write RAW data to the output buffer.

autoflush

If TRUE, performs a flush after writing the value to the output buffer. By default, autoflush is FALSE.

Examples

This example writes the RAW data value from the emp table.

CREATE or REPLACE FUNCTION write_bin_file() RETURN void AS

DECLARE
    v_tempfile      UTL_FILE.FILE_TYPE;
    v_filename      VARCHAR2(20) := 'sample.png';
    v_temprec       BYTEA;
BEGIN
SELECT imagerawdata INTO v_temprec from emp;
    v_tempfile := UTL_FILE.FOPEN('empdir', v_filename, 'wb');
    UTL_FILE.PUT_RAW(v_tempfile,v_temprec, TRUE);
    UTL_FILE.FCLOSE(v_tempfile);
END;

edb=# SELECT write_bin_file();

Output

 write_bin_file 
--------------- 
(1 row)

← Prev

UTL_ENCODE

UTL_HTTP

UTL_FILE v16

UTL_FILE exception codes

Setting file permissions with utl_file.umask

Note

Example

FCLOSE

Parameters

FCLOSE_ALL

FCOPY

Parameters

Examples

FFLUSH

Parameters

Examples

FGETATTR

Parameters

Examples

FGETPOS

Parameters

Examples

FOPEN

Parameters

FOPEN_NCHAR

Parameters

Examples

FREMOVE

Parameters

Examples

FRENAME

Parameters

Examples

FSEEK

Parameters

Examples

GET_LINE

Parameters

Examples

GET_RAW

Parameters

Examples

IS_OPEN

Parameters

NEW_LINE

Parameters

Examples

PUT

Parameters

Examples

PUT_LINE

Parameters

Examples

PUTF

Parameters

Examples

Note

PUT_NCHAR

Parameters

Examples

PUT_RAW

Parameters

Examples

← Prev

Next →