UTL_FILE - Read & Write Files from Oracle PL/SQL

UTL_FILE is Oracle’s built-in package for reading and writing operating system files directly from PL/SQL. It enables DBAs and developers to export query results as CSV files, read configuration files, write log files, and exchange data with external applications — all without leaving the database session.

Overview

UTL_FILE requires Oracle DIRECTORY objects to control which filesystem paths are accessible. This design keeps security management inside the database rather than relying on OS-level file permissions alone.

Architecture

PL/SQL Session
     │
     ▼
UTL_FILE Package
     │
     ▼
DIRECTORY Object (e.g., 'EXPORT_DIR' → /data/exports/)
     │
     ▼
OS Filesystem (Oracle OS user must have read/write permission)

Directory Setup

Always create named DIRECTORY objects rather than using raw paths. Raw path access was deprecated in Oracle 9i.

-- Create directory objects (run as DBA or user with CREATE ANY DIRECTORY)
CREATE OR REPLACE DIRECTORY export_dir  AS '/data/oracle/exports';
CREATE OR REPLACE DIRECTORY import_dir  AS '/data/oracle/imports';
CREATE OR REPLACE DIRECTORY log_dir     AS '/data/oracle/logs';
CREATE OR REPLACE DIRECTORY report_dir  AS '/data/oracle/reports';

-- Grant access to application schema
GRANT READ, WRITE ON DIRECTORY export_dir TO app_user;
GRANT READ        ON DIRECTORY import_dir TO app_user;
GRANT READ, WRITE ON DIRECTORY log_dir    TO app_user;
GRANT READ, WRITE ON DIRECTORY report_dir TO app_user;

-- Verify directory objects and grants
SELECT
  d.directory_name,
  d.directory_path,
  p.grantee,
  p.privilege
FROM dba_directories d
LEFT JOIN dba_tab_privs p
  ON p.table_name = d.directory_name
  AND p.owner = 'SYS'
  AND p.type = 'DIRECTORY'
WHERE d.directory_name IN ('EXPORT_DIR', 'IMPORT_DIR', 'LOG_DIR', 'REPORT_DIR')
ORDER BY d.directory_name, p.grantee;

-- Check OS path accessibility (the Oracle process user needs rwx on the path)
-- Run as DBA to verify
SELECT directory_name, directory_path
FROM dba_directories
ORDER BY directory_name;

File Modes

Mode	Description
`'r'`	Read text (line by line)
`'w'`	Write text (overwrite existing)
`'a'`	Append text
`'rb'`	Read binary
`'wb'`	Write binary
`'ab'`	Append binary

Key Procedures and Functions

FOPEN

Opens a file and returns a FILE_TYPE handle used by all subsequent operations.

-- Syntax
UTL_FILE.FOPEN(
  location     IN VARCHAR2,         -- DIRECTORY object name
  filename     IN VARCHAR2,         -- file name (no path)
  open_mode    IN VARCHAR2,         -- 'r', 'w', 'a', 'rb', 'wb', 'ab'
  max_linesize IN BINARY_INTEGER := 1024  -- max bytes per line (text mode)
) RETURN UTL_FILE.FILE_TYPE;

-- Open a file for writing with long lines (max 32767)
DECLARE
  l_file UTL_FILE.FILE_TYPE;
BEGIN
  l_file := UTL_FILE.FOPEN('EXPORT_DIR', 'output.csv', 'w', 32767);
  UTL_FILE.PUT_LINE(l_file, 'col1,col2,col3');
  UTL_FILE.FCLOSE(l_file);
END;
/

FCLOSE and FCLOSE_ALL

Always close file handles explicitly. FCLOSE_ALL closes all open handles for the current session — useful in exception handlers.

-- Close a specific handle
UTL_FILE.FCLOSE(file IN OUT UTL_FILE.FILE_TYPE);

-- Close all open handles in the session
UTL_FILE.FCLOSE_ALL;

PUT_LINE

Writes a line followed by the platform line terminator.

-- Syntax
UTL_FILE.PUT_LINE(
  file      IN UTL_FILE.FILE_TYPE,
  buffer    IN VARCHAR2,
  autoflush IN BOOLEAN := FALSE
);

PUT and NEW_LINE

PUT writes text without a line terminator. NEW_LINE writes one or more line terminators.

-- Build a line piece by piece
DECLARE
  l_file UTL_FILE.FILE_TYPE;
