Write Text Files from Oracle PL/SQL with UTL_FILE Example

calendar May 2, 2026 / · 5 min read · Oracle Database PL/SQL UTL_FILE File I/O Oracle DBA Database Programming Directory Object Stored Procedures  ·
Share on: linkedin copy

Write Text Files from Oracle PL/SQL with UTL_FILE Example

Purpose

UTL_FILE is the built-in Oracle PL/SQL package for reading and writing operating-system files from inside the database. The procedure below — taken straight from the shutdownabort.com DBA Quick Guides (Andrew Barry, 2007–2013, preserved via the Wayback Machine as the anchor source for this post) — opens a file in write mode, writes one line, and closes the handle. It is the smallest possible "hello world" for UTL_FILE and the right starting point for any DBA who needs to spool data to disk from PL/SQL.

Code

 1create or replace procedure andy_file_test (
 2  path     in varchar2,
 3  filename in varchar2,
 4  text     in varchar2
 5)
 6is
 7  output_file utl_file.file_type;
 8begin
 9  output_file := utl_file.fopen(path, filename, 'W');
10  utl_file.put_line(output_file, text);
11  utl_file.fclose(output_file);
12end;
13/
14
15execute andy_file_test('/home/oracle/andy/file_test', 'andy.txt', 'hello world');

Breakdown of Code

The procedure has three moving parts and three calls.

  • utl_file.file_type — a record type provided by the package. It holds the file handle that the open call returns. Every read or write call needs this handle.
  • utl_file.fopen(path, filename, 'W') — opens the file. The first argument is either a directory path string (legacy behavior, requires the deprecated UTL_FILE_DIR init parameter) or the name of a DIRECTORY object (modern, secure behavior). The third argument is the open mode: 'W' for write, 'A' for append, 'R' for read, 'RB'/'WB'/'AB' for binary variants.
  • utl_file.put_line(output_file, text) — writes one line of text to the file and adds the platform-appropriate line terminator. Use put for no terminator, new_line to write only the terminator.
  • utl_file.fclose(output_file) — closes the file handle and flushes any buffered output to disk.

The EXECUTE call at the bottom invokes the procedure with three string arguments: the directory, the filename, and the text to write. The end result is /home/oracle/andy/file_test/andy.txt containing the single line hello world.

How It Works

In modern Oracle (11g and later) the first argument of FOPEN is the name of a DIRECTORY database object, not the literal OS path. A DBA creates the DIRECTORY object once with CREATE DIRECTORY and grants READ, WRITE on it to whichever schema needs file access. The schema then passes the DIRECTORY name (uppercase, as Oracle stores identifiers) to FOPEN.

1-- One-time DBA setup (as SYS or a privileged user)
2create or replace directory andy_dir as '/home/oracle/andy/file_test';
3grant read, write on directory andy_dir to my_app_user;
4
5-- Procedure call from the application schema
6execute andy_file_test('ANDY_DIR', 'andy.txt', 'hello world');

The DIRECTORY-object pattern replaces the old UTL_FILE_DIR=* init parameter, which has been deprecated since 10gR2 and removed entirely in 18c. Do not use the old approach on any current database — it bypasses the privilege model and is a known security hole.

The Oracle process that runs the PL/SQL needs OS-level read or write permission on the underlying directory. On UNIX this means the oracle OS user (or whichever user runs the dedicated server process or the listener) must own the path or be in a group that can read or write it.

Key Points

  • UTL_FILE is owned by SYS — it is part of the seeded database; no install step is required. EXECUTE privilege on UTL_FILE is granted to PUBLIC by default.
  • Use DIRECTORY objects, not literal paths — secure, audited, and the only supported pattern in 18c+.
  • FOPEN modes are case-sensitive — pass uppercase 'W', 'A', 'R'. Lowercase silently fails or behaves unexpectedly on some versions.
  • Always close the handle — wrap the body in BEGIN ... EXCEPTION WHEN OTHERS THEN utl_file.fclose(output_file); RAISE; END; so a half-written file does not leak a handle on error.
  • Line length matters — the optional 4th FOPEN parameter sets the maximum line size (1 to 32767, default 1024). Lines longer than the limit raise UTL_FILE.WRITE_ERROR.

Common Exceptions

UTL_FILE raises a small, named exception list. Catch them by name in your code, not by OTHERS.

 1exception
 2  when utl_file.invalid_path     then ...; -- DIRECTORY object does not exist or is not granted
 3  when utl_file.invalid_mode     then ...; -- bad open-mode string
 4  when utl_file.invalid_filehandle then ...; -- handle was never opened
 5  when utl_file.invalid_operation  then ...; -- file cannot be opened or operated on
 6  when utl_file.read_error       then ...;
 7  when utl_file.write_error      then ...;
 8  when utl_file.internal_error   then ...;
 9  when utl_file.access_denied    then ...; -- OS-level permission denied
10when no_data_found               then ...; -- end of file on read

The most common production failure is UTL_FILE.INVALID_PATH — almost always because the application schema does not have READ/WRITE on the DIRECTORY object, or the OS-level path does not exist or is not writeable by the Oracle OS user.

Important Considerations

UTL_FILE writes to the database server's filesystem, not the client's. A developer running the procedure from SQL Developer on a laptop will not see the file on the laptop — the file lands on the host where the Oracle instance is running.

For very large files or high-throughput logging, consider Oracle external tables (for read) or the Java-based DBMS_LOB + BFILE API (for binary streams). UTL_FILE is fine for batch exports, audit logs, and config dumps; it is not the right tool for a million-row CSV streamed in real time.

The shutdownabort.com original (cited above) used the older path-as-string form. The procedure body still works; the only modern adjustment is the DIRECTORY-object setup shown above.

References

Posts in this series