We always recommend that customers use the ODI SDK to access the information stored in the ODI repository: the repository structure evolves from version to version, and the SDK shelters developers from these structural changes.
This said, many developers still prefer to write their own SQL queries to read from the repositories directly, even if this may mean a rewrite of these queries with each repository upgrade.
To help customers that prefer the SQL route, our support team has put together documents that describes the repositories structure for ODI versions 126.96.36.199, 12.1.2, 12.1.3 and ODI 12.2.1
To find these documents, login to http://support.oracle.com, and look for Doc ID 1903225.1 : Oracle Data Integrator 11g and 12c Repository Description
For ODI best practices, tips, tricks, and guidance that the A-Team members gain from real-world experiences working with customers and partners, visit “Oracle A-Team Chronicles for ODI”.