在Instrument表中注册字段
OFSA中除了在OFDM中对对象进行注册外,用户也可以使用脚本对对象进行注册,但是OFSA提供了一些脚本,需要根据需要进行修改,而下面的内容,是在已有的instrument表的基础上根据需要注册新字段和反向注册字段,其实也就是将已经注册的信息进行删除或新添加一些信息, 如需完全反向注册,下面的过程还需要完善.
1．创建一个序列，用于生成DBF_NAME
create sequence seq_REGEDIT_OFSA_COLUMN
increment by 1
start with 1
nomaxvalue
nocycle cache 10
2．编写注册用的存储过程。
----------------------------------------------------------
参数：p_table_name要注册字段的表名，
p_column_name字段名称
p_data_type数据库数据类型，
p_ofdm_data_type OFDM类型。
----------------------------------------------------------
CREATE OR REPLACE PROCEDURE REGEDIT_OFSA_COLUMN
(
p_table_name in varchar2,
p_column_name in varchar2,
p_data_type in varchar2,
p_ofdm_type in varchar2
) as
str varchar2(500);
v_ofdm_data_type number;
cursor c is select t.object_name from all_objects t
where t.object_type='TABLE' and t.object_name like 'FEM_%' and t.owner='OFSA';
v_table_name varchar2(35);
begin
--all of the parameters are not allowed null
if trim(p_column_name) is null
or trim(p_data_type) is null
or trim(p_ofdm_type) is null then
boc_ofsa_log_regedit('PROCEDURE','REGEDIT_OFSA_COLUMN','REGEDIT_OFSA_COLUMN','要注册的表名,字段名,数据类型,OFDM类型都不能为空',sysdate);
return ;
end if;
--disable trigger.
begin
str:='ALTER TRIGGER ofsa_tab_columns_ar1 disable';
execute immediate str;
exception
when others then
return ;
end ;
if trim(p_table_name) is not null then
-- add column name to table
begin
str:='alter table '||p_table_name||' add('||p_column_name||' '||p_data_type||' '||') ';
execute immediate str;
exception
when others then
boc_ofsa_log_regedit('PROCEDURE','REGEDIT_OFSA_COLUMN',sqlcode,'注册失败,错误信息为:'||sqlerrm||', 请根据错误信息修改参数.',sysdate);
return ;
end ;
--deal with the ofsa data type
begin
select a.ofsa_data_type_cd into v_ofdm_data_type from ofsa_data_type_dsc a where a.ofsa_data_type=upper(trim(p_ofdm_type)) and rownum=1;
exception
when others then
v_ofdm_data_type:=0;
end ;
--regedit the ofsa information
begin
INSERT INTO OFSA_TAB_COLUMNS (TABLE_NAME, COLUMN_NAME, OWNER, DISPLAY_FLG, OFSA_DATA_TYPE_CD, DBF_NAME)
VALUES (upper(p_table_name),upper(p_column_name),USER, 1, v_ofdm_data_type,'A_'||to_char(seq_regedit_ofsa_column.nextval));
INSERT INTO OFSA_TAB_COLUMNS_MLS (MLS_CD, TABLE_NAME, COLUMN_NAME, OWNER, DISPLAY_NAME, DESCRIPTION)
VALUES ('US',upper(p_table_name),upper(p_column_name), USER,upper(p_column_name),upper(p_column_name));
commit;
boc_ofsa_log_regedit('PROCEDURE','REGEDIT_OFSA_COLUMN','REGEDIT_OFSA_COLUMN','成功在表'||p_table_name||'中注册了字段'||p_column_name,sysdate);
exception
when others then
boc_ofsa_log_regedit('PROCEDURE','REGEDIT_OFSA_COLUMN','REGEDIT_OFSA_COLUMN','插入ofsa_tab_column时发生错误,注册失败',sysdate);
commit;
end;
else
open c;
loop
fetch c into v_table_name;
exit when c%notfound;
begin
str:='alter table '||v_table_name||' add('||p_column_name||' '||p_data_type||' '||') ';
begin
select a.ofsa_data_type_cd into v_ofdm_data_type from ofsa_data_type_dsc a where a.ofsa_data_type=upper(trim(p_ofdm_type)) and rownum=1;
exception
when others then
v_ofdm_data_type:=0;
end ;
--regedit the ofsa information
begin
INSERT INTO OFSA_TAB_COLUMNS (TABLE_NAME, COLUMN_NAME, OWNER, DISPLAY_FLG, OFSA_DATA_TYPE_CD, DBF_NAME)
VALUES (v_table_name,upper(p_column_name),USER, 1, v_ofdm_data_type,'A_'||to_char(seq_regedit_ofsa_column.nextval));
INSERT INTO OFSA_TAB_COLUMNS_MLS (MLS_CD, TABLE_NAME, COLUMN_NAME, OWNER, DISPLAY_NAME, DESCRIPTION)
VALUES ('US',v_table_name,upper(p_column_name), USER,upper(p_column_name),upper(p_column_name));
boc_ofsa_log_regedit('PROCEDURE','REGEDIT_OFSA_COLUMN','REGEDIT_OFSA_COLUMN','成功在表'||v_table_name||'中注册了字段'||p_column_name,sysdate);
exception
when others then
boc_ofsa_log_regedit('PROCEDURE','REGEDIT_OFSA_COLUMN',v_table_name,'插入ofsa_tab_column时发生错误,注册失败',sysdate);
end;
execute immediate str;
exception
when others then
rollback;
null;
end;
end loop;
close c;
commit;
end if;
--enable trigger
begin
str:='ALTER TRIGGER ofsa_tab_columns_ar1 enable';
execute immediate str;
exception
when others then
null;
end ;
end;
/
3．如需要在OFSA中现有的Instrument表上注册字段，则只需要执行该过程即可，注册成功或失败的信息记录在BOC_OFSA_LOG日志表中，可以根据时间和日志类型字段查看日志。
4．反向注册
CREATE OR REPLACE PROCEDURE UNREGEDIT_OFSA_COLUMN
(
p_table_name in varchar2,
p_column_name in varchar2
) as
str varchar2(500);
cursor c is select t.object_name from all_objects t
where t.object_type='TABLE' and t.object_name like 'FEM_%' and t.owner='OFSA';
v_table_name varchar2(35);
begin
--all of the parameters are not allowed null
if trim(p_column_name) is null then
boc_ofsa_log_regedit('PROCEDURE','REGEDIT_OFSA_COLUMN','REGEDIT_OFSA_COLUMN','要反向注册的字段名不能为空',sysdate);
return ;
end if;
if trim(p_table_name) is not null then
-- add column name to table
begin
str:='alter table '||p_table_name||' drop column '||p_column_name;
execute immediate str;
exception
when others then
boc_ofsa_log_regedit('PROCEDURE','REGEDIT_OFSA_COLUMN',sqlcode,'反向注册失败,错误信息为:'||sqlerrm||', 请根据错误信息修改参数.',sysdate);
return ;
end ;
--regedit the ofsa information
begin
delete from ofsa_tab_columns a where a.table_name=upper(p_table_name);
delete from ofsa_tab_columns_mls a where a.table_name=upper(p_table_name);
commit;
boc_ofsa_log_regedit('PROCEDURE','REGEDIT_OFSA_COLUMN','REGEDIT_OFSA_COLUMN','成功反向注册表'||p_table_name||'中的字段'||p_column_name,sysdate);
exception
when others then
boc_ofsa_log_regedit('PROCEDURE','REGEDIT_OFSA_COLUMN','REGEDIT_OFSA_COLUMN','操作OFSA_TAB_COLUMNS表时发生错误,反向注册失败,',sysdate);
rollback;
end;
else
open c;
loop
fetch c into v_table_name;
exit when c%notfound;
begin
str:='alter table '||v_table_name||' drop column '||p_column_name;
begin
delete from ofsa_tab_columns t where t.table_name= v_table_name and t.column_name=upper(p_column_name);
delete from ofsa_tab_columns_mls t where t.table_name= v_table_name and t.column_name=upper(p_column_name);
commit;
boc_ofsa_log_regedit('PROCEDURE','REGEDIT_OFSA_COLUMN','REGEDIT_OFSA_COLUMN','成功反向注册了表'||v_table_name||'中的字段'||p_column_name,sysdate);
exception
when others then
boc_ofsa_log_regedit('PROCEDURE','REGEDIT_OFSA_COLUMN','REGEDIT_OFSA_COLUMN','操作ofsa_tab_column时发生错误,反向注册字段||p_column_name||失败',sysdate);
end;
execute immediate str;
exception
when others then
null;
end;
end loop;
close c;
commit;
end if;
end;
/
执行该存储过程，是否已经成功地反向注册了表的信息，在日志表中可以查到。
可以到PA模块的分摊的界面去查看相关字段是否已经执行成功.
Author:丁亚军
date: 2005-10-09
476
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
