Reading File from ORACLE Directory

How to read Files from Oracle Directory using PL/SQL

File Structure

ACCOUNTING
3 Records
RESEARCH
5 Records
SALES
6 Records
OPERATIONS
0 Records

How to generate files into Oracle Directory?

DECLARE
   l_file   UTL_FILE.file_type;
   l_read   VARCHAR2 (32767);
BEGIN
   l_file := UTL_FILE.fopen ('MY_DIR', 'Summary.txt', 'R');

   LOOP
      BEGIN
         UTL_FILE.get_line (l_file, l_read);
         DBMS_OUTPUT.put_line (l_read);
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            EXIT;
      END;
   END LOOP;

   IF UTL_FILE.is_open (l_file)
   THEN
      DBMS_OUTPUT.put_line ('File is Open');
   END IF;

   UTL_FILE.fclose (l_file);
END;
/

Output

ACCOUNTING
3 Records
RESEARCH
5 Records
SALES
6 Records
OPERATIONS
0 Records
File is Open


File Structure (RESEARCH.txt)

7369,SMITH,CLERK,7902,17-DEC-80,800,500,20
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20
7876,ADAMS,CLERK,7788,23-MAY-87,1100,,20
7902,FORD,ANALYST,7566,03-DEC-81,3000,,20

With delimiter (,)

DECLARE
   l_file   UTL_FILE.file_type;
   l_read   VARCHAR2 (32767);
BEGIN
   l_file := UTL_FILE.fopen ('MY_DIR', 'RESEARCH.txt', 'R');
   LOOP
      BEGIN
         UTL_FILE.get_line (l_file, l_read);
         FOR i IN (SELECT * FROM TABLE (apex_string.split (l_read, ',')))
         LOOP
            DBMS_OUTPUT.put_line (i.COLUMN_VALUE);
         END LOOP;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            EXIT;
      END;
   END LOOP;
   UTL_FILE.fclose (l_file);
END;
/ 

Output

7369
SMITH
CLERK
7902
17-DEC-80
800
500
20
7566
JONES
MANAGER
7839
02-APR-81
2975
20
7788
SCOTT
ANALYST
7566
19-APR-87
3000
20
7876
ADAMS
CLERK
7788
23-MAY-87
1100
20
7902
FORD
ANALYST
7566
03-DEC-81
3000
20