首页 > 代码库 > LIMS系统ODI配置与使用
LIMS系统ODI配置与使用
以食品REPORTS表为例，说明ODI在LIMS系统中的配置与使用。
先为REPORTS表添加列LIMS_CODE
注意：类型为NUMBER(*,0)，不允许为空，默认值为4（中心为1、可靠性为2、环境为3、食品为4）；
说明：
ODI的增量刷新要求源表与目标表都有主键，并且同步根据主键来刷新其他字段，这就要求主键值永久不变。在LIMS系统中，主键值是会变的，比如ORDTASK中的，就有修改测试方法的功能，如果主键发生了变化，则后续的刷新就无法完成。为了使LIMS能够正常同步，引入了ORIGRE + LIMS_CODE组合，以确保在系统中唯一，同时也保证值永久不会变化。
再添加唯一索引
在模型ODI_SRC_FDD（为每套系统建立起一套模型）中，添加REPORTS的元数据信息(配置模型等过程省略)
如下图，打开ODI_SRC_FDD模型
选择右边的取消全选
选择REPORTS，再选择“Reverse Engineer”
选择“Yes”
逆向工程完成后，ODI_SRC_FDD模型中就有了ORDTASK的信息，如图
接下来，需要配置同步信息了。为了简化配置操作，封装了函数CTI_FDD_DATA.FN_ODI，并且创建了公共同义词
如上图，第一个参数为模式名，USER为当前模式名，REPORTS为表名，函数返回信息如下：
INSERT INTO SNP_SUBSCRIBERS(JRN_TNAME,JRN_SUBSCRIBER,JRN_REFDATE,JRN_ROW_COUNT,JRN_DATA_CMD,JRN_COUNT_CMD) VALUES (‘CTI_FDD_DATA.REPORTS‘,‘REPORTS‘,SYSDATE,0,NULL,‘update CTI_FDD_DATA.SNP_SUBSCRIBERS SUBS set SUBS.JRN_ROW_COUNT=(select count(‘‘X‘‘) from CTI_FDD_DATA.J$REPORTS JRN where JRN.JRN_SUBSCRIBER=‘‘$$SUBSCRIBER_NAME$$‘‘) where SUBS.JRN_SUBSCRIBER=‘‘$$SUBSCRIBER_NAME$$‘‘ and SUBS.JRN_TNAME = ‘‘CTI_FDD_DATA.REPORTS‘‘‘);commit;CREATE TABLE J$REPORTS(jrn_subscriber VARCHAR2(400 CHAR) not null, jrn_consumed VARCHAR2(1 CHAR), jrn_flag VARCHAR2(1 CHAR), jrn_date DATE, ORIGREC NUMBER(*,0), LIMS_CODE NUMBER(*,0));create or replace trigger T$REPORTS after insert or update or delete on CTI_FDD_DATA.REPORTS for each rowdeclare V_FLAG VARCHAR(1); V_ORIGREC NUMBER; V_LIMS_CODE NUMBER;begin if inserting then V_ORIGREC := :new.ORIGREC; V_LIMS_CODE := :new.LIMS_CODE; V_FLAG := ‘I‘; end if; if updating then V_ORIGREC := :new.ORIGREC; V_LIMS_CODE := :new.LIMS_CODE; V_FLAG := ‘I‘; end if; if deleting then V_ORIGREC := :old.ORIGREC; V_LIMS_CODE := :old.LIMS_CODE; V_FLAG := ‘D‘; end if; insert into CTI_FDD_DATA.J$REPORTS (JRN_SUBSCRIBER, JRN_CONSUMED, JRN_FLAG, JRN_DATE, ORIGREC, LIMS_CODE) select JRN_SUBSCRIBER, ‘0‘, V_FLAG, sysdate, V_ORIGREC, V_LIMS_CODE from CTI_FDD_DATA.SNP_SUBSCRIBERS where JRN_TNAME = ‘CTI_FDD_DATA.REPORTS‘;end;create or replace view jv$dREPORTS as select decode(TARG.ROWID, null, ‘D‘, ‘I‘) AS JRN_FLAG, JRN.JRN_SUBSCRIBER AS JRN_SUBSCRIBER, JRN.JRN_DATE AS JRN_DATE, JRN.ORIGREC AS ORIGREC, JRN.LIMS_CODE AS LIMS_CODE, TARG.ORIGSTS,TARG.FOLDERNO,TARG.REPORTNO,TARG.REPORTTYPE,TARG.REPORTCAT,TARG.REPORTGENTYPE,TARG.STATUS,TARG.DISPSTS,TARG.ASSESS,TARG.ASSESS_ENG,TARG.REMARK,TARG.ADDEDBY,TARG.ADDEDDATE,TARG.TESTBY,TARG.TESTBYFULLNAME,TARG.TESTDATE,TARG.APPROVEDBY,TARG.APPROVEDDATE,TARG.RELEASEDBY,TARG.RELEASEDDATE,TARG.PATH,TARG.PATH_PDF,TARG.PATH_WATERMARK,TARG.DIVISIONCODE,TARG.DEPT,TARG.DEPTNAME,TARG.ORDNOS,TARG.REFUSEFLAG,TARG.EDIT_TIMES,TARG.SENDEXPRESSNO,TARG.NEEDSIGN,TARG.HASSIGNED,TARG.ALLOWEDIT,TARG.HASSIGNED_W from (select L.JRN_SUBSCRIBER AS JRN_SUBSCRIBER, L.ORIGREC AS ORIGREC, L.LIMS_CODE AS LIMS_CODE, max(L.JRN_DATE) AS JRN_DATE from CTI_FDD_DATA.J$REPORTS L group by L.JRN_SUBSCRIBER, L.ORIGREC,L.LIMS_CODE) JRN, CTI_FDD_DATA.REPORTS TARG where JRN.ORIGREC = TARG.ORIGREC(+) and JRN.LIMS_CODE = TARG.LIMS_CODE(+);create or replace view jv$REPORTS asselect decode(TARG.ROWID, null, ‘D‘, ‘I‘) AS JRN_FLAG, JRN.JRN_SUBSCRIBER AS JRN_SUBSCRIBER, JRN.JRN_DATE AS JRN_DATE, JRN.ORIGREC AS ORIGREC, JRN.LIMS_CODE AS LIMS_CODE,TARG.ORIGSTS,TARG.FOLDERNO,TARG.REPORTNO,TARG.REPORTTYPE,TARG.REPORTCAT,TARG.REPORTGENTYPE,TARG.STATUS,TARG.DISPSTS,TARG.ASSESS,TARG.ASSESS_ENG,TARG.REMARK,TARG.ADDEDBY,TARG.ADDEDDATE,TARG.TESTBY,TARG.TESTBYFULLNAME,TARG.TESTDATE,TARG.APPROVEDBY,TARG.APPROVEDDATE,TARG.RELEASEDBY,TARG.RELEASEDDATE,TARG.PATH,TARG.PATH_PDF,TARG.PATH_WATERMARK,TARG.DIVISIONCODE,TARG.DEPT,TARG.DEPTNAME,TARG.ORDNOS,TARG.REFUSEFLAG,TARG.EDIT_TIMES,TARG.SENDEXPRESSNO,TARG.NEEDSIGN,TARG.HASSIGNED,TARG.ALLOWEDIT,TARG.HASSIGNED_W from ( select L.JRN_SUBSCRIBER AS JRN_SUBSCRIBER, L.ORIGREC AS ORIGREC, L.LIMS_CODE AS LIMS_CODE, max(L.JRN_DATE) AS JRN_DATE from CTI_FDD_DATA.J$REPORTS L where L.JRN_CONSUMED =‘1‘ group by L.JRN_SUBSCRIBER, L.ORIGREC,L.LIMS_CODE ) JRN, CTI_FDD_DATA.REPORTS TARGwhere JRN.ORIGREC = TARG.ORIGREC(+) and JRN.LIMS_CODE = TARG.LIMS_CODE(+);执行完上面语句后，同步的基本信息已经配置完成。接下来，需要将表添加到CDC，再配置映射关系。
如图，选择“Add to CDC”
发现，为表REPORT添加了绿色小时钟。
新建映射
如图，将ODI_SRC_FDD下面的REPORTS表、ODI_TAG_DC下面的REPORTS表拖动到窗口中
从源表拉动到目标表，建立字段映射关系，如下图
点击工具栏上的勾，进行映射验证（主要验证类型及字段长度）
验证成功
修改订阅用户（由于我们的基础信息由自定义函数生成，订阅用户默认与表名相同）
选择“增量更新”
选择仅记录的日志
选择加载知识模型
选择集成知识模型
REPORTS_FDD映射已经配置完成
选择“Run”，验证执行情况
从执行日志上面看，配置是没有问题的。接下来，进行数据方面的测试
如图，向源表插入了一条记录，然后执行同步
查询目标表，数据已经刷新
修改数据（主键REPORTNO + 其他字段REPORTTYPE）并提交，执行同步
数据已经刷新
删除数据提交，并执行同步
数据已刷新。
附FN_ODI函数
CREATE OR REPLACE FUNCTION FN_ODI(v_SCHEMA VARCHAR2, v_TABLENAME VARCHAR2) RETURN CLOB AUTHID CURRENT_USER IS m_out CLOB; m_cols CLOB; m_cnt INTEGER;BEGIN m_out := ‘‘; --创建订阅用户 SELECT COUNT(*) INTO m_cnt FROM SNP_SUBSCRIBERS WHERE JRN_TNAME = v_SCHEMA || ‘.‘ || v_TABLENAME; IF m_cnt = 0 THEN m_out := ‘INSERT INTO SNP_SUBSCRIBERS(JRN_TNAME,JRN_SUBSCRIBER,JRN_REFDATE,JRN_ROW_COUNT,JRN_DATA_CMD,JRN_COUNT_CMD) VALUES (‘‘‘ || v_SCHEMA || ‘.‘ || v_TABLENAME || ‘‘‘,‘‘‘ || v_TABLENAME || ‘‘‘,SYSDATE,0,NULL,‘‘update ‘ || v_SCHEMA || ‘.SNP_SUBSCRIBERS SUBS set SUBS.JRN_ROW_COUNT=(select count(‘‘‘‘X‘‘‘‘) from ‘ || v_SCHEMA || ‘.J$‘ || v_TABLENAME || ‘ JRN where JRN.JRN_SUBSCRIBER=‘‘‘‘$$SUBSCRIBER_NAME$$‘‘‘‘) where SUBS.JRN_SUBSCRIBER=‘‘‘‘$$SUBSCRIBER_NAME$$‘‘‘‘ and SUBS.JRN_TNAME = ‘‘‘‘‘ || v_SCHEMA || ‘.‘ || v_TABLENAME || ‘‘‘‘‘‘‘);‘; m_out := m_out || chr(10); m_out := m_out || ‘commit;‘; m_out := m_out || chr(10) || chr(10); END IF; -- 创建日志表 m_out := m_out || ‘CREATE TABLE J$‘ || v_TABLENAME || ‘(jrn_subscriber VARCHAR2(400 CHAR) not null, jrn_consumed VARCHAR2(1 CHAR), jrn_flag VARCHAR2(1 CHAR), jrn_date DATE, ORIGREC NUMBER(*,0), LIMS_CODE NUMBER(*,0));‘; --创建触发器 m_out := m_out || chr(10) || chr(10); m_out := m_out || ‘create or replace trigger T$‘ || v_TABLENAME || ‘ after insert or update or delete on ‘ || v_SCHEMA || ‘.‘ || v_TABLENAME || ‘ for each rowdeclare V_FLAG VARCHAR(1); V_ORIGREC NUMBER; V_LIMS_CODE NUMBER;begin if inserting then V_ORIGREC := :new.ORIGREC; V_LIMS_CODE := :new.LIMS_CODE; V_FLAG := ‘‘I‘‘; end if; if updating then V_ORIGREC := :new.ORIGREC; V_LIMS_CODE := :new.LIMS_CODE; V_FLAG := ‘‘I‘‘; end if; if deleting then V_ORIGREC := :old.ORIGREC; V_LIMS_CODE := :old.LIMS_CODE; V_FLAG := ‘‘D‘‘; end if; insert into ‘ || v_SCHEMA || ‘.J$‘ || v_TABLENAME || ‘ (JRN_SUBSCRIBER, JRN_CONSUMED, JRN_FLAG, JRN_DATE, ORIGREC, LIMS_CODE) select JRN_SUBSCRIBER, ‘‘0‘‘, V_FLAG, sysdate, V_ORIGREC, V_LIMS_CODE from ‘ || v_SCHEMA || ‘.SNP_SUBSCRIBERS where JRN_TNAME = ‘‘‘ || v_SCHEMA || ‘.‘ || v_TABLENAME || ‘‘‘;end;‘; --获取列信息 SELECT WM_CONCAT(‘TARG.‘ || COLUMN_NAME) INTO m_cols FROM (SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = v_TABLENAME AND COLUMN_NAME NOT IN (‘ORIGREC‘, ‘LIMS_CODE‘) ORDER BY COLUMN_ID); --创建视图 m_out := m_out || chr(10) || chr(10); m_out := m_out || ‘create or replace view jv$d‘ || v_TABLENAME || ‘ as select decode(TARG.ROWID, null, ‘‘D‘‘, ‘‘I‘‘) AS JRN_FLAG, JRN.JRN_SUBSCRIBER AS JRN_SUBSCRIBER, JRN.JRN_DATE AS JRN_DATE, JRN.ORIGREC AS ORIGREC, JRN.LIMS_CODE AS LIMS_CODE, ‘ || m_cols || ‘ from (select L.JRN_SUBSCRIBER AS JRN_SUBSCRIBER, L.ORIGREC AS ORIGREC, L.LIMS_CODE AS LIMS_CODE, max(L.JRN_DATE) AS JRN_DATE from ‘ || v_SCHEMA || ‘.J$‘ || v_TABLENAME || ‘ L group by L.JRN_SUBSCRIBER, L.ORIGREC,L.LIMS_CODE) JRN, ‘ || v_SCHEMA || ‘.‘ || v_TABLENAME || ‘ TARG where JRN.ORIGREC = TARG.ORIGREC(+) and JRN.LIMS_CODE = TARG.LIMS_CODE(+);‘; m_out := m_out || chr(10) || chr(10); m_out := m_out || ‘create or replace view jv$‘ || v_TABLENAME || ‘ asselect decode(TARG.ROWID, null, ‘‘D‘‘, ‘‘I‘‘) AS JRN_FLAG, JRN.JRN_SUBSCRIBER AS JRN_SUBSCRIBER, JRN.JRN_DATE AS JRN_DATE, JRN.ORIGREC AS ORIGREC, JRN.LIMS_CODE AS LIMS_CODE,‘ || m_cols || ‘ from ( select L.JRN_SUBSCRIBER AS JRN_SUBSCRIBER, L.ORIGREC AS ORIGREC, L.LIMS_CODE AS LIMS_CODE, max(L.JRN_DATE) AS JRN_DATE from ‘ || v_SCHEMA || ‘.J$‘ || v_TABLENAME || ‘ L where L.JRN_CONSUMED =‘‘1‘‘ group by L.JRN_SUBSCRIBER, L.ORIGREC,L.LIMS_CODE ) JRN, ‘ || v_SCHEMA || ‘.‘ || v_TABLENAME || ‘ TARGwhere JRN.ORIGREC = TARG.ORIGREC(+) and JRN.LIMS_CODE = TARG.LIMS_CODE(+);‘; RETURN m_out;END;
LIMS系统ODI配置与使用
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。