UTL_FILE v17

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, for a UTL_FILE function or procedure to process successfully, you need:

  • The EXECUTE privilege on the UTL_FILE package. You need this privilege to invoke any package APIs.

  • READorWRITE` privileges on a directory, depending on the operation being performed, as detailed in the following table.

    OperationRequired privileges
    Open file in read modeREAD
    Create or open file in append or write modeWRITE
    Copy fileREAD on source directory and WRITE on destination directory
    Remove fileWRITE
    Rename fileWRITE on source directory and destination directory

Here is an example of how to grant the WRITE privilege to a user on a directory:

GRANT WRITE ON directory tmp TO user1;

You can use a similar procedure to revoke privileges to a directory.

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/procedureReturn typeDescription
FCLOSE(file IN OUT)n/aCloses the specified file identified by file.
FCLOSE_ALLn/aCloses all open files.
FCOPY(location, filename, dest_dir, dest_file [, start_line] [, end_line ])n/aCopies 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/aForces data in the buffer to be written to disk in the file identified by file.
FGETATTR(location, filename, fexists OUT, file_length OUT, block_size OUT)n/aRetrieves the attributes for a file when its filename and location are given.
FGETPOS(file)INTEGERReturns the relative offset position in the opened file as an integer, in bytes.
FOPEN(location, filename, open_mode [, max_linesize ])FILE_TYPEOpens file filename in the directory identified by location.
FOPEN_NCHAR(location, filename, open_mode [, max_linesize])FILE_TYPEOpens file filename in the directory identified by location to read and/or write Unicode character set data.
FREMOVE(location, filename)n/aRemoves the specified file from the file system.
FRENAME(location, filename, dest_dir, dest_file [, overwrite ])n/aRenames the specified file.
FSEEK(file IN OUT [, absolute_offset IN] [, relative_offset IN])n/aMoves the file pointer within a given file by the number of bytes specified.
GET_LINE(file, buffer OUT [, len ])n/aReads a line of text into the variable buffer from the file identified by file. When specified, [, len ] adjusts the file pointer accordingly by the number of bytes read.
GET_LINE_NCHAR(file, buffer OUT [, len ])n/aReads a line of text into the variable buffer from the file identified by file and converts it to Unicode. When specified, [, len ] adjusts the file pointer accordingly by the number of bytes read, ignoring the end-of-file terminator.
GET_NEXTLINE(file, buffer OUT)n/aReads the next line of text from the file identified by file and stores it in the variable buffer.
GET_RAW(file, buffer OUT [, len ])BYTEAReads 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)BOOLEANDetermines whether the given file is open.
NEW_LINE(file [, lines ])n/aWrites an end-of-line character sequence into the file.
PUT(file, buffer)n/aWrites buffer to the given file. PUT doesn't write an end-of-line character sequence.
PUT_LINE(file, buffer [, autoflush ])n/aWrites buffer to the given file. PUT_LINE writes an end-of-line character sequence.
PUT_LINE_NCHAR (file, buffer)n/aWrites buffer in Unicode format to the given file. PUT_LINE_NCHAR writes an end-of-line character sequence.
PUTF(file, format [, arg1 ] [, ...])n/aWrites a formatted string to the given file. You can specify up to five substitution parameters, arg1,...arg5, for replacement in format. PUTF doesn't write an end-of-line character sequence.
PUTF_NCHAR(file, format [, arg1] [, arg2] [...])n/aWrites a formatted string in Unicode to the given file. PUTF_NCHAR doesn't write an end-of-line character sequence. You can specify up to five substitution parameters, arg1,...arg5, for replacement in format.
PUT_NCHAR(file, buffer)n/aWrites buffer to the given file and converts data to Unicode. PUT_NCHAR doesn't write an end-of-line character sequence.
PUT_RAW(file, buffer [, autoflush ])n/aAccepts a RAW data value as input and writes those values to the output buffer. PUT_RAW doesn't write an end-of-line character sequence.

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 codeCondition nameMessage
-29280invalid_pathInvalid directory path
-29281invalid_modeInvalid mode
-29282invalid_filehandleInvalid file ID
-29283invalid_operationInvalid file operation
-29284read_errorFile read error
-29285write_errorFile write error
-29286internal_errorUnspecified SQL error
-29287invalid_maxlinesizeInvalid maximum line size
-29288invalid_filenameInvalid file name
-29289access_deniedAccess to file location denied
-29290invalid_offsetInvalid offset
-29291delete_failedDelete operation failed
-29292rename_failedRename operation failed
-29298invalid_charsetInvalid character set
Note

VALUE_ERROR and NO_DATA_FOUND exceptions can occur, for example, when there are no more lines to read.

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. You must have the CREATE ANY DIRECTORY system privilege to create directories.

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. The default is NULL, which copies text until the last line of the source 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;

Created file: empfile.csv

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, <fexists> OUT BOOLEAN, <file_length> OUT NUMBER, <block_size> OUT 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. Supported modes are: 

- `a` &mdash; append to file.
- `r` &mdash; read from file. 
- `w` &mdash; write to file.
- `ab` &mdash; append to file in binary format.
- `rb` &mdash; read from file in binary format.
- `wb` &mdash; write to file in binary format.

max_linesize

Maximum size of a line in characters. The default is 1024 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 are included when determining if the maximum line size is exceeded. This procedure is compatible with Oracle.

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 returns the FILE_TYPE.

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. Supported modes are: 

- `a` &mdash; append to file.
- `r` &mdash; read from file. 
- `w` &mdash; write to file.
- `ab` &mdash; append to file in binary format.
- `rb` &mdash; read from file in binary format.
- `wb` &mdash; write to file in binary format.

max_linesize

Maximum size of a line in characters. The default is 1024 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 are included when determining if the maximum line size is exceeded. This procedure is compatible with Oracle.

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> IN OUT FILE_TYPE [, <absolute_offset> IN INTEGER] [, <relative_offset> IN 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. The default is NULL.

relative_offset

The relative number of bytes to move the file pointer. The default is NULL.

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. This procedure is compatible with Oracle.

GET_LINE(<file> FILE_TYPE, <buffer> OUT VARCHAR2 [, <len> INTEGER])

Parameters

file

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

buffer

Variable to receive a line from the file.

len

The number of bytes read from a file. Default is NULL. If NULL, len reads a maximum of max_linesize bytes. If no value is provided in max_linesize, it defaults to 1024 bytes.

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_LINE_NCHAR

The GET_LINE_NCHAR procedure reads a line of text into the variable buffer from the file identified by file and converts it to Unicode. When specified, len adjusts the file pointer accordingly by the number of bytes read, ignoring the end-of-file terminator. The default for len is null.

GET_LINE_NCHAR(<file> FILE_TYPE, <buffer> OUT NVARCHAR2 [, <len> INTEGER ])

Parameters

file

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

len

The number of bytes read from a file. Default is NULL. If NULL, len reads a maximum of max_linesize bytes. If no value is provided in max_linesize, it defaults to 1024 bytes.

buffer

Variable to receive a line from the file.

Examples

The following anonymous block reads through the empfile.csv file, and stores data to the buffer, while converting it into Unicode.

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

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_NCHAR(v_directory,v_filename,'r');
	LOOP
		UTL_FILE.GET_LINE_NCHAR(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;

The output displays the requested information:

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_NEXTLINE

The GET_NEXTLINE procedure reads the next line of text from the file identified by file and stores it in the variable buffer. This procedure is deprecated in Oracle.

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

Parameters

file

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

buffer

Variable to receive a line from the file.

Examples

This example reads the next line of text from the empfile.csv and stores it in the buffer.

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

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_NEXTLINE(v_empfile,v_emprec);
		DBMS_OUTPUT.PUT_LINE(v_emprec);
		IF (v_emprec IS NULL) THEN
			EXIT;
		END IF;
		v_count := v_count + 1;
	END LOOP;

	UTL_FILE.FCLOSE(v_empfile);
	DBMS_OUTPUT.PUT_LINE('End of file ' || v_filename || ' - ' ||
		v_count || ' records retrieved');
END;

The output displays the requested information:

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> 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> VARCHAR2 [, <autoflush> BOOLEAN ])

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.

autoflush

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

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;

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_NCHAR

The PUT_LINE_NCHAR procedure writes Unicode data from the buffer to the given file, and writes an end-of-line character sequence.

PUT_LINE_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

Text to write to the file.

Examples

This example creates a comma-delimited file of employees from the emp table. The data is provided in Unicode format.

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

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_NCHAR(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_NCHAR(v_empfile,v_emprec);
    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

PUTF

The PUTF procedure writes a formatted string to a file. PUTF doesn't write an end-of-line character sequence. This procedure is compatible with Oracle.

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.

arg1

Up to five arguments, arg1,...arg5, to substitute in the format string for each occurrence of %s. The default for all arguments is NULL. 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

PUTF_NCHAR

The PUTF_NCHAR procedure writes a formatted string to the given file in Unicode format. PUTF_NCHAR doesn't write an end-of-line character sequence. You can specify up to five substitution parameters, arg1,...arg5, for replacement in format. The default for the substitution parameters is NULL.

PUTF_NCHAR(<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.

arg1

Up to five arguments, arg1,...arg5, to substitute in the format string for each occurrence of %s. The default for all arguments is NULL. 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 in Unicode from the emp table. It then writes the output to the empfile.cvs file.

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

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_NCHAR(v_directory,v_filename,'w');
	FOR i IN emp_cur LOOP
		UTL_FILE.PUTF_NCHAR(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:

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_NCHAR 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. PUT_RAW doesn't write an end-of-line character sequence.

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

UUENCODE

↑ Up

Built-in packages

Next →

UTL_HTTP