Reading File From Oracle Directory

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

Preview

File Structure

ACCOUNTING RESEARCH SALES OPERATIONS
3 Records 5 Records 6 Records 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 RESEARCH SALES OPERATIONS
3 Records 5 Records 6 Records 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

COL1 COL2 COL3 COL4 COL5
7369 7566 7788 7876 7902
SMITH JONES SCOTT ADAMS FORD
CLERK MANAGER ANALYST CLERK ANALYST
7902 7839 7566 7788 7566
17-Dec-80 2-Apr-81 19-Apr-87 23-May-87 3-Dec-81
800 2975 3000 1100 3000
20 20 20 20 20

comments powered by Disqus