ODI snippets: Query to retrieve source and target columns in an interface
Get our e-books Discover the Oracle Data Integrator 11g Repository Data Model and Oracle Data Integrator Snippets and Recipes
The following query can be used to retrieve the source columns/tables and target columns/tables for interfaces in a specified project
BELOW QUERY IS VALID FOR ODI 10g ONLY!!! ODI 11g has introduced the concept of data sets, which invalidates below query.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
SELECT c.table_name AS source_table, e.col_name AS source_col, f.table_name AS target_table, a.col_name AS target_col FROM snp_pop_col a LEFT OUTER JOIN snp_src_set b ON (a.I_SRC_SET = b.I_SRC_SET) LEFT OUTER JOIN snp_source_tab c ON (b.i_src_set = c.i_src_set) LEFT OUTER JOIN snp_txt_crossr d ON (a.i_txt_map = d.i_txt) LEFT OUTER JOIN snp_col e ON (d.i_col = e.i_col) JOIN snp_pop f ON (a.i_pop = f.i_pop) WHERE a.i_pop IN ( SELECT i_pop FROM snp_pop WHERE i_folder IN ( SELECT i_folder FROM snp_folder WHERE i_project = project_id ) ) ORDER BY a.i_pop; |
[big_data_promotion]
In order to master scripting in ODI I recommend the following books.
Java BeanShell
Scripting in Java: Languages, Frameworks, and Patterns
Jython
The Definitive Guide to Jython: Python for the Java Platform.
Jython Essentials (O’Reilly Scripting)
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: 
