ODI Snippets: OdiSqlUnload with header

Uli Bethke Oracle Data Integrator (ODI)

OdiSqlUnload is a handy tool to quickly dump the content of a table into a CSV file. This is a lot quicker than first creating a file data server, interfaces etc. However, OdiSqlUnload has one major drawback. It does not allow you to load the header columns of your table(s) into the CSV. This makes the tool pretty much useless for a lot of purposes.

I propose the following workaround.

We create a procedure with three steps

(1) Create a header CSV from data dictionary or work reposiotry meta data via OdiSqlUnload

(2) Create a content CSV from your database table(s)

(3) Append file2 to file1 with OdiFileAppend

For step one we use the following command

We put the context and our session_no into the file name for our header.csv.

We then query the user_tab_cols data dictionary table and use SYS_CONNECT_BY_PATH to create a CSV from the columns. If you are using Oracle 11GR2 I recommedn to use the new analytic function LISTAGG instead. As you can see from the script above we pass the name of the table into the procedure via the table_name option.

Technology for the step is Sunopsis API

For step two we use the following command

The above will generate the content CSV for the table name that we specify via the option table_name of our procedure. Obviously the table name should be the same as in step one.

Finally in step three we append the content CSV to the header CSV

Teach me Big Data to Advance my Career

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