create or replace PROCEDURE PROC_CREATE_FILES
IS
  V_WRITE_FILE UTL_FILE.FILE_TYPE;
  V_WRITE_LINE VARCHAR2(500);
  
BEGIN
  V_WRITE_FILE := UTL_FILE.FOPEN('MYDIR', 'DEPARTMENTNAME.TXT', 'W');
  FOR EMPREC IN
  (SELECT *
   FROM
    (SELECT DEPARTMENT_ID, DEPARTMENT_NAME, STREET_ADDRESS
    FROM DEPARTMENTS D
    JOIN LOCATIONS L USING (LOCATION_ID)
    ORDER BY DBMS_RANDOM.RANDOM)
  WHERE ROWNUM <= 5)
  LOOP
    V_WRITE_LINE := CHR(9)||CHR(9)||EMPREC.DEPARTMENT_ID||chr(9)||
    EMPREC.DEPARTMENT_NAME||CHR(9)||EMPREC.STREET_ADDRESS;
    UTL_FILE.PUT_LINE(V_WRITE_FILE, V_WRITE_LINE);
    
    BEGIN
      FOR EMPCUR IN
      (SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = EMPREC.DEPARTMENT_ID)
      LOOP
        V_WRITE_LINE := EMPCUR.EMPLOYEE_ID||chr(9)||EMPCUR.FIRST_NAME||chr(9)||
        EMPCUR.LAST_NAME||chr(9)||EMPCUR.SALARY||chr(9)|| EMPCUR.HIRE_DATE||
        chr(9)||EMPCUR.MANAGER_ID||CHR(9)||EMPCUR.DEPARTMENT_ID;
        
        UTL_FILE.PUT_LINE(V_WRITE_FILE, V_WRITE_LINE);
      END LOOP;
    END;
  END LOOP;
  UTL_FILE.FCLOSE(V_WRITE_FILE);
END;