Reading File From Oracle Directory

How to read Files from Oracle Directory using PL/SQL?

File Structure

ACCOUNTINGRESEARCHSALESOPERATIONS
3 Records5 Records6 Records0 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

ACCOUNTINGRESEARCHSALESOPERATIONS
3 Records5 Records6 Records0 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

COL1COL2COL3COL4COL5
73697566778878767902
SMITHJONESSCOTTADAMSFORD
CLERKMANAGERANALYSTCLERKANALYST
79027839756677887566
17-Dec-802-Apr-8119-Apr-8723-May-873-Dec-81
8002975300011003000
2020202020