PL/SQL错误 - 无效文件ID

Question

需要关于代码和错误的帮助。执行此代码后：

CREATE OR REPLACE DIRECTORY dir_vezba AS '/home/oracle/vezba'; 

CREATE OR REPLACE PROCEDURE raise_salary 
    (p_deptno IN NUMBER, p_per IN NUMBER) 
... 
END raise_salary; 
/

SET VERIFY OFF 
DECLARE 
    v_deptno  NUMBER; 
    v_per   NUMBER; 
    v_oldsalary employees.salary%TYPE; 
    v_newsalary employees.salary%TYPE; 
    file_handle UTL_FILE.FILE_TYPE; 
    f_handle  UTL_FILE.FILE_TYPE; 
    file_line  VARCHAR2(200); 
    f_body  VARCHAR2(200); 
    f_line  VARCHAR2(200); 
    f_head  VARCHAR2(200); 
    file_report VARCHAR2(150); 
    CURSOR emp_cursor IS 
     SELECT employee_id, salary 
     FROM employees 
     WHERE department_id=v_deptno; 
BEGIN 
    f_handle:=UTL_FILE.FOPEN('DIR_VEZBA','POVECANJE_DEP.txt','r'); 
    LOOP 
     BEGIN 
     UTL_FILE.GET_LINE(f_handle, file_line); 
     EXCEPTION 
     WHEN NO_DATA_FOUND THEN 
      EXIT; 
     END;  
     v_deptno:=TO_NUMBER(SUBSTR(file_line,1,3)); 
     v_per:=TO_NUMBER(SUBSTR(file_line,4,3))/1000; 
     file_report:='IZVESTAJ'||TO_CHAR(SYSDATE, 'YY')||TO_CHAR(v_deptno)||'.log'; 
     file_handle:=UTL_FILE.FOPEN('DIR_VEZBA', file_report, 'w'); 
     UTL_FILE.PUTF(file_handle, 'Report generated on: '||SYSDATE||' for department: '||v_deptno); 
     UTL_FILE.NEW_LINE(file_handle); 
     UTL_FILE.NEW_LINE(file_handle); 
     f_head:='EMPNO OLD_SALARY NEW_SALARY'; 
     UTL_FILE.PUTF(file_handle, f_head); 
     UTL_FILE.NEW_LINE(file_handle); 
     f_line:='================================='; 
     UTL_FILE.PUTF(file_handle, f_line); 
     UTL_FILE.NEW_LINE(file_handle); 
     FOR emp_rec IN emp_cursor 
     LOOP 
     v_oldsalary:=emp_rec.salary; 
     raise_salary(v_deptno, v_per); 
     SELECT salary 
      INTO v_newsalary   
      FROM employees 
      WHERE employee_id=emp_rec.employee_id; 
     f_body:=RPAD(emp_rec.employee_id,9,' ')||RPAD(v_oldsalary,14,' ')||RPAD(v_newsalary,10,' '); 
     UTL_FILE.PUTF(file_handle, f_body); 
     UTL_FILE.FCLOSE(file_handle); 
     END LOOP; 
    END LOOP; 
    UTL_FILE.FCLOSE(f_handle); 
EXCEPTION 
    WHEN UTL_FILE.INVALID_OPERATION THEN 
     file_report:='IZVESTAJ'||TO_CHAR(SYSDATE, 'YY')||'.bad'; 
     file_handle:=UTL_FILE.FOPEN('DIR_VEZBA', file_report, 'w'); 
     UTL_FILE.PUTF(file_handle, 'NO FILE FOUND'); 
     UTL_FILE.FCLOSE(file_handle); 
END; 
/

我得到这个错误：

ORA-29282: invalid file ID 
ORA-06512: at "SYS.UTL_FILE", line 1071 
ORA-06512: at line 48 

不知道什么这个问题，这是什么意思。除此之外，在输出中我只能得到一行txt文件而没有其他的东西。它应该给我3个不同数量的文本文本，取决于表中的数据。

Answer 1

这是你的问题：

FOR emp_rec IN emp_cursor 
    LOOP 
    v_oldsalary:=emp_rec.salary; 
    raise_salary(v_deptno, v_per); 
    SELECT salary 
     INTO v_newsalary   
     FROM employees 
     WHERE employee_id=emp_rec.employee_id; 
    f_body:=RPAD(emp_rec.employee_id,9,' ')||RPAD(v_oldsalary,14,' ')||RPAD(v_newsalary,10,' '); 
    UTL_FILE.PUTF(file_handle, f_body); 
    ----------> UTL_FILE.FCLOSE(file_handle); <----------- 
    END LOOP; 

您在环路关闭文件时，所以下一轮迭代的PUTF将失败。