执行时若系统卡死再次执行 会报ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 475错误, 但是清空目录后有可以正常运行这是核心逻辑sql
v_file_handle utl_file.file_type; --句柄v_cnt number(5);
v_iloop int;
begin
v_iloop := 0;
declare cursor c_sc_retdata is
select t.mer_code||'`'||t.busi_type msg from tc_agent_pay t
BEGIN
v_file_handle:=utl_file.fopen('JHTDATA',v_file,'W');
for v_retdata in c_sc_retdata loop
if v_iloop= 0 then
v_iloop := 1;
end if;
utl_file.put_line(v_file_handle,v_retdata.msg);
END LOOP;
utl_file.fClose(v_file_handle);
end;
v_file_handle utl_file.file_type; --句柄v_cnt number(5);
v_iloop int;
begin
v_iloop := 0;
declare cursor c_sc_retdata is
select t.mer_code||'`'||t.busi_type msg from tc_agent_pay t
BEGIN
v_file_handle:=utl_file.fopen('JHTDATA',v_file,'W');
for v_retdata in c_sc_retdata loop
if v_iloop= 0 then
v_iloop := 1;
end if;
utl_file.put_line(v_file_handle,v_retdata.msg);
END LOOP;
utl_file.fClose(v_file_handle);
end;
v_file_handle utl_file.file_type%rowtype; --句柄
v_cnt number(5);
v_iloop int;
V_msg varchar2(100);
cursor c_sc_retdata is
select t.mer_code||'`'||t.busi_type msg from tc_agent_pay t;
BEGIN
v_iloop := 0;
v_file_handle:=utl_file.fopen('JHTDATA',v_file,'W');
open c_sc_retdata;
loop
fetch c_sc_retdata into V_msg;
exit when c_sc_retdata%notfound;
if v_iloop= 0 then
v_iloop := 1;
end if;
utl_file.put_line(v_file_handle,V_msg);
END LOOP;
close c_sc_retdata;
utl_file.fClose(v_file_handle);
end;