Writing File to Oracle Directory

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

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;


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;


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;