Home » Oracle Data Integrator (ODI) » ODI snippets: Query to retrieve source and target columns in an interface

ODI snippets: Query to retrieve source and target columns in an interface

by 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.

Published on March 17, 2010
Updated on November 20, 2024

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

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)

About the author:

Uli Bethke

Co-founder of Sonra

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: