Below is the step by step example is given to export data into CSV file in Oracle using PL SQL procedure. In this example, data is being exported from some fields of Emp table of Scott schema to a CSV file using UTL_FILE Oracle package.
Follow the below steps to export data from Oracle Database table to a CSV file.
1. Create a Directory Object in which you will write CSV file.
-- for windows systems create as following change the folder highlighted with your directory
CREATE OR REPLACE DIRECTORY CSVDIR AS 'd:\temp' /
-- for Unix/Linux systems create as following
CREATE OR REPLACE DIRECTORY CSVDIR AS '/temp/' /
Note: Change the folder location to your directory location, e.g., c:\abc or /abc/.
2. Create a Database Procedure in Scott schema or if you are creating in another schema then make sure you have the Emp table in that schema, else you need to change the Cursor and the field references for any other table.
CREATE OR REPLACE PROCEDURE export_to_csv
IS
v_file UTL_FILE.file_type;
v_string VARCHAR2 (4000);
CURSOR c_emp
IS
SELECT empno,
ename,
deptno,
sal,
comm
FROM emp;
BEGIN
v_file :=
UTL_FILE.fopen ('CSVDIR',
'empdata.csv',
'w',
1000);
-- if you do not want heading then remove below two lines
v_string := 'Emp Code, Emp Name, Dept, Salary, Commission';
UTL_FILE.put_line (v_file, v_string);
FOR cur IN c_emp
LOOP
v_string :=
cur.empno
|| ','
|| cur.ename
|| ','
|| cur.deptno
|| ','
|| cur.sal
|| ','
|| cur.comm;
UTL_FILE.put_line (v_file, v_string);
END LOOP;
UTL_FILE.fclose (v_file);
EXCEPTION
WHEN OTHERS
THEN
IF UTL_FILE.is_open (v_file)
THEN
UTL_FILE.fclose (v_file);
END IF;
END;3. Now run the procedure as follows:
BEGIN export_to_csv; END;
You can now check your directory you specified in Create Directory command that the file empdata.csv must exist with the table data. I have created a utility which generates the procedure online try it:
Hi, Oracle database code runs at server-side, hence the directory object would be created at server-side and will refer to directory exists on Oracle database server. Yes Utl_File package is available by default in Oracle database.
Hi. While creating the directory object,will it be mapped to the directory in the user system or mapped to directory where the database is installed(remote system)? Does the UTL_FILE available by default?
if i want to write in the local directory,not server side what i have to do?