ITPub博客

首页 > 应用开发 > IT综合 > utl_file来生成文件

utl_file来生成文件

原创 IT综合 作者:weijsh 时间:2007-01-09 16:41:31 0 删除 编辑
用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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-03-18

  • 博文量
    3
  • 访问量
    76117