BEGIN
  l_file := UTL_FILE.FOPEN('EXPORT_DIR', 'partial.txt', 'w', 32767);
  UTL_FILE.PUT(l_file, 'Part one ');
  UTL_FILE.PUT(l_file, 'Part two ');
  UTL_FILE.NEW_LINE(l_file);           -- terminates the line
  UTL_FILE.PUT(l_file, 'Line two');
  UTL_FILE.NEW_LINE(l_file, 2);        -- two blank lines
  UTL_FILE.FCLOSE(l_file);
END;
/

GET_LINE

Reads one line from a text file, stripping the line terminator.

-- Syntax
UTL_FILE.GET_LINE(
  file    IN  UTL_FILE.FILE_TYPE,
  buffer  OUT VARCHAR2,
  len     IN  BINARY_INTEGER := NULL  -- max chars to read
);

FFLUSH

Forces buffered data to the OS without closing the file. Useful when another process must read the file while Oracle still has it open.

UTL_FILE.FFLUSH(file IN UTL_FILE.FILE_TYPE);

IS_OPEN

Returns TRUE if a file handle is currently open.

IF UTL_FILE.IS_OPEN(l_file) THEN
  UTL_FILE.FCLOSE(l_file);
END IF;

FGETATTR

Retrieves file attributes without opening the file.

-- Syntax
UTL_FILE.FGETATTR(
  location  IN  VARCHAR2,
  filename  IN  VARCHAR2,
  fexists   OUT BOOLEAN,
  file_length OUT NUMBER,
  block_size  OUT BINARY_INTEGER
);

-- Check if a file exists before processing
DECLARE
  l_exists     BOOLEAN;
  l_length     NUMBER;
  l_blocksize  BINARY_INTEGER;
BEGIN
  UTL_FILE.FGETATTR('IMPORT_DIR', 'feed.csv', l_exists, l_length, l_blocksize);

  IF l_exists THEN
    DBMS_OUTPUT.PUT_LINE('File size: ' || l_length || ' bytes');
    DBMS_OUTPUT.PUT_LINE('Block size: ' || l_blocksize);
  ELSE
    DBMS_OUTPUT.PUT_LINE('File not found.');
  END IF;
END;
/

FRENAME

Renames or moves a file within directories accessible to Oracle.

-- Syntax
UTL_FILE.FRENAME(
  src_location  IN VARCHAR2,
  src_filename  IN VARCHAR2,
  dest_location IN VARCHAR2,
  dest_filename IN VARCHAR2,
  overwrite     IN BOOLEAN := FALSE
);

-- Archive processed file
UTL_FILE.FRENAME('IMPORT_DIR', 'feed.csv',
                 'ARCHIVE_DIR', 'feed_' || TO_CHAR(SYSDATE,'YYYYMMDD') || '.csv',
                 FALSE);

FCOPY

Copies a file, optionally selecting a range of lines.

-- Syntax
UTL_FILE.FCOPY(
  src_location  IN VARCHAR2,
  src_filename  IN VARCHAR2,
  dest_location IN VARCHAR2,
  dest_filename IN VARCHAR2,
  start_line    IN BINARY_INTEGER := NULL,
  end_line      IN BINARY_INTEGER := NULL
);

-- Copy lines 100-200 of a large log file for analysis
UTL_FILE.FCOPY('LOG_DIR', 'app.log', 'EXPORT_DIR', 'excerpt.log', 100, 200);

FREMOVE

Deletes a file. Raises UTL_FILE.DELETE_FAILED if the file does not exist or the Oracle process lacks permission.

BEGIN
  UTL_FILE.FREMOVE('EXPORT_DIR', 'old_report.csv');
  DBMS_OUTPUT.PUT_LINE('File deleted.');
EXCEPTION
  WHEN UTL_FILE.DELETE_FAILED THEN
    DBMS_OUTPUT.PUT_LINE('Delete failed - file may not exist or no permission.');
END;
/

Error Handling

UTL_FILE raises named exceptions rather than generic OTHERS. Always handle them explicitly.

-- Full exception handler template
DECLARE
  l_file   UTL_FILE.FILE_TYPE;
  l_buffer VARCHAR2(32767);
BEGIN
  l_file := UTL_FILE.FOPEN('EXPORT_DIR', 'test.txt', 'r', 32767);
  UTL_FILE.GET_LINE(l_file, l_buffer);
  DBMS_OUTPUT.PUT_LINE(l_buffer);
  UTL_FILE.FCLOSE(l_file);

