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

March 17, 2010

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.

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;

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)

Back to Blog

Cookie	Duration	Description
__cfruid	session	Cloudflare sets this cookie to identify trusted web traffic.
cookielawinfo-checkbox-marketing	1 month	This cookie is set by the GDPR Cookie Consent plugin to store the user consent for the cookies in the category "Marketing".
cookielawinfo-checkbox-necessary	1 month	This cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-preferences	1 month	This cookie is set by the GDPR Cookie Consent plugin to check if the user has given consent to use cookies under the "Preferences" category.
cookielawinfo-checkbox-statistics	1 month	This cookie is set by the GDPR Cookie Consent plugin to store the user consent for the cookies in the category "Statistics".
cookielawinfo-checkbox-unclassified	1 month	This cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Unclassified".
CookieLawInfoConsent	1 month	Records the default button state of the corresponding category & the status of CCPA. It works only in coordination with the primary cookie.
csrftoken	1 year	This cookie is associated with Django web development platform for python. Used to help protect the website against Cross-Site Request Forgery attacks

Cookie	Duration	Description
AnalyticsSyncHistory	1 month	Linkedin set this cookie to store information about the time a sync took place with the lms_analytics cookie.
bcookie	2 years	LinkedIn sets this cookie from LinkedIn share buttons and ad tags to recognize browser ID.
bscookie	2 years	LinkedIn sets this cookie to store performed actions on the website.
lang	session	LinkedIn sets this cookie to remember a user's language setting.
li_gc	2 years	Linkedin set this cookie for storing visitor's consent regarding using cookies for non-essential purposes.
lidc	1 day	LinkedIn sets the lidc cookie to facilitate data center selection.
mgref	1 year	This cookie is set by Eventbrite to deliver content tailored to the end user's interests and improve content creation. It is also used for event-booking purposes.
mgrefby	1 year	This cookie is set by Eventbrite to deliver content tailored to the end user's interests and improve content creation. It is also used for event-booking purposes.
UserMatchHistory	1 month	LinkedIn sets this cookie for LinkedIn Ads ID syncing.

Cookie	Duration	Description
G	1 year	Cookie used to facilitate the translation into the preferred language of the visitor.
SERVERID	session	This cookie is set by Slideshare's HAProxy load balancer to assign the visitor to a specific server.

Cookie	Duration	Description
_ga	2 years	The _ga cookie, installed by Google Analytics, calculates visitor, session and campaign data and also keeps track of site usage for the site's analytics report. The cookie stores information anonymously and assigns a randomly generated number to recognize unique visitors.
_ga_7H38LVR4Z5	2 years	This cookie is installed by Google Analytics.
_gat_gtag_UA_44804396_1	1 minute	Set by Google to distinguish users.
_gat_UA-44804396-1	1 minute	A variation of the _gat cookie set by Google Analytics and Google Tag Manager to allow website owners to track visitor behaviour and measure site performance. The pattern element in the name contains the unique identity number of the account or website it relates to.
_gcl_au	3 months	Provided by Google Tag Manager to experiment advertisement efficiency of websites using their services.
_gid	1 day	Installed by Google Analytics, _gid cookie stores information on how visitors use a website, while also creating an analytics report of the website's performance. Some of the data that are collected include the number of visitors, their source, and the pages they visit anonymously.
CONSENT	2 years	YouTube sets this cookie via embedded youtube-videos and registers anonymous statistical data.
SIDCC	6 Months	The "SIDCC" cookie is used as security measure to protect users data from unauthorised access
test_cookie	15 minutes	The test_cookie is set by doubleclick.net and is used to determine if the user's browser supports cookies.
VISITOR_INFO1_LIVE	5 months 27 days	A cookie set by YouTube to measure bandwidth that determines whether the user gets the new or old player interface.
YSC	session	YSC cookie is set by Youtube and is used to track the views of embedded videos on Youtube pages.
yt-remote-connected-devices	never	YouTube sets this cookie to store the video preferences of the user using embedded YouTube video.
yt-remote-device-id	never	YouTube sets this cookie to store the video preferences of the user using embedded YouTube video.
yt.innertube::nextId	never	This cookie, set by YouTube, registers a unique ID to store data on what videos from YouTube the user has seen.
yt.innertube::requests	never	This cookie, set by YouTube, registers a unique ID to store data on what videos from YouTube the user has seen.

Cookie	Duration	Description
AN	1 month
AS	session
ebEventToTrack	1 month
eblang	1 year
SNID	2 years	This cookie is set by the Google. This cookie is used by the map which helps visitors to identify and reach the facility.
SP	session
SS	session

From Code to Clarity: Visualizing SQL code for Documentation and Debugging

From Code to Clarity: Visualizing SQL code for Documentation and Debugging

From Code to Clarity: Visualizing SQL code for Documentation and Debugging

From Code to Clarity: Visualizing SQL code for Documentation and Debugging

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

Java BeanShell

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

Java BeanShell

Related Articles

Automating XML Conversion with Oracle's ETL Tool ODI

Parsing XML to Oracle. ODI vs Flexter Enterprise XML Parser

Tips for parsing and loading complex NDC XML files in ODI

Cookies consent