How to read Files from Oracle Directory using PL/SQL?
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 |