Oracle Discoverer中一些有用的SQL

正確使用Oracle Discoverer的資料字典有時候能極大提高工作效率，可是找不到關於Discoverer資料字典的資料，有資料估計也很難自己把它掌握。那就有多少是多少吧，工作中用到過的SQL貼到這裡，今後用到時好找。 [@more@]

 

Business area和Folder

以下sql顯示所有的business area，下屬的folder和folder的描述資訊

select eb.ba_name
     , eo.obj_name
       , eo.obj_description
from   eul4_ba_obj_links ebol
     , eul4_bas eb
       , eul4_objs eo
where  ebol.bol_ba_id  = eb.ba_id
and    ebol.bol_obj_id = eo.obj_id 
order by ba_name

 

BA_NAME	OBJ_NAME	OBJ_DESCRIPTION
ADMIN	ADM_BA_DOC
ADMIN	ADM_FOLD_DOC
ADMIN	ADM_JOIN_DETAIL_DOC
ADMIN	ADM_REFRESH_LOG_STATUS	ADM_REFRESH_LOG
ADMIN	ADM_JOIN_DOC

 

Folder 和 Item

以下sql顯示所有folder和下屬的item資訊，並用seq顯示其層次

select 1 as seq
     , eo.obj_id as id
     , eo.obj_name as name
from   eul4_objs eo
union
select 2 as seq
     , ee.it_obj_id as id
     , '     ' || ee.exp_name as name
from   eul4_expressions ee
order by id, seq

 

SEQ	ID	NAME
1	100057	ADM_BA_DOC
2	100057	BA_NAME
2	100057	OBJ_DESCRIPTION
2	100057	OBJ_NAME
1	100058	ADM_FOLD_DOC
2	100058	DESCRIPTION
2	100058	ID
2	100058	NAME
2	100058	SEQ
1	100059	ADM_JOIN_DETAIL_DOC
2	100059	DETAIL
2	100059	JOIN_ID
2	100059	MASTER
2	100059	NAME
1	100060	ADM_JOIN_DOC
2	100060	JOIN_DESC
2	100060	JOIN_ID
2	100060	ORD_ID

 

 

Join

一下sql顯示Join詳細資訊，包括join名，master folder, detail folder,以及join所用的item

select ekc.key_id as join_id
     , ekc.key_name || '  [ Master: ' || eor.obj_name || ' ;  Detail: ' || eo.obj_name  || ']' as join_desc
       , 1 as ord_id
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
union
select ee.jp_key_id as join_id
     , '    ' || eod.obj_name || '.' || eee.exp_name as join_desc
       , 2 as ord_id
from   eul4_expressions ee
     , eul4_exp_deps    eed
       , eul4_objs        eod
       , eul4_expressions eee
where  eed.pd_p_id   = ee.exp_id
and    eee.exp_id    = eed.ped_exp_id
and    eee.it_obj_id = eod.obj_id
order by join_id
      , ord_id

JOIN_ID	JOIN_DESC	ORD_ID
101631	FR_COST_CENT -> FR_CSTM_CNCT  [ Master: FR_COST_CENT ;  Detail: FR_CSTM_CNCT]	1
101631	FR_COST_CENT.COST_CENT_ID	2
101631	FR_COST_CENT.CSTM_CMPY_OR_PRSN_ID	2
......	......	.

Oracle Discoverer相關文章：

http://blog.itpub.net/post/334/18294

http://blog.itpub.net/post/334/12966

http://blog.itpub.net/post/334/6813