12new

CREATE OR REPLACE PROCEDURE EMP_REP193 (
p_errbuff OUT VARCHAR2,
p_retcode OUT NUMBER,
p_dir IN VARCHAR2,
p_filename IN VARCHAR2
) IS
v_file UTL_FILE.FILE_TYPE;
v_line VARCHAR2(1000);
CURSOR c_emp IS
SELECT last_name, department_id, salary
FROM employees outer
WHERE salary > (
SELECT AVG(salary)
FROM employees inner
WHERE inner.department_id = outer.department_id
)
ORDER BY department_id;
BEGIN
-- Initialize output variables
p_errbuff := NULL;
p_retcode := 0;

-- Open the file for writing
v_file := UTL_FILE.FOPEN(p_dir, p_filename, 'W');

-- Write the header to the file
UTL_FILE.PUT_LINE(v_file, 'LAST_NAME, DEPARTMENT_ID, SALARY');

-- Loop through the cursor and write each employee's data to the file
FOR emp_rec IN c_emp LOOP
    v_line := emp_rec.last_name || ', ' || emp_rec.department_id || ', ' || emp_rec.salary;
    UTL_FILE.PUT_LINE(v_file, v_line);
END LOOP;

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

EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
p_errbuff := 'Invalid directory path.';
p_retcode := 1;
WHEN UTL_FILE.INVALID_MODE THEN
p_errbuff := 'Invalid file open mode.';
p_retcode := 2;
WHEN UTL_FILE.WRITE_ERROR THEN
p_errbuff := 'File write error.';
p_retcode := 3;
WHEN OTHERS THEN
p_errbuff := SQLERRM;
p_retcode := 4;
IF UTL_FILE.IS_OPEN(v_file) THEN
UTL_FILE.FCLOSE(v_file);
END IF;
END EMP_REP193;