ODI Snippets: OdiSqlUnload with header
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
OdiSqlUnload "-FILE=D:/xxx/<%=odiRef.getContext("CTX_NAME")%>/odi_files/DQError/<%=odiRef.getSession("SESS_NO")%>_header.csv" "-DRIVER=oracle.jdbc.driver.OracleDriver" "-URL=jdbc:oracle:thin:@xx.1.xx.xx:xxxx:odi" "-USER=<%=odiRef.getSchemaName("ORCL_XXX_TRG", "D")%>" "-PASS=xxxxxxxxxxxx" "-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 LTRIM(SYS_CONNECT_BY_PATH(column_name, ','),',') path FROM ( SELECT MAX(column_id) OVER (PARTITION BY table_name) as tot, column_name, column_id FROM user_tab_cols WHERE table_name = UPPER('<%=odiRef.getOption("TABLE_NAME")%>') ) WHERE column_id = tot START WITH column_id = 1 CONNECT BY PRIOR column_id = column_id -1 |
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
|
1 2 |
OdiSqlUnload "-FILE=D:/xxx/<%=odiRef.getContext("CTX_NAME")%>/odi_files/DQError/<%=odiRef.getSession("SESS_NO")%>_load.csv" "-DRIVER=oracle.jdbc.driver.OracleDriver" "-URL=jdbc:oracle:thin:@xx.1.xx.xx:xx:odi" "-USER=<%=odiRef.getSchemaName("ORCL_XXX_TRG", "D")%>" "-PASS=xxxxxxxxxxxxxxxx" "-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 <%=odiRef.getOption("TABLE_NAME")%> |
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
|
1 |
OdiFileAppend "-FILE=D:/xxx/<%=odiRef.getContext("CTX_NAME")%>/odi_files/DQError/<%=odiRef.getSession("SESS_NO")%>*.csv" "-TOFILE=D:/ODI/<%=odiRef.getContext("CTX_NAME")%>/odi_files/DQError/<%=odiRef.getSession("SESS_NO")%>_errors.csv" "-CASESENS=NO" |
[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)
