ODI Snippets: OdiSqlUnload with header

Published on November 19, 2009
Updated on February 15, 2024

Get our e-books Discover the Oracle Data Integrator 11g Repository Data Model and Oracle Data Integrator Snippets and Recipes
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

In order to master scripting in ODI I recommend the following books.

Java BeanShell

Scripting in Java: Languages, Frameworks, and Patterns


The Definitive Guide to Jython: Python for the Java Platform.
Jython Essentials (O’Reilly Scripting)