OdiSqlUnload and the Possibilities

The Oracle Data Integrator is one of the best futuristic data transformation and loading tools with a variety of features for the end-user. ODI as an ELT tool provides a lot of inbuilt features for data processing using different technologies.

One of the most useful I find is the ODI tool OdiSqlUnload, which can be used in multiple ways in different scenarios for different purposes. To use this feature, create a new ODI procedure and in the Source tab select technology as “ODI Tools”. Then using the below syntax we can create files with the listed customization options:

  • Dynamic output file path
  • Dynamically fetched schema username
  • Dynamically fetched schema password
  • File content is decided by the SQL query (the logical schema for the SQL to be executed in needs to be set in the Target tab of the ODI procedure)

OdiSqlUnload “-FILE=/Output.txt” “-DRIVER=” “-URL=” “-USER=” “-PASS=” “-FILE_FORMAT=VARIABLE” “-FIELD_SEP=” “-ROW_SEP=” “-DATE_FORMAT=yyyy/MM/dd HH:mm:ss” “-CHARSET_ENCODING=ISO8859_1” “-XML_CHARSET_ENCODING=ISO-8859-1”
select <>, <>, <> from <>.<

>

Now that I am able to successfully generate the ODI generated files, let’s look at the different ways this can be leveraged to cater to various scenarios:

  • Create and attach files to emails via the OdiSendMail tool
  • Generate error logs for ODI Load Plans and notify administrators
  • Create multiple files with different delimiter formats and data content in a single procedure by adding multiple steps
  • Create files in a single path using data from different physical sources using different logical schemas in different steps in a single procedure

Can you think of any more use of this cool tool?

Published by Rahul

Rahul is a data analyst and expert in visualizing business scenarios using data science. He has performed extensive research across varied business scenarios and datasets to come up with insightful results. Rahul is skilled in a number of programming languages and data analysis tools. When he is not busy refining business data, Rahul can be found somewhere in the Appalachian trails or in an ethnic restaurant in Chicago.

Leave a Reply

[class^="wpforms-"]
[class^="wpforms-"]