EXCEPTION
  WHEN UTL_FILE.INVALID_PATH THEN
    DBMS_OUTPUT.PUT_LINE('ERROR: Directory object not found or path not allowed.');
    UTL_FILE.FCLOSE_ALL;
  WHEN UTL_FILE.INVALID_MODE THEN
    DBMS_OUTPUT.PUT_LINE('ERROR: Invalid open mode specified.');
    UTL_FILE.FCLOSE_ALL;
  WHEN UTL_FILE.INVALID_FILEHANDLE THEN
    DBMS_OUTPUT.PUT_LINE('ERROR: File handle is invalid or file not open.');
    UTL_FILE.FCLOSE_ALL;
  WHEN UTL_FILE.INVALID_OPERATION THEN
    DBMS_OUTPUT.PUT_LINE('ERROR: Operation not valid for this open mode (e.g., reading a write-only file).');
    UTL_FILE.FCLOSE_ALL;
  WHEN UTL_FILE.READ_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('ERROR: OS read error.');
    UTL_FILE.FCLOSE_ALL;
  WHEN UTL_FILE.WRITE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('ERROR: OS write error (disk full?).');
    UTL_FILE.FCLOSE_ALL;
  WHEN UTL_FILE.FILE_OPEN THEN
    DBMS_OUTPUT.PUT_LINE('ERROR: File already open.');
    UTL_FILE.FCLOSE_ALL;
  WHEN UTL_FILE.ACCESS_DENIED THEN
    DBMS_OUTPUT.PUT_LINE('ERROR: OS permission denied.');
    UTL_FILE.FCLOSE_ALL;
  WHEN UTL_FILE.NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('INFO: End of file reached.');
    IF UTL_FILE.IS_OPEN(l_file) THEN UTL_FILE.FCLOSE(l_file); END IF;
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
    UTL_FILE.FCLOSE_ALL;
    RAISE;
END;
/

Practical Examples

CSV Export

