I am writing a PL/SQL code to take data from select query through cursor & write the output into a CSV file.
PL/SQL code is like below.
set serverout on size 1000000
set serveroutput on
alter session set nls_date_format='YYYY-MM-DD';
CREATE OR REPLACE DIRECTORY MY_FILE_DIR AS '/usr/tmp2';
DECLARE
file_name varchar2(100) := 'Emp_Data';
file utl_file.file_type;
TYPE emp_rec IS RECORD(
v_empno varchar2(10),
v_last_name varchar2(40)
);
cursor PnJD_cur is
select employee_number, last_name
from apps.zshr_employee_v a
where a.user_person_type='01'
and a.EFFECTIVE_END_DATE='4712-12-31';
BEGIN
IF NOT PnJD_cur%ISOPEN THEN
OPEN PnJD_cur;
ENDIF;
FETCH PnJD_cur INTO emp_rec;
WHILE PnJD_cur%FOUND
LOOP
file := utl_file.fopen(MY_FILE_DIR,file_name||'.csv','w');
utl_file.put(file, emp_rec.v_empno|| ',' ||
emp_rec.v_last_name
);
utl_file.fclose(file);
-- dbms_output.put_line(file||'.csv');
FETCH PnJD_cur INTO emp_rec;
END LOOP;
END;
I was believing that this code will 'charmingly' write into the csv file but it did not. Rather it gave an output as below. Ultimately I had to do CTRL+C to terminate the execution.
Any HELP in this regard is HIGHLY APPRECIATED. Thank you in advance.
SQL> @EMP_Personal_Job_Data_Extract1123
Session altered.
Directory created.
37
38
39
40
41
42
43
44
45
46
47
48
49 50
51
52
53
54
55 ^C
SQL>