UTL_FILE – Practical Guide | mrcaption49

This implementation demonstrates an end-to-end CSV export process in Oracle using UTL_FILE. First, two relational tables—emp_department and employee—are created to simulate HR data. Sample records are inserted into both tables. A procedure export_employee_csv is then defined to build a CLOB string by first adding a CSV header and then appending employee details by joining the tables. This CLOB is passed to a helper procedure generate_csv, which efficiently writes the CLOB content to a physical .csv file using chunked writes via UTL_FILE. The chunking ensures large data volumes are handled safely. A dedicated Oracle Directory (NGCSUTL) is created and granted access, pointing to an OS path for file export. When export_employee_csv is executed, it generates a timestamped CSV file at the designated location. This modular approach cleanly separates data preparation from file I/O. The solution is production-ready, handles exceptions gracefully, and ensures clean file generation.

✅ End-to-End CSV File Generation Using UTL_FILE – Practical Guide

This is a complete and production-like workflow that shows how to:

Create data tables 💾
Populate them with sample data 🧪
Convert that data into CSV format using a CLOB 🧱
Export the content into a .csv file via UTL_FILE ✨

Let’s go step by step.

🧱 Step 1: Define the Base Tables

You start by creating two related tables:

emp_department – Holds department details.
employee – Holds employee details and references the department using a foreign key.

CREATE TABLE emp_department (
  dept_id   NUMBER PRIMARY KEY,
  dept_name VARCHAR2(50)
);

CREATE TABLE employee (
  emp_id     NUMBER PRIMARY KEY,
  emp_name   VARCHAR2(100),
  email      VARCHAR2(100),
  dept_id    NUMBER,
  salary     NUMBER(10,2),
  doj        DATE,
  CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES emp_department(dept_id)
);

💡 This setup simulates a mini HR system.

🧪 Step 2: Insert Sample Data

Now insert data into the department and employee tables.

INSERT INTO emp_department VALUES (1, 'Finance');
INSERT INTO emp_department VALUES (2, 'HR');
INSERT INTO emp_department VALUES (3, 'Engineering');

INSERT INTO employee VALUES (101, 'Alice Thomas', 'alice@example.com', 1, 70000, TO_DATE('2021-05-10','YYYY-MM-DD'));
INSERT INTO employee VALUES (102, 'Bob Martin', 'bob@example.com', 2, 60000, TO_DATE('2020-03-15','YYYY-MM-DD'));
INSERT INTO employee VALUES (103, 'Carol Evans', 'carol@example.com', 3, 95000, TO_DATE('2019-11-01','YYYY-MM-DD'));

📌 The data is now ready to be transformed and exported.

📤 Step 3: Create the CSV Export Procedure (export_employee_csv)

This procedure does 3 main things:

Builds a CSV header line.
Loops through all employees and appends a line for each one.
Calls generate_csv to write the CLOB to a .csv file.

CREATE OR REPLACE PROCEDURE export_employee_csv IS
  v_file_name   VARCHAR2(100);
  v_emp_data    CLOB := EMPTY_CLOB();
BEGIN
  v_file_name := 'EMPLOYEE_EXPORT_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MI') || '.csv';

  -- Header line
  v_emp_data := 'EMP_ID,EMP_NAME,EMAIL,DEPARTMENT,SALARY,DATE_OF_JOINING' || CHR(10);

  -- Data lines
  FOR rec IN (
    SELECT e.emp_id, e.emp_name, e.email, d.dept_name, e.salary,
           TO_CHAR(e.doj, 'YYYY-MM-DD') AS doj
    FROM employee e
    JOIN emp_department d ON e.dept_id = d.dept_id
  ) LOOP
    v_emp_data := v_emp_data || TO_CLOB(
      rec.emp_id || ',' || rec.emp_name || ',' || rec.email || ',' ||
      rec.dept_name || ',' || rec.salary || ',' || rec.doj || CHR(10)
    );
  END LOOP;

  -- Generate the CSV file
  generate_csv(v_file_name, 'NGCSUTL', v_emp_data);
END;

✅ This keeps data formatting and file writing cleanly separated.

🧾 Step 4: UTL_FILE-Based CSV Generator (generate_csv)

This utility handles large CLOBs safely, by:

Reading the CLOB in chunks (32,767 characters max),
Writing each chunk line-by-line into the file,
Ensuring the file is closed properly even in case of error.

CREATE OR REPLACE PROCEDURE generate_csv (
  p_fileName   VARCHAR2,
  p_dir_name   VARCHAR2,
  p_clob       CLOB
) IS
  c_chunk_size CONSTANT BINARY_INTEGER := 32767;
  l_file       UTL_FILE.FILE_TYPE;
  l_pos        INTEGER := 1;
  l_clob_len   INTEGER := DBMS_LOB.GETLENGTH(p_clob);
  l_buffer     VARCHAR2(32767);
BEGIN
  l_file := UTL_FILE.FOPEN(p_dir_name, p_fileName, 'W', c_chunk_size);

  WHILE l_pos <= l_clob_len LOOP
    l_buffer := DBMS_LOB.SUBSTR(p_clob, c_chunk_size, l_pos);
    UTL_FILE.PUT_LINE(l_file, l_buffer);
    l_pos := l_pos + LENGTH(l_buffer);
  END LOOP;

  UTL_FILE.FCLOSE(l_file);

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

🛡️ Built-in exception handling prevents partial file corruption.

📂 Step 5: Create the Oracle Directory (if not already done)

Make sure the directory object (NGCSUTL) is defined in Oracle and points to a valid OS path where Oracle has write access.

CREATE OR REPLACE DIRECTORY NGCSUTL AS '/u01/app/oracle/exports';
GRANT READ, WRITE ON DIRECTORY NGCSUTL TO your_user;

💬 This is required only once and is managed by a DBA/admin.

🚀 Step 6: Execute the Procedure

Run it like this:

BEGIN
  export_employee_csv;
END;

After execution, you’ll find a file like:

EMPLOYEE_EXPORT_20250720_1050.csv

in the path: /u01/app/oracle/exports (or your mapped path for NGCSUTL).

🧾 Output Sample

File content:

EMP_ID,EMP_NAME,EMAIL,DEPARTMENT,SALARY,DATE_OF_JOINING
101,Alice Thomas,alice@example.com,Finance,70000,2021-05-10
102,Bob Martin,bob@example.com,HR,60000,2020-03-15
103,Carol Evans,carol@example.com,Engineering,95000,2019-11-01

✅ Summary

Step What You Did Purpose
1️⃣ Created tables (employee, emp_department) Sample dataset setup
2️⃣ Inserted employee and department data Seeded data
3️⃣ Built CLOB line-by-line with data Prepared memory content
4️⃣ Used generate_csv with UTL_FILE Persisted to disk
5️⃣ Executed the export procedure File created as CSV