Learn how to use the UTL_FILE
package to export data from a table to the CSV file in Oracle PL/SQL.
In Oracle, the package UTL_FILE
contains many procedures and functions for writing a text file. Below are the syntax details and the necessary steps to write a file:
Syntax and Steps to Write a File Using UTL_FILE
-- Declare a variable to store file type
n_file UTL_FILE.FILE_TYPE;
-- Open the file in Begin section, it will open the file and return the file handle into the variable n_file
n_file := UTL_FILE.FOPEN('DIR_OBJ', 'YourCSVFileName.csv', 'w', 4000);
-- Write a single or multiple lines
UTL_FILE.PUT_LINE(n_file, 'abc, xyz, xxx');
-- Close the file
UTL_FILE.FCLOSE(n_file);
Basic Example
Declare
n_file Utl_File.File_Type;
Begin
-- The directory object MY_DIR must be exist or create a new one
n_file := Utl_File.Fopen('MY_DIR', 'myfile.csv', 'w', '4000');
Utl_File.Put_Line(n_file, 'First line.');
Utl_File.Put_Line(n_file, 'Second line.');
Utl_File.Put_Line(n_file, 'Third line.');
Utl_File.Fclose(n_file);
End;
As I mentioned in the above example, the directory object MY_DIR
must exist. The directory object in Oracle is a reference to the physical directory on the server. The following is an example of creating a directory object in Oracle:
-- Windows example
CREATE OR REPLACE DIRECTORY CSVDIR AS 'd:\oracle\csvfiles';
-- Linux example
CREATE OR REPLACE DIRECTORY CSVDIR AS '/usr1/oracle/csvfiles';
To learn more about the directory object in Oracle, check this link.
Export Data from a Table to CSV in Oracle Example
The following is an example of a stored procedure in Oracle, which will export the data from the EMP table to a CSV file:
Create Or Replace Procedure exp_emp_data Is
n_file utl_file.file_type;
v_string Varchar2(4000);
-- get the data using cursor
Cursor c_emp Is
Select
empno,
ename,
deptno,
sal,
comm
From
emp;
Begin
n_file := utl_file.fopen('CSVDIR', 'empdata.csv', 'w', 4000);
-- if you do not want heading then remove below two lines
v_string := 'Emp Code, Emp Name, Dept, Salary, Commission';
utl_file.put_line(n_file, v_string);
-- open the cursor and concatenate fields using comma
For cur In c_emp Loop
v_string := cur.empno
|| ','
|| cur.ename
|| ','
|| cur.deptno
|| ','
|| cur.sal
|| ','
|| cur.comm;
-- write each row
utl_file.put_line(n_file, v_string);
End Loop;
-- close the file
utl_file.fclose(n_file);
Exception
When Others Then
-- on error, close the file if open
If utl_file.is_open(n_file) Then
utl_file.fclose(n_file);
End If;
End;
Now your stored procedure has been created, execute it to export the data:
Begin
exp_emp_data;
End;