utl_file来生成文件

用utl_file来生成文件[@more@]procedure sp_organization
is
cursor c1 is
select * from pb_organization;
v_rowvalue1 c1%rowtype;
v_id pb_organization.id%type;
v_organizationcode pb_organization.organizationcode%type;
v_shortname pb_organization.shortname%type;
v_appellation pb_organization.appellation%type;
v_postcode pb_organization.postcode%type;
begin
open c1;
l_output:=utl_file.fopen( 'd:oracleoradatasync', 'YT_PB_ORGANIZATION_'||to_char(sysdate-1,'YYYYMMDD')||'.dat', 'w' );
loop
fetch c1 into v_rowvalue1;
exit when c1%notfound;
v_ID:=v_rowvalue1.id;
v_organizationcode:=v_rowvalue1.organizationcode;
v_shortname:=v_rowvalue1.shortname;
v_appellation:=v_rowvalue1.appellation;
v_postcode:=v_rowvalue1.postcode;
utl_file.putf(l_output,v_id||'|'||v_organizationcode||'|'||v_shortname||'|'||v_appellation||'|'||v_postcode||'|'||'n');
end loop;
close c1;
utl_file.fclose( l_output );
delete from t_syncdata_log
where to_char(export_date,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd')
and table_name='pb_organization';
insert into t_syncdata_log(id,table_name,export_date,sign)
values(seq_syncdata_log.nextval,'pb_organization',sysdate,1);
commit;
v_sign:=1;
exception
when others then
delete from t_syncdata_log
where to_char(export_date,'yyyy-mm-dd')=to_char(sysdate,'yyyy-mm-dd')
and table_name='pb_organization' and sign=0;
insert into t_syncdata_log(id,table_name,export_date,sign)
values(seq_syncdata_log.nextval,'pb_organization',sysdate,0);
commit;
l_output:=utl_file.fopen( 'd:oracleoradatasync', 'pb_organization_error.txt', 'w' );
utl_file.put(l_output,to_char(sysdate,'yyyy-mm-dd')||'error');
utl_file.fclose(l_output);
v_sign:=0;
end sp_organization;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/32202/viewspace-889185/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/32202/viewspace-889185/