Writing File to Oracle Directory

How to write file to ORACLE Directory using PL/SQL?

Creating directory

GRANT CREATE ANY DIRECTORY TO apex_19_2;
CREATE OR REPLACE DIRECTORY my_dir AS 'C:\App\MY_DIR';

Writing File To Directory

DECLARE
l_file UTL_FILE.file_type;
BEGIN
l_file := UTL_FILE.fopen ('MY_DIR', 'First_File.txt', 'W');
UTL_FILE.put_line (l_file, 'This is first File.');
UTL_FILE.fclose (l_file);
END;

DECLARE
l_file UTL_FILE.file_type;
BEGIN
l_file := UTL_FILE.fopen ('MY_DIR', 'First_File.txt', 'W');

FOR l_cnt
IN (SELECT empno
|| ','
|| ename
|| ','
|| job
|| ','
|| mgr
|| ','
|| hiredate
|| ','
|| sal
|| ','
|| comm
|| ','
|| deptno
row_data
FROM emp)
LOOP
UTL_FILE.put_line (l_file, l_cnt.row_data);
END LOOP;

UTL_FILE.fclose (l_file);
END;

Multiple files

DECLARE
l_file UTL_FILE.file_type;

CURSOR c_get_dept
IS
SELECT deptno, dname FROM dept;

TYPE l_dept_rec_type IS RECORD
(
deptno NUMBER,
dname VARCHAR2 (30)
);

TYPE l_dept_tab_type IS TABLE OF l_dept_rec_type;

l_dept_tab l_dept_tab_type;
BEGIN
OPEN c_get_dept;

FETCH c_get_dept BULK COLLECT INTO l_dept_tab;

CLOSE c_get_dept;

IF l_dept_tab.COUNT > 0
THEN
FOR l_cnt_1 IN l_dept_tab.FIRST .. l_dept_tab.LAST
LOOP
l_file :=
UTL_FILE.fopen ('MY_DIR',
l_dept_tab (l_cnt_1).dname || '.txt',
'W');

FOR l_cnt
IN (SELECT empno
|| ','
|| ename
|| ','
|| job
|| ','
|| mgr
|| ','
|| hiredate
|| ','
|| sal
|| ','
|| comm
|| ','
|| deptno
row_data
FROM emp
WHERE deptno = l_dept_tab (l_cnt_1).deptno)
LOOP
UTL_FILE.put_line (l_file, l_cnt.row_data);
END LOOP;

UTL_FILE.fclose (l_file);
END LOOP;
END IF;
END;

Multiple files including summary file

DECLARE
l_file UTL_FILE.file_type;
l_summary_file UTL_FILE.file_type;

CURSOR c_get_dept
IS
SELECT deptno, dname FROM dept;

TYPE l_dept_rec_type IS RECORD
(
deptno NUMBER,
dname VARCHAR2 (30)
);

TYPE l_dept_tab_type IS TABLE OF l_dept_rec_type;

l_dept_tab l_dept_tab_type;

CURSOR c_get_emp (
p_deptno NUMBER)
IS
SELECT empno
|| ','
|| ename
|| ','
|| job
|| ','
|| mgr
|| ','
|| hiredate
|| ','
|| sal
|| ','
|| comm
|| ','
|| deptno
row_data
FROM emp
WHERE deptno = p_deptno;

TYPE l_emp_rec_type IS RECORD (row_data VARCHAR2 (4000));

TYPE l_emp_tab_type IS TABLE OF l_emp_rec_type;

l_emp_tab l_emp_tab_type;
BEGIN
OPEN c_get_dept;

FETCH c_get_dept BULK COLLECT INTO l_dept_tab;

CLOSE c_get_dept;

l_summary_file := UTL_FILE.fopen ('MY_DIR', 'Summary.txt', 'W');

IF l_dept_tab.COUNT > 0
THEN
FOR l_cnt_1 IN l_dept_tab.FIRST .. l_dept_tab.LAST
LOOP
l_file :=
UTL_FILE.fopen ('MY_DIR',
l_dept_tab (l_cnt_1).dname || '.txt',
'W');
UTL_FILE.put_line (l_summary_file, l_dept_tab (l_cnt_1).dname);

OPEN c_get_emp (l_dept_tab (l_cnt_1).deptno);

FETCH c_get_emp BULK COLLECT INTO l_emp_tab;

CLOSE c_get_emp;

UTL_FILE.put_line (l_summary_file, l_emp_tab.COUNT || ' Records');

IF l_emp_tab.COUNT > 0
THEN
FOR l_cnt IN l_emp_tab.FIRST .. l_emp_tab.LAST
LOOP
UTL_FILE.put_line (l_file, l_emp_tab (l_cnt).row_data);
END LOOP;

UTL_FILE.fclose (l_file);
END IF;
END LOOP;
END IF;

UTL_FILE.fclose (l_summary_file);
END;