Get a list of all of your ODI objects including the path in your project or model
Get our e-books Discover the Oracle Data Integrator 11g Repository Data Model and Oracle Data Integrator Snippets and Recipes
Below is a query that will retrieve all of the ODI objects in your project and model. It will also spit out the path where they sit in your hierarchy. Very useful if you need to know which objects have changed over the last week or if you want to track down one of the thousands of objects in your ODI projects and models.
FlowForward.
All Things Data Engineering
Straight to Your Inbox!
WITH obj as(
SELECT i_package i_instance, i_folder, NULL i_project, pack_name obj_name,3200 obj_type, 'Package' obj_type_name, last_date, last_user FROM SNP_PACKAGE
UNION ALL
SELECT i_pop i_instance, i_folder, NULL i_project, pop_name obj_name,3100 obj_type, 'Interface' obj_type_name, last_date, last_user FROM SNP_POP
UNION ALL
SELECT i_trt i_instance, i_folder, i_project, trt_name obj_name,3600 obj_type, CASE trt_type WHEN 'U' THEN 'Procedure' ELSE 'Knowledge Module' END obj_type_name, last_date, last_user FROM SNP_TRT
UNION ALL
SELECT i_var i_instance, NULL i_folder, i_project, var_name obj_name, 3500 obj_type, 'Variable' obj_type_name, last_date, last_user FROM SNP_VAR t
UNION ALL
SELECT i_table i_instance, i_sub_model i_folder, i_mod i_project, table_name obj_name, 2400 obj_type, 'Table' obj_type_name, last_date, last_user from snp_table t
)
,fd (i_folder, i_project, folder_name, folder_path, lv) AS(
SELECT i_folder, i_project, folder_name, folder_name folder_path, 1 lv
FROM snp_folder
WHERE par_i_folder IS NULL
UNION ALL
SELECT tf.i_folder, tf.i_project, tf.folder_name, fd.folder_path||''||tf.folder_name, fd.lv+1
FROM snp_folder tf JOIN fd
ON fd.i_folder = tf.par_i_folder
)
,mpl as (
SELECT sm.i_smod i_mc, 'sm' typemc, COALESCE(sm.i_smod_parent,sm.i_mod) i_mp, NVL2(sm.i_smod_parent,'sm','m') typemp, sm.smod_name name --, m.i_mod_folder
FROM snp_sub_model sm
UNION ALL
SELECT i_mod, 'm' typ, i_mod_folder, 'mf', mod_name
FROM snp_model m
UNION ALL
SELECT i_mod_folder, 'mf', par_i_mod_folder, 'mf', mod_folder_name FROM snp_mod_folder
)
,mp (i_mc, typemc, i_mp, typemp, model_tech, model_path, lv) AS(
SELECT i_mc, typemc, i_mp, typemp, name tname, name model_path, 1 lv
FROM mpl
WHERE i_mp IS NULL
UNION ALL
SELECT mpl.i_mc, mpl.typemc, mpl.i_mp, mpl.typemp, mp.model_tech, mp.model_path||''||mpl.name model_path, mp.lv+1 lv
FROM mpl JOIN mp
ON mpl.i_mp = mp.i_mc AND mpl.typemp=mp.typemc
)
SELECT obj.i_instance, OBJ_NAME, CASE WHEN COALESCE(project_name,mp.model_tech) IS NULL THEN 'Global ' || OBJ_TYPE_NAME ELSE OBJ_TYPE_NAME END obj_type_name
,obj.last_date
,obj.last_user
,COALESCE(project_name,mp.model_tech) project_model
,COALESCE(fd.folder_path,mp.model_path) path
FROM obj
LEFT OUTER
JOIN fd
ON fd.i_folder = obj.i_folder AND obj_type_name!='Table'
LEFT OUTER
JOIN mp
ON mp.i_mc = obj.i_folder AND obj_type_name='Table'
LEFT OUTER
JOIN snp_project p
ON p.i_project = COALESCE(obj.i_project, fd.i_project)
LEFT OUTER
JOIN snp_model m
ON m.i_mod = obj.i_project
Uli Bethke
Uli has been rocking the data world since 2001. As the Co-founder of Sonra, the data liberation company, he’s on a mission to set data free. Uli doesn’t just talk the talk—he writes the books, leads the communities, and takes the stage as a conference speaker.
Any questions or comments for Uli? Connect with him on LinkedIn.
Follow Uli Bethke: 
