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
这样的错误,怎么解决
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
这样的错误,怎么解决
我只是这样设了一下,设了写,读到是没有,因为好像不能搞读写吧
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));
这一行只是输入语句而已,你这个现在太长了,我估计你的问题应该是处在后面要写入的值的拼接上
你这个样子:把你后面要写入的值先用字符连接好,然后传过来!
strBuff := 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.put_line(vInHandle,strBuff);
--我估计是你上面拼接的有问题!
v_str_name:=rpad(v_table_name,17,' ');
utl_file.put_line(vInHandle,v_str_name);
这样,它还是报错,我就纳闷了!
grant read,write on directory TESTDIR to usr1;
GRANT EXECUTE ON utl_file TO usr1;