utl_file 问题

Question

set serveroutput on;declare
  v_last_time   date;
  v_cardnum     cardmast.cardnum%type;  
  v_table_name  audtrail.table_name%type;
  v_fid_name    audtrail.fld_name%type;
  v_index_1     audtrail.index_1%type;
  v_index_2     audtrail.index_2%type;
  v_index_3     audtrail.index_3%type;
  v_index_4     audtrail.index_4%type;
  v_before_val  audtrail.before_val%type;
  v_after_val   audtrail.after_val%type;
  v_change_data audtrail.change_date%type;
  v_change_time audtrail.change_time%type;
  v_fld_type    audtrail.fld_type%type;
  v_comments    audtrail.comments%type;
  c_user        audtrail.user_id%type;
  file_name     varchar2(20); 
  vInHandle      utl_file.file_type;
  
  cursor card_adu_cursor( v_last_time date) is 
  select audtrail.table_name,audtrail.fld_name,audtrail.index_1,audtrail.index_2,audtrail.index_3,audtrail.index_4,audtrail.before_val,audtrail.after_val,audtrail.user_id,audtrail.change_date,
  audtrail.change_time,audtrail.fld_type,audtrail.comments from  audtrail,cardmast,temp_cif,calink where cardmast.cif=temp_cif.cif and cardmast.cardnum=audtrail.index_1 and cardmast.cardnum=calink.cardnum and audtrail.change_date=v_last_time;
   
 begin
   select to_char(sysdate-1,'yyyymmdd') into file_name from dual;
  if utl_file.is_open(vInHandle) then
  dbms_output.put_line('al_open--the file');
 else
  vInHandle := utl_file.fopen('DIFF_LED_CC','AUDITINF_'||file_name,'w',32767);  
  dbms_output.put_line('open the file'||file_name);
  end if;  
    select  CURRENT_EOD_DATE into v_last_time from eod_ctrl;
    dbms_output.put_line(v_last_time||'time');
      open  card_adu_cursor(v_last_time);
      loop
       fetch card_adu_cursor into v_table_name,v_fid_name,v_index_1,v_index_2,v_index_3,v_index_4,v_before_val,v_after_val,
        c_user,v_change_data,v_change_time,v_fld_type,v_comments;
       exit when card_adu_cursor%notfound;
           dbms_output.put_line(v_table_name);  
           utl_file.put_line(vInHandle,rpad(v_table_name,17)||rpad(v_fid_name,20)||rpad(v_index_1,20)||rpad(v_index_2,20)||rpad(v_index_3,20)||rpad(v_index_4,20)||rpad(v_before_val,120)||rpad(v_after_val,120)||rpad(c_user,8)||rpad(to_char(v_change_data,'dd/mm/yyyy'),10)||lpad(to_char(v_change_time,'000000'),6)||rpad(v_fld_type,1)||rpad(v_comments,40));
         utl_file.fflush(vInHandle);              
      end loop;
    close card_adu_cursor;
    utl_file.fclose(vInHandle);  
  exception
   when no_data_found then
    dbms_output.put_line('the audtrail!');end;
/
这是我的程序，
一直有个问题就在utl_file.put_line(vInHandle,rpad(v_table_name,17)||rpad(v_fid_name,20)||rpad(v_index_1,20)||rpad(v_index_2,20)||rpad(v_index_3,20)||rpad(v_index_4,20)||rpad(v_before_val,120)||rpad(v_after_val,120)||rpad(c_user,8)||rpad(to_char(v_change_data,'dd/mm/yyyy'),10)||lpad(to_char(v_change_time,'000000'),6)||rpad(v_fld_type,1)||rpad(v_comments,40));这一行，老是报 ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 175
ORA-06512: at "SYS.UTL_FILE", line 1065
ORA-06512: at line 41
这样的错误，怎么解决？

Answer 1

确保  oracle在你要进行日志输出的目录和文件  具有读写权限！

Answer 2

vInHandle := utl_file.fopen('DIFF_LED_CC','AUDITINF_'||file_name,'w',32767);   
是不是应该先放到if utl_file.is_open(vInHandle) then 他前面啊 

Answer 3

谢谢，不是这个原因，我有试过，程序主要的问题在于utl_file.put_line(vInHandle,rpad(v_table_name,17)||rpad(v_fid_name,20)||rpad(v_index_1,20)||rpad(v_index_2,20)||rpad(v_index_3,20)||rpad(v_index_4,20)||rpad(v_before_val,120)||rpad(v_after_val,120)||rpad(c_user,8)||rpad(to_char(v_change_data,'dd/mm/yyyy'),10)||lpad(to_char(v_change_time,'000000'),6)||rpad(v_fld_type,1)||rpad(v_comments,40));只 要把这一行的数据不要，删掉，就没有什么错误了的！