[ODI Repository] Query related with Procedures and Packages

-- Using ODI Repository Info by SQL Query

-- Get IP Address Information of Jobs related with OdiFtp.. (in ODI Work Repository)

-- ODI Version: ODI 10g 10.1.3.5.1

-- Created by Cheon Jeonghyeon 

-- WideTNS Co., Ltd

  

SELECT 

  PROJECT_NAME,  --  프로젝트 명

  FOLDER_PATH,     --  구성된 폴더 경로

  PACK_NAME,       --  패키지 명

  STEP_NAME,        -- 단계 명

  STEP_TYPE,         -- 단계 명령 타입

  REGEXP_SUBSTR(TXT,'([[:digit:]]+\.?){4}')  IP Address -- 연결 네트워크 IP Address

FROM SNP_STEP S,

  SNP_PACKAGE P,

    (

    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,

    SNP_TXT T

WHERE 1=1

  AND S.I_PACKAGE=P.I_PACKAGE

  AND F.I_FOLDER = P.I_FOLDER

  AND S.I_TXT_ACTION = T.I_TXT

  AND STEP_NAME LIKE '%OdiFtp%';