DEV Community

mrcaption49
mrcaption49

Posted on

UTL_FILE – Practical Guide | mrcaption49

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) );
Enter fullscreen mode Exit fullscreen mode

💡 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'));
Enter fullscreen mode Exit fullscreen mode

📌 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;
Enter fullscreen mode Exit fullscreen mode

✅ 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;
Enter fullscreen mode Exit fullscreen mode

🛡️ 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;
Enter fullscreen mode Exit fullscreen mode

💬 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;
Enter fullscreen mode Exit fullscreen mode

After execution, you’ll find a file like:

EMPLOYEE_EXPORT_20250720_1050.csv
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

✅ 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

Top comments (0)