How to write file to ORACLE Directory using PL/SQL?
- Creating directory
GRANT CREATE ANY DIRECTORY TO apex_19_2;
CREATE OR REPLACE DIRECTORY my_dir AS 'C:\App\MY_DIR';
- Writing File To Directory
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;
- Multiple files
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;
- Multiple files including summary file
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;