How to query a whole schema or even database?! This tip will save you hours and hours of boring work.

Uli Bethke Oracle Data Integrator (ODI)

I really like this tip especially its simplicity. It has saved me hours and hours of mind numbing work in the past.

The use case

Imagine the following situation. You are tasked to complete a source to target map. With the help of various source system SMEs, legacy data models, and the data dictionary of the source database you have been able to complete 99% of the mappings. However, there is that one field that is nowhere to be found in the source system (there's always one). The business users have given you some sample values for this field. Wouldn't it be nice to run a query such as SELECT column_name FROM schema/database WHERE value = 'Sample Value'.

I will show you how you can achieve something similar with ODI.

The trick is to dump the content of your schema or database into a folder and then use grep your Windows search or whatever to search for your sample value.

In ODI this is extremely easy. Using the Command on Source/Target functionality it can be completed with two lines of code.

An example

Our objective is to find the table where ODI stores Option values for interfaces.

In a first step we create the procedure that dumps the content of the ODI schema to a folder

Command on Source

We query the data dictionary for those tables that are owned by the ODI work repository user. If you want to dump out the whole database just get rid of the WHERE clause.

SELECT table_name,owner from all_tables WHERE owner = ‘<name of your work schema>’

Command on Target

We use the tool OdiSqlUnload to unload those tables from the Command on Source recordset. For each table we create a separate text file.

OdiSqlUnload "-FILE=C:unload#table_name.txt" "-DRIVER=oracle.jdbc.driver.OracleDriver" "-URL=<your connection>" "-USER=#owner" "-PASS=<your password>" "-FILE_FORMAT=VARIABLE" "-FIELD_SEP=;" "-ROW_SEP=rn" "-DATE_FORMAT=yyyy/MM/dd HH:mm:ss" "-CHARSET_ENCODING=ISO8859_1" "-XML_CHARSET_ENCODING=ISO-8859-1"

SELECT * FROM #table_name


In a second step we enter a dummy value for one of our interfaces. This will be the value that we will search for in the dump.

Our dummy value is XXXXXX.

Next we run the procedure. This will dump the table contents into files.

Finally, we use Windows Search, grep or similar to find the file where our sample value is stored.

Windows search comes back with one result. The last column of table SNP_UE_USED, which is I_TXT_VALUE.

I am sure you can think of many more use cases for this. Of course, you can make all of this more sophisticated and wrap it up in a package with options to run against different databases such as MS SQL Server etc.

For very big databases you will need to watch performance. You may want to only extract a subset of tables and search for your sample value there before moving on to the next set and so on.

About the author

Uli Bethke LinkedIn Profile

Uli has 18 years’ hands on experience as a consultant, architect, and manager in the data industry. He frequently speaks at conferences. Uli has architected and delivered data warehouses in Europe, North America, and South East Asia. He is a traveler between the worlds of traditional data warehousing and big data technologies.

Uli is a regular contributor to blogs and books, holds an Oracle ACE award, and chairs the the Hadoop User Group Ireland. He is also a co-founder and VP of the Irish chapter of DAMA, a non for profit global data management organization. He has co-founded the Irish Oracle Big Data User Group.