-- Export a query result as a CSV file
CREATE OR REPLACE PROCEDURE export_employees_csv (
  p_dept_id   IN  NUMBER,
  p_directory IN  VARCHAR2,
  p_filename  IN  VARCHAR2
) AS
  l_file      UTL_FILE.FILE_TYPE;
  l_line      VARCHAR2(32767);
  l_row_count NUMBER := 0;

  -- Enclose fields containing commas or quotes in double-quotes
  FUNCTION csv_field(p_val IN VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    IF p_val IS NULL THEN RETURN ''; END IF;
    IF INSTR(p_val, ',') > 0 OR INSTR(p_val, '"') > 0 THEN
      RETURN '"' || REPLACE(p_val, '"', '""') || '"';
    END IF;
    RETURN p_val;
  END csv_field;

BEGIN
  l_file := UTL_FILE.FOPEN(p_directory, p_filename, 'w', 32767);

  -- Header row
  UTL_FILE.PUT_LINE(l_file,
    'EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,HIRE_DATE,SALARY,DEPARTMENT_ID');

  -- Data rows
  FOR r IN (
    SELECT employee_id, first_name, last_name, email,
           hire_date, salary, department_id
    FROM   employees
    WHERE  department_id = p_dept_id
    ORDER  BY employee_id
  ) LOOP
    l_line :=
      csv_field(TO_CHAR(r.employee_id))                     || ',' ||
      csv_field(r.first_name)                               || ',' ||
      csv_field(r.last_name)                                || ',' ||
      csv_field(r.email)                                    || ',' ||
      csv_field(TO_CHAR(r.hire_date, 'YYYY-MM-DD'))         || ',' ||
      csv_field(TO_CHAR(r.salary, 'FM999999990.00'))        || ',' ||
      csv_field(TO_CHAR(r.department_id));

    UTL_FILE.PUT_LINE(l_file, l_line);
    l_row_count := l_row_count + 1;

    -- Flush every 1000 rows to reduce memory pressure
    IF MOD(l_row_count, 1000) = 0 THEN
      UTL_FILE.FFLUSH(l_file);
    END IF;
  END LOOP;

  UTL_FILE.FCLOSE(l_file);
  DBMS_OUTPUT.PUT_LINE('Exported ' || l_row_count || ' rows to ' || p_filename);

EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.IS_OPEN(l_file) THEN UTL_FILE.FCLOSE(l_file); END IF;
    RAISE;
END export_employees_csv;
/

-- Execute the export
EXEC export_employees_csv(90, 'EXPORT_DIR', 'dept90_employees.csv');

Reading a Configuration File

-- Read key=value pairs from a config file
-- File format:  BATCH_SIZE=500
--               MAX_ERRORS=10
--               LOG_LEVEL=DEBUG

CREATE OR REPLACE PACKAGE config_reader AS
  TYPE config_t IS TABLE OF VARCHAR2(4000) INDEX BY VARCHAR2(100);

  FUNCTION load_config (
    p_directory IN VARCHAR2,
    p_filename  IN VARCHAR2
  ) RETURN config_t;
END config_reader;
/

CREATE OR REPLACE PACKAGE BODY config_reader AS
  FUNCTION load_config (
    p_directory IN VARCHAR2,
    p_filename  IN VARCHAR2
  ) RETURN config_t IS
    l_file    UTL_FILE.FILE_TYPE;
    l_line    VARCHAR2(32767);
    l_key     VARCHAR2(100);
    l_value   VARCHAR2(4000);
    l_eq_pos  INTEGER;
    l_config  config_t;
  BEGIN
    l_file := UTL_FILE.FOPEN(p_directory, p_filename, 'r', 32767);

    LOOP
      BEGIN
        UTL_FILE.GET_LINE(l_file, l_line);
      EXCEPTION
        WHEN NO_DATA_FOUND THEN EXIT;
      END;

      -- Skip blank lines and comment lines starting with #
      l_line := TRIM(l_line);
      CONTINUE WHEN l_line IS NULL OR SUBSTR(l_line, 1, 1) = '#';

      l_eq_pos := INSTR(l_line, '=');
      IF l_eq_pos > 1 THEN
        l_key   := TRIM(UPPER(SUBSTR(l_line, 1, l_eq_pos - 1)));
        l_value := TRIM(SUBSTR(l_line, l_eq_pos + 1));
        l_config(l_key) := l_value;
      END IF;
    END LOOP;

    UTL_FILE.FCLOSE(l_file);
    RETURN l_config;

  EXCEPTION
    WHEN UTL_FILE.INVALID_PATH OR UTL_FILE.NO_DATA_FOUND THEN
      IF UTL_FILE.IS_OPEN(l_file) THEN UTL_FILE.FCLOSE(l_file); END IF;
      RETURN l_config;
    WHEN OTHERS THEN
      IF UTL_FILE.IS_OPEN(l_file) THEN UTL_FILE.FCLOSE(l_file); END IF;
      RAISE;
  END load_config;
END config_reader;
/

-- Usage
DECLARE
  l_cfg     config_reader.config_t;
  l_key     VARCHAR2(100);
BEGIN
  l_cfg := config_reader.load_config('IMPORT_DIR', 'batch.conf');
  l_key := l_cfg.FIRST;
  WHILE l_key IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE(l_key || ' = ' || l_cfg(l_key));
    l_key := l_cfg.NEXT(l_key);
  END LOOP;
END;
/

Line-by-Line Processing with Error Logging

-- Load and process a data file, logging bad rows
CREATE OR REPLACE PROCEDURE process_data_file (
  p_data_dir   IN VARCHAR2,
  p_data_file  IN VARCHAR2,
  p_log_dir    IN VARCHAR2,
  p_log_file   IN VARCHAR2
) AS
  l_data_fh    UTL_FILE.FILE_TYPE;
  l_log_fh     UTL_FILE.FILE_TYPE;
  l_line        VARCHAR2(32767);
  l_line_num    NUMBER := 0;
  l_ok_count    NUMBER := 0;
  l_err_count   NUMBER := 0;
  l_fields      APEX_APPLICATION_GLOBAL.VC_ARR2;  -- or split manually

  PROCEDURE log_error(p_msg IN VARCHAR2) IS
  BEGIN
    UTL_FILE.PUT_LINE(l_log_fh,
      TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS.FF3') ||
      ' | LINE ' || LPAD(l_line_num, 7) || ' | ' || p_msg);
  END;

BEGIN
  l_data_fh := UTL_FILE.FOPEN(p_data_dir, p_data_file, 'r', 32767);
  l_log_fh  := UTL_FILE.FOPEN(p_log_dir,  p_log_file,  'w', 32767);

  UTL_FILE.PUT_LINE(l_log_fh,
    'Processing started: ' || TO_CHAR(SYSTIMESTAMP));
  UTL_FILE.PUT_LINE(l_log_fh, RPAD('-', 80, '-'));

  LOOP
    BEGIN
      UTL_FILE.GET_LINE(l_data_fh, l_line);
    EXCEPTION
      WHEN NO_DATA_FOUND THEN EXIT;
    END;

    l_line_num := l_line_num + 1;
    CONTINUE WHEN l_line_num = 1;  -- Skip header

    -- Attempt to process the row
    BEGIN
      -- Parse comma-separated: emp_id, name, salary
      DECLARE
        l_id     NUMBER  := TO_NUMBER(REGEXP_SUBSTR(l_line, '[^,]+', 1, 1));
        l_name   VARCHAR2(100) := TRIM(REGEXP_SUBSTR(l_line, '[^,]+', 1, 2));
        l_sal    NUMBER  := TO_NUMBER(REGEXP_SUBSTR(l_line, '[^,]+', 1, 3));
      BEGIN
        INSERT INTO employee_staging (emp_id, emp_name, salary)
        VALUES (l_id, l_name, l_sal);
        l_ok_count := l_ok_count + 1;
      END;
    EXCEPTION
      WHEN OTHERS THEN
        log_error('FAILED: ' || SQLERRM || ' | DATA: ' || l_line);
        l_err_count := l_err_count + 1;
    END;

    IF MOD(l_line_num, 500) = 0 THEN
      COMMIT;
      UTL_FILE.FFLUSH(l_log_fh);
    END IF;
  END LOOP;

  COMMIT;

  UTL_FILE.PUT_LINE(l_log_fh, RPAD('-', 80, '-'));
  UTL_FILE.PUT_LINE(l_log_fh, 'Lines processed : ' || (l_line_num - 1));
  UTL_FILE.PUT_LINE(l_log_fh, 'Rows loaded     : ' || l_ok_count);
  UTL_FILE.PUT_LINE(l_log_fh, 'Errors          : ' || l_err_count);
  UTL_FILE.PUT_LINE(l_log_fh,
    'Processing ended: ' || TO_CHAR(SYSTIMESTAMP));

  UTL_FILE.FCLOSE(l_data_fh);
  UTL_FILE.FCLOSE(l_log_fh);

EXCEPTION
  WHEN OTHERS THEN
    UTL_FILE.FCLOSE_ALL;
    ROLLBACK;
    RAISE;
END process_data_file;
/

Best Practices

Security

Never grant CREATE ANY DIRECTORY to application schemas. Let a DBA create directory objects and grant only READ or WRITE as needed.
Verify OS permissions. The Oracle process OS user (typically oracle) must have appropriate permissions on the underlying directory.
Audit directory grants. Query DBA_TAB_PRIVS WHERE TYPE = 'DIRECTORY' regularly to check for over-privileged accounts.
Avoid storing absolute paths in code. Always use directory object names so paths can be changed without recompiling.

Reliability

Always close file handles in exception handlers. An unclosed handle is not released until the session terminates.
Use FFLUSH for long-running jobs so progress is visible to monitoring tools if the job hangs.
Set max_linesize to 32767 when writing CSV or other variable-width output — the default 1024 causes UTL_FILE.WRITE_ERROR for long lines.
Check for file existence with FGETATTR before attempting FOPEN in read mode to produce a clean error message rather than an exception.

Performance

Batch commits during large file loads (every 500–1000 rows) to balance redo log overhead against rollback cost.
Write headers before the loop to avoid an extra file open/close cycle.
Open files with 'a' (append) mode for log files that are shared across multiple job runs.

Required Privileges

Action	Privilege
Create `DIRECTORY` objects	`CREATE ANY DIRECTORY` (DBA only)
Read files	`READ ON DIRECTORY <dir_name>`
Write files	`WRITE ON DIRECTORY <dir_name>`
Execute `UTL_FILE` procedures	Granted to `PUBLIC` by default

DBMS_LOB - Read and write LOB data, including exporting BLOBs to files
Oracle Scheduler - Schedule PL/SQL procedures that use UTL_FILE for automated exports
SQL*Loader - Bulk-load files created by UTL_FILE into Oracle tables

UTL_FILE - Read & Write Files from Oracle PL/SQL

UTL_FILE - Read & Write Files from Oracle PL/SQL

Overview

Architecture

Directory Setup

File Modes

Key Procedures and Functions

FOPEN

FCLOSE and FCLOSE_ALL

PUT_LINE

PUT and NEW_LINE

GET_LINE

FFLUSH

IS_OPEN

FGETATTR

FRENAME

FCOPY

FREMOVE

Error Handling

Practical Examples

CSV Export

Reading a Configuration File

Line-by-Line Processing with Error Logging

Best Practices

Security

Reliability

Performance

Required Privileges

Related Topics