PL-SQL Program to load data into csv file

1. Creating Directory


CREATE OR REPLACE DIRECTORY EXTRACT_DIR AS 'C:\Users\bhavesh1.lakhani\Desktop\Extract';

2. PL/SQL Procedure


CREATE OR REPLACE PROCEDURE EMPLOYEE_CSV AS
CURSOR c_data IS
SELECT first_name,
last_name,
job_id,
employee_id,
salary
FROM employees;
v_file UTL_FILE.FILE_TYPE;
BEGIN
v_file := UTL_FILE.FOPEN(location => 'EXTRACT_DIRECTORY',
filename => 'employee.csv',
open_mode => 'w',
max_linesize => 10000);
FOR cur_rec IN c_data LOOP
UTL_FILE.PUT_LINE(v_file,
cur_rec.first_name || ',' ||
cur_rec.last_name || ',' ||
cur_rec.job_id || ',' ||
cur_rec.salary);
END LOOP;
UTL_FILE.FCLOSE(v_file);
EXCEPTION
WHEN OTHERS THEN
UTL_FILE.FCLOSE(v_file);
RAISE;
END;
/

3. Calling Procedure


EXEC EMPLOYEE_CSV;

Procedure will create Employee.csv file and it will load data into file.

You can see loaded data

Leave a Reply

Your email address will not be published. Required fields are marked *