巧用Oracle Discoverer中的資料字典檢查join

Oracle Discoverer中的EUL(End User Layer)其實是一些資料字典，儲存Discoverer Objects資訊。合理利用這些資料字典能極大提高工作效率。[@more@]

問題：

EDENFR使用者已經配置好所有的Discoverer Objects(包括Business Area, Folder, Join等)，以此為基礎，將所有Discoverer Objects複製到EDENUK使用者下，一般採用exp/imp Business Area然後修改屬性的方法。在給EDENUK修改屬性和配置過程中，經常由於各種原因會出現各種改動。

現在客戶要求以EDENFR的joins為標準，檢查EDENUK中所有joins，並列出EDENUK中所有不一致的joins。類似的檢查也要在EDENES,EDENDE等多個使用者下進行。

開啟Discoverer administrator，發現EDENFR下的joins有一百多個，如果逐條檢查，工作量將非常巨大。

方法：

eul4_key_cons.key_name紀錄了該EUL中所有的join名字，而且Discoverer中join的命名有如下規則：From_Folder_name -> To_Folder_name, 比如：

FR_##_CSTM -> FR_??_LIVE_VHC
FR_##_CSTM -> FR_??_LIVE_VHC 2
FR_##_CSTM -> FR_COST_CENT

以下SQL能返回join ID, join name, master folder(from folder), detail folder(to folder)資訊：

SQL>select ekc.key_id as join_id
     , ekc.key_name || '  [ Master: ' || eor.obj_name || ' ;  Detail: ' || eo.obj_name  || ']' as join_desc
 from   eul4_key_cons ekc
     , eul4_objs eo
    , eul4_objs eor
where  ekc.key_obj_id = eo.obj_id
and    ekc.fk_obj_id_remote = eor.obj_id

 JOIN_ID JOIN_DESC
1 101685 FR_SLS_QTTN -> FR_CSTM_ORD_HDR  [ Master: FR_SLS_QTTN ;  Detail: FR_CSTM_ORD_HDR]
2 101697 FR_SLS_QTTN -> FR_DRV  [ Master: FR_SLS_QTTN ;  Detail: FR_DRV]
3 101701 FR_SLS_QTTN -> FR_SLS_QTTN_2  [ Master: FR_SLS_QTTN ;  Detail: FR_SLS_QTTN_2]
4 101713 FR_SLS_QTTN -> FR_SLS_QTTN_ITEM  [ Master: FR_SLS_QTTN ;  Detail: FR_SLS_QTTN_ITEM]

檢查過程：

根據以上規律和資訊，可以用下面方法快速檢查

1. 建立表，儲存EDENFR的 join / join from table / join to table 資訊
create table tmp_join_fr
as
select ekc.key_name join_name,
       substr(ekc.key_name, 4, instr(ekc.key_name, '->', 1, 1) - 5) fromcol,
       substr(ekc.key_name, instr(ekc.key_name, '->', 1, 1) + 6) tocol
  from eul4_key_cons ekc, eul4_objs eo, eul4_objs eor
 where ekc.key_obj_id = eo.obj_id
   and ekc.fk_obj_id_remote = eor.obj_id
   and ekc.key_name like 'FR_%'

2. 建立表，儲存EDENUK join / join from table / join to table 資訊  
create table tmp_join_uk
as
select ekc.key_name join_name,
       substr(ekc.key_name, 4, instr(ekc.key_name, '->', 1, 1) - 5) fromcol,
       substr(ekc.key_name, instr(ekc.key_name, '->', 1, 1) + 6) tocol
  from eul4_key_cons ekc, eul4_objs eo, eul4_objs eor
 where ekc.key_obj_id = eo.obj_id
   and ekc.fk_obj_id_remote = eor.obj_id
   and ekc.key_name like 'UK_%'

3. 用以下SQL檢查EDENFR中有而EDENUK中缺失的join
select * from tmp_join_fr where fromcol||tocol not in (select fromcol||tocol from tmp_join_uk)  

 用以下SQL檢查EDENFR中沒有有而EDENUK中有的join

select * from tmp_join_uk where fromcol||tocol not in (select fromcol||tocol from tmp_join_fr)