create or replace procedure p_getusertabsql(
avc_user varchar2)
is
lvc_user varchar2(30);
lvc_table varchar2(30);
li_count number(10);
lvc_bbbz varchar2(200);
lvc_sqlerrm varchar2(4000);
e_usererror exception;
lvc_line varchar2(4000);
cursor cur_tab is
select B.TABLE_NAME,B.BBBZ,B.BFL
from xt_tabqd B
where B.OWNER = lvc_user;
cursor cur_exe is
select table_name
from xt_gettabsqllog
--修改时间:2015-06-05 修改人:zhangruiu
--where tabzt <> -1
where tabzt <> -1 and owner=lvc_user;
--修改完成
fhandle utl_file.file_type;
begin
lvc_user := upper(trim(avc_user));
--0、输入合法性判断
select count(*) into li_count
from xt_tabqd
where owner = lvc_user;
if li_count = 0 then
raise e_usererror;
end if;
--修改日期:2015-06-02 修改人:zhangruiu
delete from xt_gettabsqllog where owner=lvc_user;
--修改完成
for cur in cur_tab loop
lvc_table := cur.table_name;
lvc_bbbz := cur.bbbz;
if lvc_bbbz like '1%' or lvc_bbbz like '2%' then
begin
p_gettabsql(lvc_user,lvc_table);
insert into xt_gettabsqllog values (lvc_user,lvc_table,0,'');
commit;
exception
when others then
lvc_sqlerrm := substr(to_char(sqlcode)||sqlerrm,1,4000);
insert into xt_gettabsqllog values (lvc_user,lvc_table,-1,lvc_sqlerrm);
commit;
end;
end if;
end loop;
--1、打开文件
fhandle := utl_file.fopen(lvc_user||'_FILE_DIR','init-object.sql', 'w');
for cur in cur_exe loop
--修改时间:2015-06-02 修改人:zhangruiu
if lvc_user='J1_G3_ZBQ' then
--lvc_line := '@"01 dw\02 ldm\01 object\01 table\'||cur.table_name||'.sql";';
lvc_line := '@"01 dw\01 inf\01 object\01 table\'||cur.table_name||'.sql";';
elsif lvc_user='J1_LDM' then
lvc_line := '@"01 dw\02 ldm\01 object\01 table\'||cur.table_name||'.sql";';
elsif lvc_user='J1_DW' then
lvc_line := '@"01 dw\03 dw\01 object\01 table\'||cur.table_name||'.sql";';
elsif lvc_user='J1_DI' then
lvc_line := '@"01 dw\05 di\01 object\01 table\'||cur.table_name||'.sql";';
elsif lvc_user='J1_LDCX' then
lvc_line := '@"01 dw\08 ldcx\01 object\01 table\'||cur.table_name||'.sql";';
elsif lvc_user='J1_CXTJ' then
lvc_line := '@"03 xt\01 cxtj\01 object\01 table\'||cur.table_name||'.sql";';
elsif lvc_user='J1_DMT' then
lvc_line := '@"03 xt\06 dmt\01 object\01 table\'||cur.table_name||'.sql";';
elsif lvc_line='J1_METAONE' then
lvc_line := '@"03 xt\07 metaone\01 object\01 table\'||cur.table_name||'.sql";';
end if;
--@"01 dw\02 ldm\01 object\01 table\ldmt02_jbxx_nsrckzhzhxx.sql";
utl_file.put_line(fhandle ,lvc_line);
end loop;
--9、关闭文件。
utl_file.fclose(fhandle);
exception
when e_usererror then
raise_application_error(-20001,'用户名错误,系统中不存在对应用户');
when others then
utl_file.fclose(fhandle);
raise_application_error(-20001,to_char(sqlcode) || sqlerrm);
end;
avc_user varchar2)
is
lvc_user varchar2(30);
lvc_table varchar2(30);
li_count number(10);
lvc_bbbz varchar2(200);
lvc_sqlerrm varchar2(4000);
e_usererror exception;
lvc_line varchar2(4000);
cursor cur_tab is
select B.TABLE_NAME,B.BBBZ,B.BFL
from xt_tabqd B
where B.OWNER = lvc_user;
cursor cur_exe is
select table_name
from xt_gettabsqllog
--修改时间:2015-06-05 修改人:zhangruiu
--where tabzt <> -1
where tabzt <> -1 and owner=lvc_user;
--修改完成
fhandle utl_file.file_type;
begin
lvc_user := upper(trim(avc_user));
--0、输入合法性判断
select count(*) into li_count
from xt_tabqd
where owner = lvc_user;
if li_count = 0 then
raise e_usererror;
end if;
--修改日期:2015-06-02 修改人:zhangruiu
delete from xt_gettabsqllog where owner=lvc_user;
--修改完成
for cur in cur_tab loop
lvc_table := cur.table_name;
lvc_bbbz := cur.bbbz;
if lvc_bbbz like '1%' or lvc_bbbz like '2%' then
begin
p_gettabsql(lvc_user,lvc_table);
insert into xt_gettabsqllog values (lvc_user,lvc_table,0,'');
commit;
exception
when others then
lvc_sqlerrm := substr(to_char(sqlcode)||sqlerrm,1,4000);
insert into xt_gettabsqllog values (lvc_user,lvc_table,-1,lvc_sqlerrm);
commit;
end;
end if;
end loop;
--1、打开文件
fhandle := utl_file.fopen(lvc_user||'_FILE_DIR','init-object.sql', 'w');
for cur in cur_exe loop
--修改时间:2015-06-02 修改人:zhangruiu
if lvc_user='J1_G3_ZBQ' then
--lvc_line := '@"01 dw\02 ldm\01 object\01 table\'||cur.table_name||'.sql";';
lvc_line := '@"01 dw\01 inf\01 object\01 table\'||cur.table_name||'.sql";';
elsif lvc_user='J1_LDM' then
lvc_line := '@"01 dw\02 ldm\01 object\01 table\'||cur.table_name||'.sql";';
elsif lvc_user='J1_DW' then
lvc_line := '@"01 dw\03 dw\01 object\01 table\'||cur.table_name||'.sql";';
elsif lvc_user='J1_DI' then
lvc_line := '@"01 dw\05 di\01 object\01 table\'||cur.table_name||'.sql";';
elsif lvc_user='J1_LDCX' then
lvc_line := '@"01 dw\08 ldcx\01 object\01 table\'||cur.table_name||'.sql";';
elsif lvc_user='J1_CXTJ' then
lvc_line := '@"03 xt\01 cxtj\01 object\01 table\'||cur.table_name||'.sql";';
elsif lvc_user='J1_DMT' then
lvc_line := '@"03 xt\06 dmt\01 object\01 table\'||cur.table_name||'.sql";';
elsif lvc_line='J1_METAONE' then
lvc_line := '@"03 xt\07 metaone\01 object\01 table\'||cur.table_name||'.sql";';
end if;
--@"01 dw\02 ldm\01 object\01 table\ldmt02_jbxx_nsrckzhzhxx.sql";
utl_file.put_line(fhandle ,lvc_line);
end loop;
--9、关闭文件。
utl_file.fclose(fhandle);
exception
when e_usererror then
raise_application_error(-20001,'用户名错误,系统中不存在对应用户');
when others then
utl_file.fclose(fhandle);
raise_application_error(-20001,to_char(sqlcode) || sqlerrm);
end;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29802484/viewspace-1705453/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29802484/viewspace-1705453/