emp_sal

CREATE OR REPLACE PROCEDURE EMP_REP_<userid>
IS
-- Variables for file handling
v_file_handle UTL_FILE.FILE_TYPE;
v_line VARCHAR2(32767);
v_file_dir VARCHAR2(100) := 'YOUR_DIRECTORY'; -- Replace with your Oracle directory
v_file_name VARCHAR2(100) := 'employee_report.txt';

-- Cursor to retrieve employee details exceeding average department salary
CURSOR c_emp_report IS
    SELECT e.EMPLOYEE_ID,
           e.FIRST_NAME,
           e.LAST_NAME,
           e.DEPARTMENT_ID,
           e.SALARY,
           (SELECT AVG(SALARY) 
            FROM EMPLOYEES 
            WHERE DEPARTMENT_ID = e.DEPARTMENT_ID) AS AVG_SALARY
    FROM EMPLOYEES e
    WHERE e.SALARY > 
        (SELECT AVG(SALARY) 
         FROM EMPLOYEES 
         WHERE DEPARTMENT_ID = e.DEPARTMENT_ID);

BEGIN
-- Open the file for writing
v_file_handle := UTL_FILE.FOPEN(v_file_dir, v_file_name, 'W');

-- Write the header line
UTL_FILE.PUT_LINE(v_file_handle, 'EMPLOYEE_ID | FIRST_NAME | LAST_NAME | DEPARTMENT_ID | SALARY | AVG_SALARY');
UTL_FILE.PUT_LINE(v_file_handle, '-------------------------------------------------------------------------------');

-- Loop through the cursor and write each employee record to the file
FOR r_emp IN c_emp_report LOOP
    v_line := r_emp.EMPLOYEE_ID || ' | ' ||
              r_emp.FIRST_NAME || ' | ' ||
              r_emp.LAST_NAME || ' | ' ||
              r_emp.DEPARTMENT_ID || ' | ' ||
              r_emp.SALARY || ' | ' ||
              r_emp.AVG_SALARY;
              
    UTL_FILE.PUT_LINE(v_file_handle, v_line);
END LOOP;

-- Close the file
UTL_FILE.FCLOSE(v_file_handle);

EXCEPTION
-- Handle exceptions
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('Invalid directory path.');
WHEN UTL_FILE.INVALID_MODE THEN
DBMS_OUTPUT.PUT_LINE('Invalid file open mode.');
WHEN UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Error writing to file.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An unexpected error occurred: ' || SQLERRM);
UTL_FILE.FCLOSE(v_file_handle);
END EMP_REP_<userid>;