본문 바로가기

ETL Tools/Oracle Data Integrator

[ODI Repository] Search Interface Description

/*

Created by Cheon.jh

Creeted on 2012.05.25

ODI Version. 11.1.1.6

Using on showing the description of all Interface.


*/



SELECT P.TABLE_NAME,

  '/*

  Description

  Job Path: '||F.PROJECT_NAME|| ' - ' ||

  F.FOLDER_PATH|| '

  Interface Name: ' ||

  P.POP_NAME|| '

  Source Table: ' ||

  LISTAGG(S.LSCHEMA_NAME||'.'||S.TABLE_NAME ,', ') WITHIN GROUP ( ORDER BY S.TAB_ORD) || '

  Target Table: ' ||

  P.LSCHEMA_NAME||'.'||P.TABLE_NAME||'

  */' SQL_Query

FROM SNP_POP P,

    SNP_DATA_SET DS,

    SNP_SOURCE_TAB S,

  (

    SELECT I_FOLDER,

      PROJECT_NAME,

      SYS_CONNECT_BY_PATH(FOLDER_NAME, '/') FOLDER_PATH

    FROM SNP_FOLDER F,

      SNP_PROJECT P

    WHERE 1=1

      AND F.I_PROJECT = P.I_PROJECT 

    START WITH PAR_I_FOLDER IS NULL 

    CONNECT BY PRIOR I_FOLDER = PAR_I_FOLDER

    ) F

WHERE 1=1

  AND P.I_POP = DS.I_POP

  AND DS.I_DATA_SET = S.I_DATA_SET

  AND P.I_FOLDER = F.I_FOLDER

GROUP BY 

    F.PROJECT_NAME,

    F.FOLDER_PATH,

    P.POP_NAME,

    P.LSCHEMA_NAME,

    P.TABLE_NAME